Structured Finance using EXCEL


Key Topics


Basic Elements of a Cash Flow Model, Process of Building, Basic Framework, Validate Assumptions, Test Model

Dates and Timing

Time Progression, Day-Count Systems, Data Validation Lists, EDATE

Asset Cashflow

Loan Level versus Representative Line Amortization, Fixed Rate Amortization Inputs, Floating Rate, Notional Asset Amortization, OFFSET, MATCH, MOD, PMT


SMM: Single Monthly Mortality, CPR: Conditional Prepayment Rate, PSA: Public Securities Association, ABS: Absolute Prepayment Speed, Historical Prepayment Data, Building Prepayment Curves, Effect of Prepayments on Structured Transactions, Weighted Averages Using SUMPRODUCT and SUM

Loss Analysis

Delinquencies versus Defaults versus Loss, Importance of Analysing Delinquency, Deriving Historical Loss Curves, Analysing Historical Loss Curves, Projecting Loss Curves, Integrating Loss Projections, Seasoning and Default Timing


Historical Recovery Analysis, Projecting Recoveries


Priority of Payments, Cash Flow Waterfall, Types of Liabilities, Fees, Interest, Principal, Basic Asset and Liability Interactions

Advanced Liability Structures

Triggers, Affect on the Liability Structure, Swaps, Basic Interest Rate Swap, Cash-Funded Reserve Account, AND and OR


Internal Testing, Cash In versus Cash Out Test, Balances at Maturity, Asset Principal Check, Performance Analytics, Monthly Yield, Bond-Equivalent Yield, Modified Duration, Conditional Formatting, Goal Seek, Array Formulas

Model Understanding

Effects of Increased Loss, Varying Principal Allocation Methodologies, Varying Prepayment Rates, Varying Loss Timing, Varying Recovery Rate, Value of a Swap

Note - Course content and topics to be covered in the training can be customized as per requirement for Corporates.

Faculty profile

FEES for Corporates

Based on the syllabus, contents and other requirements for the corporate training, scope with detailed inclusions and exclusions along with the fee will be shared.