Loan Portfolio CDR ModelFree Financial Model Download

A Loan Portfolio CDR (Cumulative Default Rate) Model values a closed pool of loans acquired by a credit investor, projecting annual defaults, prepayments, principal collections, recoveries, and equity returns. The model applies vintage-based cumulative default rates (CDR curves—lower in Year 1, peaking Year 3-4, then stabilizing), conditional prepayment rates (CPR), and loss recovery rates (typically 50-75% for senior secured, 10-25% for unsecured) to compute net loss rates and residual cash flow. A typical $100M portfolio at 80% leverage (20% equity) with 10% weighted-average coupon (WAC), 2.5% average CDR, and 65% recovery rate generates 12-16% levered equity IRR with 1.5-2.0x MOIC over the runoff.

The Portfolio_Rollforward sheet drives the core mechanics: Opening Balance (rolling forward via closure formula) + New Originations grown at a fixed rate → Gross Additions. CDR and CPR curves (year-dependent, via CHOOSE function) are applied to Gross Additions to compute annual Defaults and Prepayments. Scheduled Amortization (using simplified WAM formula: Avg_Balance / WAM years) completes the runoff. Defaults exit the performing pool; Interest Income and Principal Collections apply to remaining performing balance only, preventing double-counting. The Default_Recovery sheet applies Recovery_Lag (typically 1-2 years) and Recovery_Rate to compute cash recovery timing. The Cash_Flow sheet nets: Interest_Income + Principal_Collections + Recoveries − Cost_of_Funds − Servicing − Opex = Net Portfolio CF. Fresh equity top-ups (MAX(0, (New_Originations − Principal_Collections) × Equity_%)) are called only when growth outpaces recycled principal.

This model suits credit investors, distressed funds, loan acquirers, and BDCs. Key metrics include net portfolio yield (WAC minus funding cost minus loss rate), MOIC (cumulative cash returned / cumulative cash invested), IRR, and cumulative loss rate (as % of initial balance). Typical leverage for institutional investors is 75-85% LTV; covenant tests include minimum equity IRR (8-12%) and maximum cumulative loss rate (10-15% of portfolio).