Cash flow modeling

Your cash flow model is the engine behind every structuring decision. It translates historical collateral performance into projected deal economics and tells you whether your advance rates, triggers, and credit enhancement are calibrated correctly. If your model is wrong, your deal is mispriced.

This guide walks you through building and validating cash flow models for ABF transactions, from basic projection mechanics to stress testing and sensitivity analysis.

What you’re actually modeling

A cash flow model projects how money moves from borrowers through your collateral pool, through the structural waterfall, and out to investors. The core equation:

Collections = Scheduled Principal + Prepayments + Recoveries

Losses = Defaults × Severity

Available Funds = Collections + Interest Received - Losses - Fees

You’re projecting these flows over time, typically monthly, from closing through legal final maturity. For a warehouse facility with a 2-year revolving period and 1-year amortization, that’s 36 monthly periods. For a term ABS deal, it might be 60-120+ months depending on asset class.

Two fundamentally different modeling contexts:

1. Static pool (term ABS): Fixed collateral at closing. You project runoff of the original pool with no new additions.

2. Dynamic pool (warehouse, revolving): Collateral turns over. You model replenishment, eligibility tests, and reinvestment alongside amortization.

Most modeling errors come from conflating these two or using static pool logic for a revolving structure.

Input assumptions: where the model lives or dies

Your model is a machine that converts assumptions into projections. The assumptions are everything.

Prepayment speed (CPR)

CPR (Conditional Prepayment Rate) measures voluntary early payoffs, expressed as an annualized percentage of the remaining pool.

How to derive it:

1. Pull 12-24 months of historical prepayment data from your tape
2. Calculate monthly SMM (Single Monthly Mortality): prepayments ÷ beginning balance
3. Annualize: CPR = 1 - (1 - SMM)^12

Typical ranges by asset class:

Asset Class	Base Case CPR	Stress Range
Auto loans	12-18%	8-25%
Consumer unsecured	15-30%	10-40%
Equipment	8-15%	5-20%
RMBS (non-agency)	10-20%	5-35%

Prepayment matters for two reasons: it affects WAL (how long your money is out), and it affects excess spread economics (faster prepay = less interest collected over the life of the deal).

Default rate (CDR)

CDR (Constant Default Rate) measures the annualized rate at which loans transition to default status.

How to derive it:

1. Define your default trigger (90+ DPD, charge-off, bankruptcy filing)
2. Calculate monthly default rate: new defaults ÷ beginning balance
3. Annualize: CDR = 1 - (1 - MDR)^12

The shape matters as much as the level. Consumer credit defaults typically front-load in months 6-24 after origination. Equipment defaults may be more evenly distributed. Your model needs to reflect the credit curve shape, not just a flat CDR.

Typical ranges:

Asset Class	Base Case CDR	Stress (BBB)	Stress (AAA)
Prime auto	1.5-3.0%	5-7%	10-15%
Subprime auto	8-15%	18-25%	35-50%
Consumer unsecured	5-12%	15-20%	30-40%
Equipment	1-3%	4-6%	8-12%

Severity (loss given default)

Severity is the percentage of defaulted balance you don’t recover. A 40% severity means you recover 60 cents on the dollar.

Components:

• Liquidation proceeds (collateral sale, if secured)
• Collection recovery (unsecured)
• Costs (repo, legal, servicing, storage)

Typical ranges:

Asset Class	Base Severity	Stress Severity
Auto (prime)	35-45%	50-65%
Auto (subprime)	55-70%	75-90%
Consumer unsecured	85-95%	95-100%
Equipment	30-50%	50-70%
Residential mortgage	25-40%	40-60%

Note: Severity assumptions need to reflect your actual historical recoveries, not industry benchmarks. If your servicing operation is better or worse than average, your severity will differ.

Recovery timing

When you get recoveries matters, not just how much. A 60% recovery in month 3 post-default is worth more than 60% in month 18.

Model recovery lag explicitly:

• Auto: 2-4 months typical
• Consumer unsecured: 6-18 months
• Mortgage: 12-36 months

Roll rates and delinquency transition

For surveillance and dynamic modeling, you need transition probabilities:

• Current → 30 DPD
• 30 DPD → 60 DPD
• 60 DPD → 90 DPD
• 90 DPD → Default
• DPD buckets → Cure (back to current)

This is more granular than CDR and lets you model the pipeline of future defaults based on current delinquency inventory.

Building the waterfall model

The waterfall is the core of your structural model. It defines who gets paid, in what order, from available funds each period.

Step 1: map the liability stack

Create a table of each liability:

Tranche	Balance	Rate	Type	Priority
Class A	$85M	SOFR + 150	Floating	Senior
Class B	$10M	SOFR + 350	Floating	Mezz
Class C	$5M	Fixed 12%	Fixed	Junior
Residual	N/A	Excess	Equity	Last

Illustrative pricing. See pricing disclaimer.

Step 2: define priority of payments

The indenture specifies the waterfall. A typical pre-event waterfall:

