Free Excel templates

A free CLO and ABS waterfall model in Excel: three deals, tranche by tranche

An auto ABS run monthly at two default rates, a CLO run quarterly under base, stress and severe default paths with its coverage tests, and a non-QM RMBS paying sequentially: ten sheets, no macros.

The file runs three fictional securitisations through their waterfalls. Tallis, a $500 million auto ABS at 6.5% annual defaults and 45% recovery, loses 7.9% of the pool over its life and pays every class in full; at 1.4 times the defaults the loss rises to 10.7% and the senior class still repays faster. Merrow, a $400 million CLO, returns 12.0% to its equity with a 1.70 multiple in the base case, with no failing coverage test and no rated note unpaid. Harborline, a non-QM RMBS, pays sequentially under base and stress.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

ABS_CLO_RMBS_Waterfall_Model.xlsx (733 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This file is one worked case from The Structured Credit Analyst. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Apple Books → Or on Google Play

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 19 paperbacks by Julian R. Sterling on markets, funds and compliance, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

Ten sheets. Every assumption is on Inputs, in blue on a light blue background.

Read Me and Inputs
The three pools, the capital structures, prepayment and default rates, recovery, triggers and the CLO default paths.
Auto ABS and Auto ABS 1.4x
Tallis month by month: pool, defaults, recoveries, interest and principal down the classes, overcollateralization and the residual.
CLO Base, CLO Stress and CLO Severe
Merrow quarter by quarter: par, defaults, interest, the OC and IC tests, diversion and the equity distributions.
RMBS and RMBS Stress
Harborline month by month, sequential pay, base and stress.
Checks
The book's figures against the formulas: all OK.

What the worked case returns

MeasureValue
Tallis class A WAL1.47 years
Tallis class D WAL4.67 years
Tallis lifetime net loss, base7.9%
Tallis lifetime net loss, 1.4x defaults10.7%
Tallis overcollateralization at month 12$35.8m
Merrow equity IRR, base12.0%
Merrow equity multiple, base1.70x
Merrow cumulative par loss, base$17.1m

Read the stress sheets against the base. The waterfall protects the senior classes by diverting interest when a coverage test fails, which is why the equity, not the notes, absorbs the first change in defaults.

How to use it on your own deal

  1. Enter your pool, capital structure and assumptions on Inputs.
  2. Run the base case and read the tranche WALs and losses.
  3. Switch to the stress paths and watch when coverage tests fail and cash is diverted.
  4. Compare the equity return across paths before pricing the bottom of the stack.

Questions people ask about it

Is this template really free?

Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Does it use macros or circular references?

No macros and no circular reference: each period is a row, and each class is paid from what the classes above it leave.

What does an OC test do in a CLO?

It compares the par of the collateral with the notes above a given point in the stack. When it fails, interest that would have gone to the junior notes and the equity is diverted to pay down the senior notes until the test is cured.

Can I use it on a real deal?

You can reuse the structure. The deals and every figure are fictional and simplified, and nothing here is investment advice.

The rest of the files

This template is the companion workbook for The Structured Credit Analyst; the companion page also carries the other files that go with it. Free, like this one.

Open the companion files →

Other free templates

This template comes from The Structured Credit Analyst. The book is on Apple Books and Google Play.

If this book helped, or didn’t, a rating where you bought it is worth more than it looks: it is what the next reader goes on. The template stays free either way.