Cashflow Simulation

The Cashflow Projection Model creates detailed forecasts of your loan portfolio's future cash flows by analyzing how loans will perform under different economic scenarios. It answers critical business questions like:

Cashflow Projection Model - User Guide

What This Model Does

The Cashflow Projection Model creates detailed forecasts of your loan portfolio's future cash flows by analyzing how loans will perform under different economic scenarios. It answers critical business questions like:

• How much money will we receive each month?
• What happens if the economy gets worse and more borrowers default?
• How will early loan payoffs affect our cash flow?
• What can we recover from loans that go bad?

The model produces month-by-month projections showing exactly when and how much cash you can expect to receive from your loan portfolio.

How the Model Calculates Cash Flows

❗ Step 1: Historical Performance Analysis

The model starts by examining your loan portfolio's historical performance:

Default Rate Calculation

• Analyzes charge-offs over the past 6 months for each loan segment
• Calculates a Monthly Default Rate (MDR) = (written_off_amount ÷ starting_outstanding_balance) × 100
• Converts to Annual Default Rate (CDR) using the formula: CDR = 1 - (1 - MDR/100)^12
• This tells us: "Historically, X% of loans in this segment default each year"

Prepayment Rate Calculation

• Examines early payoffs (payments above scheduled amounts) over the past 6 months
• Calculates Single Monthly Mortality (SMM) = (Extra Payments ÷ Remaining Balance) × 100
• Only applies when: non_delinquent_payments > principal_scheduled AND remaining_balance > 0
• Converts to Annual Prepayment Rate (CPR) using: CPR = 1 - (1 - SMM/100)^12
• This tells us: "Historically, Y% of loans in this segment pay off early each year"

Rate Aggregation

• Filter-Key Specific Rates: Average of 6-month historical rates for each loan segment
• Overall Portfolio Rates: Weighted average across all segments for comparison scenarios

❗ Step 2: Scenario Creation

The model creates 210 different economic scenarios by combining:

Default Rate Multipliers (7 scenarios)

• 0.0x = No defaults → Effect: Maximum cash flow, no losses, all scheduled payments received
• 0.5x = Half normal default rates → Effect: 50% fewer losses, higher cash flows than baseline
• 0.75x = Mild improvement → Effect: 25% fewer losses, slightly higher cash flows
• 1.0x = Normal conditions (baseline) → Effect: Expected historical performance levels
• 1.25x = Mild stress → Effect: 25% more losses, reduced cash flows
• 1.5x = Moderate stress → Effect: 50% more losses, notably reduced cash flows
• 2.0x = Severe stress → Effect: Double the losses, significantly reduced cash flows

Prepayment Rate Multipliers (5 scenarios)

• 0.0x = No early payoffs → Effect: Loans run to full term, maximum interest income over time
• 0.5x = Reduced prepayments → Effect: Slower principal recovery
• 1.0x = Normal prepayment activity → Effect: Expected historical prepayment patterns
• 1.5x = Increased prepayments → Effect: Faster principal recovery
• 2.0x = High prepayment environment → Effect: Rapid principal recovery

Recovery Rates (3 scenarios)

• 0% = No recovery from defaults → Effect: Maximum losses, conservative assumption
• 5% = Low recovery expectations → Effect: Small offset to default losses
• 10% = Moderate recovery expectations → Effect: Meaningful reduction in net losses

Aggregation Types (2 versions per scenario)

• Filter-Key Specific (aggregated=False): Uses each segment's historical rates
• Portfolio Aggregate (aggregated=True): Uses overall portfolio rates

What Each Variable Change Means for Your Business

Increasing Default Rates (CDR Multiplier)

Higher multipliers mean:

• ✅ Lower Total Cash Flow: More loans default, reducing overall collections
• ✅ Earlier Cash Flow: Defaults happen early, accelerating the end of cash flows
• ✅ Higher Gross Losses: More write-offs and charge-offs
• ✅ Reduced Portfolio Life: Portfolio pays down faster due to defaults
• ✅ Lower Interest Income: Defaulted loans stop generating interest

Increasing Prepayment Rates (CPR Multiplier)

Higher multipliers mean:

