CLO cash flow and waterfall model
Collateral, tests, interest and principal waterfalls. Build a CLO quarter by quarter: a loan portfolio that defaults, prepays and recovers with a lag, reinvested for three years; the interest waterfall with overcollateralisation and interest coverage tests, the closed-form cure that diverts interest to the senior notes and the deferral of unpaid mezzanine interest; the sequential principal waterfall; and the equity IRR and each tranche’s loss under a base, a stress and a severe case, with grids of the equity return and the interest diverted by default rate and recovery.
By Surojit Chakraverti, founder of L3VLUP and an investor running a long-short healthcare and technology equities strategy.
On this subject: clo cash flow and waterfall model.Updated 14 November 2026
Who builds it, and for whatThe model a credit hedge fund, a CLO equity or mezzanine investor or a structured credit desk builds before buying a tranche of a new-issue broadly syndicated CLO, and that a rating adviser or a manager reruns when the portfolio changes. It answers what default and recovery experience the structure can absorb before each tranche stops receiving cash: when the coverage tests first fail, how much interest is then diverted from the junior notes to repay the senior ones, which classes are repaid in full and what the equity earns. The portfolio is the manager’s and the terms are the trustee’s; the arithmetic is the same on every deal.
Every sheet below, in your browser, with its outputs and checks
Starter workbook free with an account. Worked model: one a month with Plus, every one with Pro. Downloads
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Assumptions | ||||||||||||||||||||||||
| 2 | $m unless stated. Quarterly periods. A new-issue broadly syndicated CLO: the portfolio, the notes and every term are invented. | ||||||||||||||||||||||||
| 4 | Quarter | Unit | Close | Q1 | Q2 | Q3 | Q4 | Q5 | Q6 | Q7 | Q8 | Q9 | Q10 | Q11 | Q12 | Q13 | Q14 | Q15 | Q16 | Q17 | Q18 | Q19 | Q20 | Notes | |
| 5 | Quarter number | # | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | Close = 0. The deal is called at the end of quarter 20, five years after close. | |
| 7 | Scenario | ||||||||||||||||||||||||
| 8 | Scenario | # | 1 | 1 = base, 2 = stress, 3 = severe. Selects the default, recovery and prepayment rates on the Scenarios sheet. | |||||||||||||||||||||
| 10 | Portfolio and reinvestment | ||||||||||||||||||||||||
| 11 | Performing par at close | $m | 500.0 | Senior secured loans, bought before close. | |||||||||||||||||||||
| 12 | Weighted average spread over the base rate | bp | 395 | On performing loans. New loans are bought at the same spread. | |||||||||||||||||||||
| 13 | Reinvestment price | % | 99.5% | Cash paid per unit of par on loans bought in the reinvestment period. | |||||||||||||||||||||
| 14 | Liquidation price at the call | % | 98.0% | Of par, on the performing loans sold when the deal is called. | |||||||||||||||||||||
| 15 | Reinvestment period | quarters | 12 | Three years. Recoveries arrive four quarters after a default: a structural lag, not an input. | |||||||||||||||||||||
| 16 | Base rate (three-month reference rate) | % | 3.6% | 3.6% | 3.6% | 3.6% | 3.4% | 3.4% | 3.4% | 3.4% | 3.2% | 3.2% | 3.2% | 3.2% | 3.1% | 3.1% | 3.1% | 3.1% | 3.0% | 3.0% | 3.0% | 3.0% | The path the loans and the notes both float on. | ||
| 18 | Fees | ||||||||||||||||||||||||
| 19 | Senior expenses | bp a year | 4 | Trustee, administration and rating agency fees, on opening performing plus defaulted par. | |||||||||||||||||||||
| 20 | Senior management fee | bp a year | 15 | Paid ahead of the notes, on the same base. | |||||||||||||||||||||
| 21 | Subordinated management fee | bp a year | 25 | Paid after Class E interest and any cure, ahead of equity. The incentive fee is not modelled. | |||||||||||||||||||||
| 23 | Liabilities | ||||||||||||||||||||||||
| 24 | Class A (AAA), principal at close | $m | 315.0 | ||||||||||||||||||||||
| 25 | Class A (AAA), spread over the base rate | bp | 145 | Floating: base rate + spread, paid quarterly. | |||||||||||||||||||||
| 26 | Class B (AA), principal at close | $m | 55.0 | ||||||||||||||||||||||
| 27 | Class B (AA), spread over the base rate | bp | 195 | ||||||||||||||||||||||
| 28 | Class C (A), principal at close | $m | 30.0 | ||||||||||||||||||||||
| 29 | Class C (A), spread over the base rate | bp | 285 | C, D and E defer unpaid interest into principal. | |||||||||||||||||||||
| 30 | Class D (BBB), principal at close | $m | 25.0 | ||||||||||||||||||||||
| 31 | Class D (BBB), spread over the base rate | bp | 415 | ||||||||||||||||||||||
| 32 | Class E (BB), principal at close | $m | 20.0 | ||||||||||||||||||||||
| 33 | Class E (BB), spread over the base rate | bp | 700 | ||||||||||||||||||||||
| 34 | Subordinated notes (equity), invested at close | $m | 55.0 | The residual claim: no coupon, the cash left after everything senior. | |||||||||||||||||||||
| 36 | Coverage test triggers | ||||||||||||||||||||||||
| 37 | Class A/B OC trigger | % | 128.0% | Numerator / notes at and above the class. At close the ratio is 135.1%. | |||||||||||||||||||||
| 38 | Class C OC trigger | % | 119.0% | At close 125.0%. | |||||||||||||||||||||
| 39 | Class D OC trigger | % | 111.0% | At close 117.6%. | |||||||||||||||||||||
| 40 | Class E OC trigger | % | 106.0% | At close 112.4%. | |||||||||||||||||||||
| 41 | Class A/B IC trigger | % | 120.0% | Interest available after senior costs / interest due at and above the class. | |||||||||||||||||||||
| 42 | Class C IC trigger | % | 115.0% | ||||||||||||||||||||||
| 43 | Class D IC trigger | % | 110.0% | ||||||||||||||||||||||
| 44 | Class E IC trigger | % | 105.0% | ||||||||||||||||||||||
Select a cell to see its row, its schedule and the type of cell. Font colours: blue for inputs, black for formulas, green for links to other sheets. Scroll sideways to see every period.
Download
CLO cash flow and waterfall model: the workbook
An Excel workbook with working formulas, no macros and no external links: the same model you can work with on this page.
- Browse without an accountPreview every model online, sheet by sheet, with its outputs and checks.
- Free accountWork with every model online: change the inputs, switch the case, see every formula. And every starter workbook, with its key schedules left blank to build.
- L3VLUP Plus$4.99/monthEverything in Free, and one worked model each billing month as an Excel file: every schedule built, formulas live, checks at zero, yours to keep.
- L3VLUP Pro$25/monthEverything in Plus, every worked model in the library, and the Pro applications.
- The worked model
- 11 sheets and 35,169 live formulas, all 5 schedules built and linked, every check reading zero. Change an input in Excel and the whole model moves.
- The starter
- The same inputs and layout with the cLO coverage tests: OC and IC ratios and the cure left blank for you to build, each blank cell marked. The worked model is its answer.
What the base case says
- Equity IRR (annualised)
- 11.2%
- Equity cash-on-cash multiple
- 1.4x
- Class A principal loss
- $0.0m
- Class B principal loss
- $0.0m
- Class C principal loss
- $0.0m
- Class D principal loss
- $0.0m
- Class E principal loss
- $0.0m
- First quarter any test fails
- never
- Quarters in which interest is diverted
- 0
- Interest diverted to the notes, total
- $0.0m
- Cumulative defaults, % of original par
- 9.1%
- Cumulative loss, % of original par
- 3.2%
Read from the workbook as served, every input at its default. Periods: Close, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13, Q14, Q15, Q16, Q17, Q18, Q19, Q20. The figures are invented and move with whatever you type in.
What this workbook builds
- Collateral roll-forward
- Collateral sheet
- Performing par defaulting and prepaying quarterly, recoveries received four quarters after default, reinvestment at a price, interest and the sale at the call.
- Coverage tests and the cure
- Coverage Tests sheet
- OC and IC ratios for Class A/B, C, D and E against their triggers, and the closed-form paydown that diverts interest to the senior notes, capped by the cash left.
- Interest waterfall
- Interest Waterfall sheet
- Senior costs, interest on five classes with the cure after each test, deferral of unpaid C, D and E interest into principal, the subordinated fee and the residual to equity.
- Principal waterfall and notes
- Principal Waterfall sheet
- The diverted interest allocated to the notes in order, the sequential principal waterfall after the reinvestment period, each class’s balance and the equity cash flow.
- Sensitivity grids
- Sensitivity sheet
- Equity IRR and interest diverted to the senior notes by default rate and recovery rate, each cell a full run of the waterfall on the Grid runs sheet.
What this model is
A quarterly model of an invented new-issue broadly syndicated CLO: a $500 million portfolio of senior secured loans funded by Class A to E notes and $55 million of subordinated notes, with a three-year reinvestment period and a call at the end of year five. The question is what default and recovery experience the structure can absorb before each tranche stops receiving cash.
The portfolio defaults at a constant annual rate applied quarterly, prepays at a constant rate and recovers a share of each defaulted loan four quarters after the default. During the reinvestment period principal proceeds buy new loans at a reinvestment price; afterwards they repay the notes in order.
The interest waterfall pays senior costs, Class A and B interest, then tests Class A/B overcollateralisation and interest coverage. A test that fails diverts interest to repay the senior-most notes by the amount that restores the ratio, capped by the cash left. Class C, D and E interest and tests follow, then the subordinated fee and the residual to equity. Unpaid C, D and E interest capitalises into the note.
Outputs are the equity IRR and cash-on-cash multiple and, for each rated tranche, its principal loss, whether it is repaid in full and the first quarter a test fails. Three cases (base, stress, severe) and two grids, each cell a full run of the waterfall: equity IRR and interest diverted by default rate and recovery rate. The incentive fee and the portfolio’s loan-by-loan detail are not modelled.
Seats: Equity research and hedge funds, Private equity, Investment banking.
Careers that do this work: investment banking, debt capital markets, restructuring, private credit, distressed & special situations, private equity.
How the schedules connect
Every row in the workbook is tagged with the schedule it belongs to; the inspector above shows the tag when you click a row. These are the schedules this model is made of and where each one lives.
CLO collateral roll-forward: defaults, recoveries and reinvestment
Performing par, defaults, a recovery lag, reinvestment
Assumptions, rows 11–16 · Collateral, rows 6–10 · Collateral, rows 13–14 · Collateral, rows 17–22 · Collateral, rows 25–28 · Collateral, rows 31–35 · Collateral, rows 38–40
CLO coverage tests: OC and IC ratios and the cure
OC and IC tests by class and the interest they divert
Assumptions, rows 37–44 · Coverage Tests, row 6 · Coverage Tests, rows 9–16 · Coverage Tests, rows 19–28 · Coverage Tests, rows 31–40 · Coverage Tests, rows 43–52 · Coverage Tests, row 55
CLO interest waterfall: costs, notes, cures and equity
Costs, five classes of interest, deferral, the residual
Assumptions, rows 19–21 · Assumptions, rows 24–34 · Interest Waterfall, rows 6–15 · Interest Waterfall, rows 18–27 · Interest Waterfall, row 30 · Interest Waterfall, rows 33–37 · Interest Waterfall, rows 40–44 · Interest Waterfall, rows 47–51 · Interest Waterfall, rows 54–57
CLO principal waterfall: sequential paydown and the call
Diverted interest, sequential principal, class balances
Principal Waterfall, row 6 · Principal Waterfall, rows 9–15 · Principal Waterfall, rows 18–23 · Principal Waterfall, rows 26–35 · Principal Waterfall, row 38
Sensitivity tables
Two assumptions at once, without a data table
Sensitivity, rows 5–6 · Sensitivity, rows 9–12 · Sensitivity, rows 16–19 · Sensitivity, row 22
What you should be able to explain
- Why a CLO is a waterfall of cash by priority and not a set of independent loans: interest and principal each have an order, and the order is the product.
- How the overcollateralisation ratio is built (performing par, principal cash and defaulted par at its recovery value, over the notes at and above a class) and why defaulted loans reduce it only to the extent they will not recover.
- What a failing coverage test does: it does not stop the deal, it redirects interest from the equity and the junior notes to repay the senior notes until the ratio is restored, which protects the senior classes at the equity’s expense.
- Why Class C, D and E interest can be deferred into principal while Class A and B interest cannot, and what the deferral does to the balance the OC test is run on.
- What the reinvestment period does: principal proceeds buy new loans, so prepayments do not shorten the deal and a price below par adds to par, until the sequential principal waterfall begins.
- Why recoveries arrive after a lag, and why the equity’s return is so much more sensitive to the default rate than any rated class is.
What a reviewer looks for
- Defaulted loans left in the OC numerator at par, or removed altogether, instead of carried at the recovery assumption.
- Coverage tests run on closing balances after the cash has already been paid out, so the cure arrives a quarter late and nothing is diverted when it should be.
- A cure that pays down notes without capping it by the cash left, so interest is diverted that was never collected.
- Mezzanine interest paid in full from cash that should have gone to the cure, or deferred interest left out of the balance the next test runs on.
- Recoveries received in the quarter of the default, which brings forward cash the structure does not have and understates the loss in the first years.
- Principal proceeds reinvested after the reinvestment period, or distributed to equity while a rated class is still outstanding.
- An equity IRR read from a single case: it falls from 11.2% to a loss of 92% across the three cases on this page, so the grid is the answer and the base case is one cell of it.
Conventions this workbook uses
Stated on the cover sheet too. A model is only as trustworthy as the decisions it tells you it made.
- Quarterly periods. Close is quarter 0 and the deal is called at the end of quarter 20. Interest accrues on opening balances at (base rate + spread) / 4, so nothing is circular.
- Defaults are the opening performing par x the quarterly default rate, 1 - (1 - annual rate) ^ (1/4). Prepayments are performing par after defaults x the quarterly prepayment rate. Defaulted loans earn nothing and are resolved four quarters after the default, when the recovery rate is received in cash; the rest is the realised loss.
- In the reinvestment period, prepayments and recoveries buy new loans at the reinvestment price at the same spread; interest on them starts the next quarter. After it, principal repays the notes sequentially, Class A first, and equity receives principal only once every rated class is repaid.
- At the call the remaining performing loans are sold at the liquidation price, defaulted loans still outstanding realise their recovery rate at once, and the proceeds run through the sequential waterfall.
- OC ratio = (performing par after defaults + principal cash + defaulted par x the recovery rate) / opening notes at and above the class, including deferred interest. IC ratio = interest available after senior costs / interest due at and above the class. A test fails when either is below its trigger.
- A failing test diverts interest to repay the senior-most notes. The OC cure is notes - numerator / trigger, the repayment after which the ratio equals the trigger; the IC cure is the repayment that removes enough Class A interest to restore the ratio. The larger is paid, capped by the cash left and by the notes outstanding, and diversions already made count towards later tests in the same quarter.
- Class A and B interest cannot be deferred: a shortfall is carried as arrears and paid first when cash allows. Class C, D and E interest that cannot be paid is capitalised into the note (PIK) and earns interest from the next quarter. Fees carry arrears the same way.
- Equity IRR is the IRR of the quarterly cash flows annualised as (1 + IRR) ^ 4 - 1. A tranche’s principal loss is the balance still outstanding after the call, deferred interest included.
What this model does not cover
Where its answer stops, so you know what to add before relying on it.
- The incentive fee. It is paid to the manager once the equity has earned a hurdle IRR, so it depends on the equity’s own return and cannot be computed on opening balances without iteration. The management fees are modelled; the incentive fee is not.
- Loan-by-loan detail. The portfolio is one pool with one weighted average spread, one default rate and one recovery rate; there is no rating migration, no CCC bucket and no market value test.
- Reinvestment tests and trading. Reinvestment is at one price and spread, with no weighted average life, diversity or rating constraint, and no discretionary sales.
- Day counts, payment dates and floors. Interest accrues at (base rate + spread) / 4 and there is no base rate floor or hedge.
- Optional redemption, refinancing and reset. The deal is called at the end of year five at one liquidation price.
- A real deal. The portfolio, notes and terms are invented; a case from a deal’s offering memorandum and trustee reports would have to restate every one of them.
Build it yourself
The starter workbook
The Coverage Tests sheet has been cleared: the OC numerator, and for Class A/B, C, D and E the notes at and above the class, the OC and IC ratios, whether the test fails and the closed-form cure that decides how much interest is diverted to the senior notes. Rebuild it from the Assumptions, Collateral and Interest Waterfall sheets, then switch to the severe case and watch the diversion repay Class A while the equity is starved.
Blanks: CLO coverage tests: OC and IC ratios and the cure. Download it beside the worked model.
The path around this model
Understand it, drill it, read the build, then apply it to a real company.
Build · Lab · ~15 min
Capital Stack Challenge
Finance a real buyout from revolver to equity, hold it through a shock, and explain every layer against the deal the sponsor actually did.
Read · Guide · 11 min
Credit and Covenant Modelling: What a Lender Actually Tests
Read · Guide · 10 min
Scenario and Sensitivity Architecture: Building a Model That Can Be Stress-Tested
Read · Guide · 11 min
How to Review a Financial Model Before It Leaves Your Desk
Apply · Skill
Model Audit
Find the errors in a financial model before someone senior does.
Vocabulary: Collateralised Loan Obligation (CLO), Overcollateralisation Test, Excess Spread, Securitisation, Cash Flow Waterfall, PIK Interest, Mezzanine Debt, Interest Coverage Ratio, Loss Given Default (LGD), Credit Spread, Private Credit, Contractual Subordination, Distressed Debt.
Questions about this model
What does the equity earn in the base case?
With defaults at 2.0% a year, recoveries of 65% and prepayments of 20%, the subordinated notes return 11.2% a year, 1.43x the $55 million invested: $44.2 million of interest residual through the life of the deal and $34.7 million of principal at the call. Defaults reach 9.1% of the original par and the realised loss is 3.2%, yet no test fails in any quarter: the tightest Class A/B OC ratio is 132.5% against a 128.0% trigger, and every rated class is repaid in full.
What happens in the stress case?
Defaults of 4.0% a year and recoveries of 50% fail the Class C test in quarter 11 and the Class E test in quarter 17. $7.5 million of interest is diverted to repay Class A over 7 quarters, and the equity loses 6.9% a year and gets back 0.81x its money. The rated notes lose nothing; the cost falls on the equity, which is what the structure is for.
What does the severe case do to the waterfall?
Defaults of 7.0% a year, recoveries of 35% and prepayments of 10% fail a test in quarter 5, and the diversion logic does the work: $47.3 million of interest is diverted to the senior notes over 16 quarters, Classes A to D are repaid in full and the equity returns 0.17x, a loss of 91.8% a year. The mezzanine classes defer interest rather than default: $9.9 million of Class E interest and $8.4 million of Class D interest is capitalised, and Class E leaves $23.2 million outstanding at the call.
How much can the portfolio lose before a tranche stops being paid?
Read the grids, which run the whole waterfall at each default and recovery rate. Holding prepayments at 20% and recovery at 65%, the equity returns 18.2% with no defaults, 11.2% at 2.0% a year and 2.5% at 4.0%, and has lost money by 6.0%. Ten points of recovery moves the base IRR between 9.3% and 13.0%. The tests start to bite, and interest to move from the equity to the senior notes, only as defaults mount: nothing is diverted at 4.0% with a 65% recovery, then $6.0 million at 6.0% a year and $21.3 million at 8.0%.
How is the overcollateralisation test built, and how is the cure worked out?
The ratio is performing par after this quarter’s defaults, plus principal cash, plus defaulted par at the recovery rate, over the opening notes at and above the class, deferred interest included. If it is below its trigger, interest is diverted to repay the senior-most notes by the notes less the numerator divided by the trigger: the repayment after which the ratio equals the trigger. The interest coverage test is cured in the same way from the interest due. The larger cure is paid, capped by the cash left and by the notes outstanding.
Related models
Credit and restructuring · Advanced
Recovery waterfall model
Claims by class, priority, recovery, the fulcrum
Credit and restructuring · Advanced
Credit model
Debt capacity, covenants, sweep, maturities, recovery
Financial institutions · Advanced
Consumer lending model: loan vintages
Vintages, loss curves, allowance, advance rates, ROE, P/TBV
M&A and private equity · Advanced
Private equity fund model: cash flows, J-curve and waterfall
Capital calls, fees, NAV, the J-curve, carry and clawback
Questions about this model
Discussions open here soon.