1. Trustee fees and expenses
2. Servicer fees
3. Hedge payments (if any)
4. Class A interest
5. Class A principal (to target or minimum)
6. Class B interest
7. Class B principal (to target)
8. Class C interest
9. Class C principal
10. To reserve account (to target)
11. Residual to equity holder

After a trigger event (OC test failure, DQ trigger breach), the waterfall may change:

• Principal redirected to senior classes (sequential instead of pro rata)
• Excess spread trapped
• Junior interest deferred

Step 3: build the period-by-period logic

For each monthly period:

Beginning Pool Balance
- Scheduled Principal (from amortization schedule)
- Prepayments (CPR applied to balance)
- Defaults (CDR applied to balance)
+ Recoveries (lagged, severity-adjusted)
= Ending Pool Balance

Interest Collections (WAC × Average Balance)
+ Principal Collections
+ Recoveries
- Senior Fees
= Available Funds

Apply Waterfall →
Track: Each tranche ending balance, cumulative interest paid, coverage ratios

Step 4: handle revolving features

For warehouse and revolving structures:

• Reinvestment: During the revolving period, principal collections are used to purchase new collateral (subject to eligibility criteria)
• Eligibility tests: New collateral must meet concentration limits, credit quality thresholds, WAC/WALS tests
• Ramp-up: If the pool starts below target size, model the ramp to full utilization

The model needs logic to:

1. Calculate replenishment capacity each period
2. Apply eligibility constraints
3. Track pool composition changes over time

Step 5: calculate coverage tests

Most deals have OC and IC tests that determine waterfall behavior:

Overcollateralization (OC) Ratio:

OC = Pool Balance ÷ Notes Outstanding

Interest Coverage (IC) Ratio:

IC = Available Interest ÷ Required Interest Payments

Your model should track these each period and flag when tests fail, triggering waterfall changes.

Worked example: consumer loan warehouse

A $100M consumer loan warehouse with:

• $85M Class A (SOFR + 175)
• $15M equity (you)
• 2-year revolving, 1-year amortization
• Pool WAC: 18%, WAL: 2.5 years

Base case assumptions:

• CPR: 20%
• CDR: 8%
• Severity: 90%
• Recovery lag: 12 months

Month 1 projection:

Line Item	Amount
Beginning Balance	$100,000,000
Scheduled Principal	$2,500,000
Prepayments (20% CPR)	$1,510,000
Defaults (8% CDR)	$614,000
Recoveries (from prior defaults)	$0
Ending Balance	$95,376,000
Interest Collections	$1,500,000
Available Funds	$5,524,000

Waterfall application:

Priority	Payment	Running Total
Trustee/Admin fees	$8,333	$8,333
Servicer (1% annual)	$83,333	$91,666
Class A interest (SOFR 5.3% + 175 = 7.05%)	$499,125	$590,791
Reinvestment (revolving period)	$4,010,000	$4,600,791
Equity distribution	$923,209	$5,524,000

Stress testing and scenario analysis

Your base case is your expectation. Stress testing tells you what happens when you’re wrong.

Rating agency stress levels

Rating agencies publish their stress multiples. For a consumer unsecured pool:

Rating Level	CDR Multiple	Severity Multiple
AAA	4.0-5.0x	1.25-1.5x
AA	3.0-4.0x	1.2-1.4x
A	2.5-3.0x	1.15-1.3x
BBB	2.0-2.5x	1.1-1.2x

If your base case CDR is 8%, the AAA stress runs at 32-40% CDR.

Break-even analysis

The most important stress output: at what loss level does each tranche take a loss?

Run iterative scenarios increasing CDR until:

1. The senior tranche principal is impaired
2. The mezz tranche is wiped out
3. Equity receives zero distributions

Example output:

• Class A break-even: 45% cumulative net loss (CNL)
• Class B break-even: 28% CNL
• Equity break-even: 12% CNL

This tells you how much cushion exists at each level of the capital structure.

Prepayment stress

Fast prepayment reduces interest collections and compresses WAL. Slow prepayment extends duration and increases exposure to credit deterioration.

Run scenarios at:

• 50% of base CPR (slow pay, extension)
• 150% of base CPR (fast pay, compression)
• 0% CPR (full extension to maturity)

Timing stress

Front-loaded defaults hit the deal before it builds excess spread cushion. Model scenarios where:

• 70% of lifetime defaults occur in first 12 months
• Default curve shifts 6 months earlier
• Recovery timing extends by 6 months

Combination scenarios

Real stress events hit multiple vectors simultaneously. Model combined scenarios:

• High CDR + high severity + slow recovery
• Fast prepay + high defaults (refinance out of good loans, stuck with bad)
• Interest rate spike + extended WAL

Sensitivity analysis

Sensitivity tables show how outputs change as inputs vary. These are essential for IC presentations.

One-way sensitivity

Hold everything constant except one variable:

Class A Principal Loss Sensitivity to CDR:

