A free fulcrum security and recovery waterfall model in Excel, with worked cases
A capital structure in strict priority, one enterprise value, and a waterfall that names the fulcrum by arithmetic, then the same structure across seven enterprise values and two worked cases from the book: six sheets, no macros.
The file walks an enterprise value down a capital structure in strict priority and names the fulcrum, the tranche where value runs out, without you choosing it. In the book's worked case a levered manufacturer has filed to reorganise with a going-concern value of $600 million against $850 million of claims: $50 million of DIP and administrative claims, a $400 million first-lien term loan, $250 million of senior unsecured notes and $150 million of subordinated notes. Both senior layers recover par, the senior unsecured notes are the fulcrum at 60.0 cents, and the subordinated notes and the equity get nothing. Bought at 45 cents, the fulcrum has 33.3% of upside to recovery; at $750 million the break drops to the subordinated notes, at $450 million it climbs to the boundary of the first lien.
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.
Fulcrum_Security_Recovery_Waterfall.xlsx (22 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.
Need the full deal structure? The LBO Model, Pro Edition adds four debt tranches, covenants, a management ratchet and 31 checks, with no circular reference and no macros. $99 → · Watch the 60-second preview
The book behind this model
This file is one worked case from The Distressed Debt Investor. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
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).
LBO Model, Pro Edition: seven-year buyout model with four debt tranches, covenants, a management ratchet, 31 checks, a blank copy and an 18-page guide. $99 → · See what is inside · Watch the 5-minute demo
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Six sheets. Inputs are blue type on a yellow fill; black is a formula and green links to another sheet. Clear the inputs and the model empties out: nothing is hard-coded to force the book's answer.
Read Me
What each sheet does, the source chapters and the colour convention.
Fulcrum Finder
Enterprise value, less professional-fee leakage and a priming DIP, gives the value available to the pre-petition stack. The waterfall shows cumulative claim, value reaching each tranche, recovery in amount and in cents, and a status (money-good, fulcrum, out of the money). Market prices set against recoveries give the upside on each tranche.
EV Ladder
The same structure across seven enterprise values, from 400 to 900, with the recovery of every tranche and the fulcrum named in each column. This is where you watch the break migrate.
Chapter 6 Example
The $600 million manufacturer of Chapter 6 and the two flexes the chapter runs: $750 million, where the fulcrum drops to the subordinated notes, and $450 million, where value runs out exactly at the first lien.
Appendix B
A second structure (first lien 350, senior unsecured 300, subordinated 150) at an enterprise value of 600, with the printed market prices of 92, 48 and 12 against the recoveries, and the flexes to 450 and 780.
Checks
Sixteen recoveries the book prints against what the workbook computes, and four internal tests: the fulcrum is found by arithmetic, the waterfall never distributes more than the enterprise value, recoveries fall with priority, and there is exactly one fulcrum.
What the worked cases return
Measure
Value
Going-concern enterprise value (Chapter 6)
$600m
Claims in priority
$850m
DIP and first-lien term loan recovery
100.0% (par)
Fulcrum: senior unsecured notes
60.0 cents ($150m on $250m)
Upside to recovery at a 45-cent price
33.3%
Subordinated notes and equity
0.0%
Flex to $750m: subordinated notes become the fulcrum
33.3 cents
Flex to $450m: senior unsecured notes
0.0% (break at the first lien)
Appendix B at 600: senior unsecured against a 48-cent price
83.3 cents, 73.6% upside
Appendix B at 780: subordinated notes
86.7 cents
Read the ladder rather than the single case. One variable, enterprise value, moves the fulcrum a full tranche in each direction: the senior unsecured notes recover 60.0 cents at $600 million, par at $750 million and nothing at $450 million. The first lien recovers par at every value from $450 million up, which is what seniority buys. The decision the sheet supports is which tranche sits at the seam for the enterprise value you can defend, and how far that value can fall before the paper you bought converts to nothing.
How to use it on your own case
Open Fulcrum Finder and enter the capital structure top to bottom in strict priority: face of each tranche, then your conservative going-concern enterprise value.
Add professional-fee leakage and any priming DIP. Both come off the top before the pre-petition stack sees any value.
Read the Status column: it names the fulcrum. Enter market prices to see the upside or loss to recovery on each tranche.
Set the seven enterprise values on EV Ladder to the range you think plausible and read the bottom row: where the fulcrum sits at each value.
Expect Checks to go out once you change the book's inputs. The internal tests (no over-distribution, monotonic recoveries, one fulcrum) should still read OK on your own structure.
Questions people ask about it
Is this fulcrum security model 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.
How does it find the fulcrum without a macro?
No macros. Each tranche receives the lesser of its face and the value left after the cumulative claims ahead of it. The tranche that is reached but not filled is the fulcrum; when value runs out exactly at a tranche boundary, the model says so instead of naming one. The file recalculates in Excel, LibreOffice or Google Sheets.
Why is the fulcrum not simply the cheapest bond?
Price and recovery are different things. In the Appendix B case the subordinated notes trade at 12 cents and recover nothing at an enterprise value of 600, while the senior unsecured notes at 48 cents recover 83.3. Cheap and impaired to zero are compatible; the fulcrum is wherever value breaks, and it moves when the value does.
Can I use it for a real restructuring?
You can reuse the structure. The companies, the capital structures and every figure in them are fictional, taxes and fees are ignored as the book does, and nothing here is investment advice.
The rest of the files
This template is one of the companion files for The Distressed Debt Investor: the full set adds the recovery and return workbook (fee leakage, DIP priming, form and timing of recoveries), the valuation and liquidation floor workbook, and Which Tranche to Buy, which prices the three bets of Appendix B. Free, like this one.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
If this book helped — or didn’t — a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.