A free distribution waterfall model: preferred return, catch-up and carried interest
The four tiers step by step, three readings of the same "20% promote" priced in dollars, and a European against a deal-by-deal waterfall on the same five assets.
A fund returns $170m on $100m of capital. Run the waterfall in the usual order and the limited partners receive $152.8m and the manager $17.2m: 24.6% of the profit, not the 20% the documents seem to promise. The difference sits entirely in how the catch-up is defined. This template computes the waterfall and prices that definition.
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.
Fund_Waterfall_Model.xlsx (23 KB) · a copy for Google Sheets
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 project or fund? The Pro Editions go further: Development (60 months, S-curves, senior and mezzanine loans, JV waterfall), $99 → · Fund (up to eight assets, European or American waterfall), $129 →
The book behind this model
This file is one worked case from Real Estate Fund Management. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
Go further: European and American waterfalls, accrued carry and clawback
Private Fund Finance & Controllership: capital calls and equalization, fees and offsets, both waterfalls, NAV and capital accounts, IRR and PME, fund financing and ILPA reporting, on one fictional fund. Course Book, Excel exercises that check your answers, a 160-question quiz and the Private Equity Fund Model, Pro Edition included ($199).
Eight modules on one logistics park: rent roll and NOI, cap rate and DCF, LTV, DSCR and debt yield, levered returns, a development with its construction loan, the JV waterfall and quarterly NAV. Excel exercises that check your answers, a 160-question quiz and the Real Estate Acquisition and Development Model, Pro Edition included ($149).
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).
Real Estate Fund Model, Pro Edition: a closed-end fund with up to eight assets, fees, European or American waterfall, 30 built-in checks, a blank copy and a 19-page guide. $129 → · See what is inside
REPE Interview Prep Pack: 18 real estate PE interview cases solved step by step, with three Excel models. $129 →
Real Estate Acquisition and Development Model, Pro Edition: monthly ground-up model over 60 months, S-curves, senior and mezzanine loans, bulk or unit sale, JV waterfall, 33 checks, a 14-page guide. $99 → · See what is inside
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 10 paperbacks by Julian R. Sterling on tax and incentives, printed on demand and shipped worldwide. Browse the bookshop →
The waterfall, tier by tier
Tier
To LPs
To GP
1. Return of capital
$100.0m
2. Preferred return (8% simple, 5 years)
$40.0m
3. GP catch-up (100%)
$14.0m
4. Residual split 80/20
$12.8m
$3.2m
Total
$152.8m
$17.2m
Three readings of "a 20% promote"
Same fund, same distributions, same pref. Only the target of the catch-up changes:
Reading
GP total
GP share of profit
A. Catch-up to 20% of all profit, then 20% of the rest
$17.2m
24.6%
B. Catch-up stops when the GP is at 20% counting the pref
$14.0m
20.0%
C. No catch-up: carry on profit above the pref only
$6.0m
8.6%
None of the three is wrong. The point is that the phrase does not settle the question, and the wording is worth $3.2m to the manager against reading B on a fund this size.
This is the sheet to bring to a term-sheet negotiation.
European against deal-by-deal
Five assets, two sold early at a profit and three sold later at breakeven. Under a European waterfall the manager earns $6.24m, paid only once the whole fund is whole.
Deal by deal it earns $11.04m, most of it banked years before the losers are sold. The $4.8m difference is what a clawback would have to recover, from money that may already have been distributed.
What is in the file
Waterfall
The four tiers in the order the documents apply them, with a line that proves every dollar is allocated.
Catch-Up Definitions
The three readings side by side, and the cost of each to the LP.
Sequencing Risk
The five-asset fund under both waterfall types, and the clawback.
Checks
Every figure the book prints against the model, each marked OK.
How to use it
On Waterfall, type your capital, total distributions, pref rate, fund life, promote and catch-up rate in the blue cells.
Check the GP share of profit. If it is above the headline promote, the catch-up is reading A.
On Sequencing Risk, replace the five assets with your deals to see how much carry a deal-by-deal structure pays early.
Questions people ask about it
Why does the GP get more than 20%?
Because in the most common construction the catch-up first brings the GP to 20% of all profit, and the GP then also takes 20% of what is left. Reading B avoids this.
Is the preferred return compounded?
In this example it is 8% simple on contributed capital, as the book works it. Change the input to model your own terms.
Does it compute an IRR-based hurdle?
No. It follows a simple pref on a single contribution and a single distribution, so every tier can be checked by hand. The same companion set includes models with dated flows.
Is it free?
Yes. It costs nothing: an email address opens the download.
The rest of the files
This template is one of the companion files for Real Estate Fund Management: the full set adds
debt sizing and covenants, underwriting and exit sensitivity, asset management and fund operations, and the fund definitions priced. 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.