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.

Julian R. Sterling · from Real Estate Fund Management

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.

Download the template

One Excel file, no macros, nothing locked. No account, no email address.

Fund_Waterfall_Model.xlsx19 KB

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, with no sign-up.

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.

Open the companion files →

Other free templates

LBO modelSources and uses to sponsor IRR, twelve sheets, every figure checked.
Real estate property modelRent roll, cash flow, debt and returns for one asset, empty and ready to fill.
Residual land valuationDevelopment appraisal with the four downside tests and peak debt.
DCF with terminal valuePerpetuity growth and exit multiple side by side, with the TV share of value.
Loan sizing: LTV, LTC, DSCR, debt yieldThe four lender tests at once, the binding one, and a rate shock.
Direct lending credit modelUnitranche underwriting: coverage, leverage, sweep, base, downside and stress.
Data center financial modelTurnkey, powered shell and powered land on one campus, levered returns and delay.
13-week cash flow modelLiquidity on paper against usable liquidity, a springing covenant and six levers.
Small business acquisition model (SBA loan)SBA 7(a) loan sized in closed form, lender coverage against cash coverage, exit returns.
3-statement financial modelFive-year forecast feeding a DCF and an LBO on one case, with a blank build.
Project finance modelSolar and storage project, debt sculpted to a 1.35x DSCR, no circularity.
REIT valuation modelFFO, AFFO, NAV and the implied cap rate for one REIT, plus an empty template.
Self-storage financial modelCohort rent roll, break-even occupancy, ten-year hold and the bid for one store.
Tax lien certificate yield calculatorBid, minimum charge and redemption month turned into a real annual yield.
Sale and leaseback modelNine buildings, the price split five ways, rent cover on the entity that signs.

This template comes from Real Estate Fund Management. The book is on Amazon.

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.