• ✅ Faster Principal Recovery: Get your money back sooner
• ✅ Lower Total Interest Income: Loans pay off before generating full interest
• ✅ Shorter Portfolio Life: Portfolio pays down much faster
• ✅ Front-Loaded Cash Flows: Higher cash flows early, lower cash flows later
• ✅ Reinvestment Risk: Need to find new investment opportunities sooner

Increasing Recovery Rates

Higher recovery rates mean:

• ✅ Lower Net Losses: Recover more money from defaulted loans
• ✅ Higher Total Cash Flow: Offset some default losses with collections
• ✅ Extended Cash Flow Timeline: Recoveries may come months after defaults
• ✅ Improved Portfolio Returns: Better overall profitability despite defaults

❗ Step 3: Monthly Cash Flow Calculations

For each scenario, the model calculates what happens month by month using a specific order of operations:

Order of Operations (Critical Sequence)

For each month, the model follows this precise sequence:

• Calculate Survival Factor: Determines what percentage of the original portfolio is still active
• Apply Scheduled Payments: Calculate regular principal and interest payments (adjusted by survival factor)
• Calculate Defaults: Determine loan losses based on total remaining scheduled payments (principal + interest + fees)
• Calculate Prepayments: Determine early payoffs based on total remaining unscheduled payments
• Calculate Recoveries: Apply recovery rate to default amounts
• Update Remaining Balance: Starting balance minus scheduled payments, prepayments, and net defaults
• Update Survival Factor: Reduce for next period based on current defaults and prepayments

Month 1 Calculations

• Starting Balance: Current outstanding loan amount
• Survival Factor: 100% (all loans start active)
• Scheduled Payments: Regular principal and interest due
• Expected Defaults: $0 (defaults begin in month 2)
• Expected Prepayments: Based on total remaining payments × prepayment rates
• Expected Recoveries: $0 (no defaults to recover from yet)

Month 2+ Calculations (Recursive Process)

• Starting Balance: Previous month's ending balance
• Survival Factor: Percentage of loans still active (compounds monthly reduction)
• Expected Scheduled Payments: Regular payments × Survival factor
• Expected Defaults: Total remaining scheduled payments × Monthly default rate × Survival factor
• Expected Prepayments: Total remaining unscheduled payments × Monthly prepayment rate × Survival factor
• Expected Recoveries: Expected defaults × Recovery rate
• Ending Balance: Starting balance - Scheduled payments - Prepayments - (Defaults - Recoveries)

❗ Step 4: Cash Flow Components

Each month's total cash flow consists of:

• Scheduled Principal: Regular loan principal payments
• Scheduled Interest & Fees: Interest and fee income
• Prepayments: Early principal repayments
• Recoveries: Collections from previously defaulted loans

Total Monthly Cash Flow = Scheduled Principal + Scheduled Interest & Fees + Prepayments + Recoveries - Defaults

Step 5: Portfolio Aggregation

Individual Segment Analysis

• Uses each loan segment's specific historical rates
• Shows performance differences between loan types/risk grades
• Useful for detailed risk analysis

Portfolio-Level Analysis

• Uses overall portfolio averages for comparison scenarios
• Provides baseline comparison against segment-specific projections
• Useful for high-level portfolio planning

Key Model Features

Survival Factor Modelling

The model tracks how your loan portfolio "survives" over time:

• Month 1: 100% of loans are active
• Month 2: Reduced by defaults and prepayments from Month 1
• Month 3: Further reduced by Month 2 activity
• This ensures realistic cash flow projections that account for portfolio runoff

Recursive Calculations

Each month's calculations depend on the previous month's results:

• Defaults in early months reduce future scheduled payments
• Prepayments accelerate principal recovery but reduce future interest
• The model properly accounts for these interdependencies

Enhanced Default and Prepayment Modeling

Critical Update: Defaults and prepayments are now calculated based on total remaining payments (principal + interest + fees), not just principal:

• More realistic modeling of cash flow losses when loans default or prepay
• Defaults reduce both principal and interest income streams
• Prepayments eliminate both future principal and interest payments
• Balance tracking uses net default impact (defaults minus recoveries)

Performance Optimizations

• Vectorized calculations for improved processing speed
• Parallel processing using 80% of available CPU cores
• Memory-efficient data handling and optimization
• Automatic fallback to sequential processing if needed