# Loan Portfolio CDR Model Forecast loan defaults by vintage and cohort, model recovery and loss severity, and calculate portfolio risk metrics to assess reserve adequacy. Link default and recovery rates to macroeconomic variables and run recession stress scenarios to satisfy regulatory capital requirements. - Canonical: https://finamodel.com/templates/loan-portfolio-cdr-model - Excel download: https://finamodel.com/templates/loan-portfolio-cdr.xlsx - Category: Credit - Model type: Portfolio - Difficulty: Advanced - Audiences: Credit & risk, Bankers & advisors, Credit Analysts, Bank Risk Teams, Loan Portfolio Managers, Investors - Tags: credit-risk, loan-portfolio, default-rate, loss-severity, reserve ## Overview 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). ## What's included - Portfolio composition by loan type, vintage, and origination channel - Cumulative default rate curves by cohort and stress scenario - Loss given default assumptions by collateral type - Recovery rates and timing of recoveries post-default - Reserve calculations and capital impact analysis - Loss given default (LGD) assumptions by collateral type - Forward-looking economic indicators and scenario linkages ## Loan Portfolio CDR Model: Closed-Pool Runoff and Cash Flow Mechanics Understanding how a loan portfolio cdr model projects defaults and prepayments is essential for credit investors evaluating structured portfolios. This template simulates a segmented loan pool under Base, Downside, and Upside scenarios, modeling default (CDR) and prepayment (CPR) behavior, recovery lags, and a senior/mezz/equity tranche waterfall. It calculates levered equity returns, net income, and key risk metrics to assess reserve adequacy and investment performance over a 10-year horizon. ### Key Operating Drivers: Segments, CDR/CPR Curves, and Recovery Assumptions 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. ### Calculation Flow: From Segment Runoff to Aggregate Cash Flows 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. ### Outputs: Returns, Impairment Tests, and Accrual P&L 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. ### Practical Use: Evaluating Portfolio Risk and Structural Outcomes 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. ## Vintage segmentation Track defaults by loan origination year to identify periods of elevated risk from loose underwriting and model outcome differences across cohorts. ## Macroeconomic stress scenarios Link default and recovery rates to economic variables such as unemployment and house prices to show portfolio loss impact under recession conditions. ## Expected loss and reserve calculations Compute probability of default, loss given default, and exposure at default for each cohort, then aggregate to allowance for loan losses. ## Vintage segmentation Track defaults by loan origination year to identify periods of elevated risk from loose underwriting and model outcome differences across cohorts. ## Macroeconomic stress scenarios Link default and recovery rates to economic variables such as unemployment and house prices to show portfolio loss impact under recession conditions. ## Expected loss and reserve calculations Compute probability of default, loss given default, and exposure at default for each cohort, then aggregate to allowance for loan losses. ## Features - **Vintage segmentation:** Track defaults by loan origination year to identify periods of elevated risk (e.g., loose underwriting) and model outcome differences. - **Macroeconomic stress scenarios:** Link default and recovery rates to economic variables (unemployment, house prices, GDP growth) to show impact of recessions. - **Expected loss and ALLL calculations:** Compute probability of default, loss given default, and exposure at default for each cohort; aggregate to allowance for loan losses. ## Use cases - **Regulatory capital and reserve adequacy:** Calculate required reserves and capital buffers under CCAR, CECL, or equivalent frameworks to satisfy regulators. - **Portfolio stress testing:** Model loss outcomes under severe recession, unemployment spike, or sector downturn to stress investor and lender confidence. - **Loan pricing and approval:** Use CDR curves and LGD assumptions to calculate risk-adjusted returns and pricing required for underwriting approval. ## Frequently asked questions ### What is a cumulative default rate (CDR)? CDR is the percentage of loans in a cohort that have defaulted by a given loan age. For example, 3% of loans originated in Year 1 may have defaulted by age 5. CDR curves show how credit risk evolves as loans season. ### How do I estimate loss given default? LGD is the percentage of the loan balance lost after recovery. It depends on collateral quality, seniority, and market conditions at the time of recovery. Historical loss data by product type is the most reliable input. ### What economic scenarios should I model? At minimum, model a base case, a recession scenario with unemployment rising 2-4%, and a severe recession. Link CDR and recovery rates to each scenario and show loss sensitivity across the three cases. ### Who uses loan portfolio CDR models? Credit analysts, bank risk teams, loan portfolio managers, and investors use these models for regulatory capital planning, portfolio stress testing, and loan pricing and approval decisions. ### What is CECL and how does it affect reserve modeling? CECL (Current Expected Credit Loss) requires banks to reserve for lifetime expected losses at origination rather than incurred losses. CDR curves and LGD assumptions feed directly into the CECL allowance calculation. ## Related templates - [Credit Portfolio CDO Model](https://finamodel.com/templates/credit-portfolio-cdo-model) - [Mortgage Portfolio Model](https://finamodel.com/templates/mortgage-portfolio-model) - [Student Loan Portfolio](https://finamodel.com/templates/student-loan-model)