Loan Portfolio CDR Model

The model's foundation is a segmented pool of loans, split into senior secured, second-lien/mezzanine, and unsecured/consumer segments, each with its own mix, weighted average coupon (WAC), and risk multipliers. Default and prepayment behaviors are driven by baseline CDR and CPR curves, shaped by segment-specific multipliers and scenario multipliers.

• Recoveries occur with a lag and are blended across segments based on gross defaults. These drivers, along with funding rates, servicing costs, and operating expenses, are user-editable assumptions, allowing the model to reflect different portfolio compositions and market conditions.
• The closed-pool runoff design assumes no new originations by default, so the pool amortizes to zero over the 10-year horizon.

Each segment's balance rolls forward annually: opening balance plus new originations (if any) forms gross additions, from which gross defaults, prepayments, and scheduled amortization are subtracted to arrive at closing balance. The aggregate portfolio rollforward sums these segment-level results, computing blended WAC and blended recovery rates based on period-specific weights.

• Interest income is then calculated on the average performing balance using the blended WAC, while recoveries are received after a user-defined lag. Debt is sized to a target leverage ratio, with draws or repayments adjusting the balance.
• This flow ensures that defaults reduce both principal and interest income naturally, avoiding double-counting of losses.

The model generates levered equity cash flows, from which it computes equity IRR, MOIC, and gross IRR. A tranche waterfall sequentially pays senior interest and principal, then mezzanine interest and principal, with residual cash flowing to equity.

• A mezzanine impairment test compares cumulative net losses to the equity attachment point, flagging whether the mezzanine tranche is at risk. Additionally, an accrual income statement derives net interest income, pre-provision income, and net income, along with NIM, ROA, and ROE.
• Scenario outputs for Base, Downside, and Upside are accessible via a single selector, providing a quick view of how key metrics respond to stress.

This template is suited for credit investors analyzing a closed pool of loans held for investment, such as direct lenders or distressed debt funds.

• By adjusting segment assumptions, scenario drivers, and structural parameters like advance rates and spreads, users can test how the portfolio performs under varying default, recovery, and funding conditions.
• The waterfall and impairment test help assess whether mezzanine debt remains protected and how equity returns change with leverage.
• While the public version is a values-only preview, the underlying model captures the documented mechanics, enabling users to understand the relationships between pool performance, debt structure, and equity outcomes.