CDR	Cumulative Loss	Class A Loss
5%	4.5%	$0
10%	9.0%	$0
15%	13.5%	$0
20%	18.0%	$0
25%	22.5%	$2.1M
30%	27.0%	$6.8M

Two-way sensitivity

Vary two inputs simultaneously:

Equity IRR Sensitivity (CDR × CPR):

CDR ↓ / CPR →	15%	20%	25%	30%
6%	18.2%	16.8%	15.1%	13.2%
8%	15.4%	14.2%	12.7%	10.9%
10%	12.1%	11.0%	9.6%	7.9%
12%	8.2%	7.3%	6.0%	4.5%

Key sensitivity tables for IC

Every IC presentation needs:

1. Loss sensitivity: CNL vs tranche impairment thresholds
2. Prepayment sensitivity: WAL and yield across CPR scenarios
3. Coverage ratio sensitivity: OC/IC ratios under stress
4. Return sensitivity: Equity IRR across key assumption ranges
5. Break-even table: What has to go wrong for each tranche to lose money

Model validation

A model nobody else can verify is a model nobody will trust.

Back-testing

Run historical periods through your model and compare:

• Projected defaults vs actual defaults
• Projected prepayments vs actual
• Projected cash flows vs actual distributions

If your model consistently over- or under-predicts, recalibrate your assumptions.

Reasonableness checks

• Does the WAL make sense given the asset characteristics?
• Do coverage ratios trend appropriately over time?
• Does excess spread behave as expected under stress?
• Are cumulative losses tracking the default/severity/recovery assumptions?

Documentation standards

Your model needs:

• Assumption log: Source and rationale for every input
• Version control: Track changes, date stamps, who modified what
• Cell documentation: Comments explaining non-obvious formulas
• Audit trail: Clear path from raw data to final outputs

Third-party validation

Rating agencies will stress-test your model or run parallel models. Lenders may require independent model review. Budget 2-4 weeks and $15K-$50K for formal third-party validation.

Common modeling pitfalls

Overfitting to history: Your historical data reflects one economic environment. Stress your assumptions beyond historical experience.

Ignoring timing: A model that correctly predicts 15% cumulative defaults but puts them in the wrong periods will give you the wrong coverage ratios and cash distributions.

Waterfall errors: Misreading the indenture is the most common source of model error. Pay attention to:

• Pre-event vs post-event waterfalls
• Target OC vs floor OC
• Sequential vs pro rata principal
• Interest deferral mechanics

Forgetting fees: A 1% servicing fee and 10bps trustee fee on a $100M pool is $1.1M annually coming out before noteholders get paid. Model all fees.

Excel hell: A 50-tab workbook with circular references, hardcoded values buried in formulas, and no documentation is a liability, not an asset. Structure your model for auditability.

Not stress-testing to failure: You need to know where the deal breaks. If you haven’t found the failure point, you haven’t stressed hard enough.

Tools and platforms

Excel

Still the workhorse for bespoke models. Pros: flexible, accessible, fast to iterate. Cons: error-prone, hard to audit, doesn’t scale to complex structures.

When to use: Unrated warehouse facilities, preliminary structuring, quick scenario analysis.

Intex

Industry standard for rated ABS. Handles complex waterfalls, maintains deal libraries, interfaces with investor systems.

When to use: Any rated transaction, deals where investors require Intex-compatible models, complex multi-tranche structures.

Cost: Platform subscription runs $30K-$100K+ annually depending on coverage.

Bloomberg

Cash flow analytics (CFLOW), scenario tools, market data integration. Good for relative value analysis and surveillance.

Moody’s Analytics

Credit models, portfolio analytics, CDOROM for CLOs. Strong on probability-weighted scenario analysis.

Trepp

CMBS and CLO focused. Loan-level data, deal models, surveillance tools.

Python/R

Custom modeling for automation, integration with data pipelines, machine learning applications. Increasingly used for model development with Excel/Intex for final delivery.

Build vs buy decision

Factor	Build (Excel/Python)	Buy (Intex/Bloomberg)
Deal complexity	Simple warehouse	Complex multi-tranche
Investor requirements	Private bilateral	Rated, syndicated
Ongoing surveillance	Light, internal	Heavy, investor-facing
Budget	<$50K	>$50K/year acceptable
Time to production	2-4 weeks	1-2 weeks (if licensed)

Most practitioners maintain both: a bespoke model for internal analysis and structuring, and Intex for investor-facing materials and rating agency submission.

Presenting cash flow results

Your IC, lenders, and investors need to quickly understand the story. Standard outputs:

1. Cash flow waterfall chart: Period-by-period distributions to each tranche
2. Coverage ratio time series: OC and IC ratios over the deal life
3. Loss and prepayment curves: Cumulative projected vs stressed
4. WAL table: Across prepayment scenarios
5. Sensitivity matrices: The key two-way tables
6. Break-even summary: One-line threshold for each tranche

Keep the narrative tight: here’s what we expect, here’s what happens if we’re wrong, here’s why the structure holds up.