Private Equity Waterfall in Excel: Follow $160 Million Through All Four Tiers
A fund sells an investment. There is $160 million available to distribute. The limited partners (LPs) invested $100 million, and the general partner (GP) has negotiated 20% carried interest.
Should the GP receive $12 million — 20% of the $60 million profit — immediately?
Only if the distribution waterfall has reached the part of the agreement where that outcome is permitted. Before that point, the LPs may have priority over the entire next dollar. A fund that exits at $120 million instead of $160 million produces a surprisingly different answer: the GP might receive only $3.36 million, even though 20% of the $20 million profit would be $4 million.
This article follows the same fund and the same Excel model from the first dollar to the final reconciliation. The example is intentionally simple; real LPAs define the result, and fees, GP co-investment, multiple capital calls and interim distributions must eventually be layered on.
If you have never built a waterfall, read the numbers first. The formulas will make sense after you have watched the cash move.
Our Fund: One Capital Call, One Exit
Assume the following illustrative terms:
| Input | Value | What it means |
|---|---|---|
| Capital contributed by LPs at Year 0 | $100.00m | All contributed on the same date |
| Gross proceeds available at Year 2 | $160.00m | After deal costs; the simplified fund has no fees or taxes |
| Preferred return | 8% | Compounded annually on outstanding contributed capital |
| Carried interest | 20% | GP's economic share once fully caught up |
| GP catch-up | 100% | GP receives every dollar within the catch-up tier |
| GP co-investment | $0 | Excluded only to isolate carried interest |
| Distribution structure | Whole-of-fund | All LP capital must first be returned |
There are no interim distributions, no additional calls and no management fees in this baseline. We can therefore use two annual compounding periods rather than solve a dated IRR hurdle.
The $160 million proceeds consist economically of $100 million returned capital and $60 million profit. But the $60 million profit is not simply divided 80/20 on arrival. The LPA sends cash through four sequential tiers.
Here is the complete result. The sections below build every row.
| Waterfall tier | Cash entering tier | LP receives | GP receives | Cash remaining |
|---|---|---|---|---|
| 1. Return of capital | $160.00m | $100.00m | $0.00m | $60.00m |
| 2. Preferred return | $60.00m | $16.64m | $0.00m | $43.36m |
| 3. 100% GP catch-up | $43.36m | $0.00m | $4.16m | $39.20m |
| 4. 80/20 residual | $39.20m | $31.36m | $7.84m | $0.00m |
| Total | $148.00m | $12.00m | $0.00m |
First reconcile the cash: $148m + $12m = $160m.
Then reconcile the profit: the LP has received its $100 million capital plus $48 million profit. The GP has received $12 million of carry. The profit split is $48m / $12m = 80% / 20%. The GP's 20% is calculated on fund profit, not on the full $160 million proceeds.
Now rewind and watch the tiers fill.
The First $100 Million Is Not Profit
The LP contributed $100 million. Under the assumed whole-of-fund LPA, the first $100 million of distributable cash returns that capital.
Cash available 160.00
Less capital returned to LPs (100.00)
-------
Cash entering Tier 2 60.00
This is return of capital, not preferred return and not carried interest.
The distinction becomes important when a fund makes several capital calls. Commitment is an agreement to invest; contributed capital is what has actually been funded. Our example assumes the whole $100 million was called on day one. A production model should track each actual contribution date and the precise definition of capital that the LPA requires to be returned — often including specified expenses or prior distributions.
At this stage, the GP has received nothing as carried interest. We have $60 million left.
The Next $16.64 Million Belongs to the LP
The LPA says an 8% preferred return, compounded annually for the two years the $100 million was outstanding.
Preferred return
= $100m × ((1 + 8%)^2 - 1)
= $16.64m
The amount is not $8 million in the first year plus another $8 million in the second. Compounding earns a return on the first year's accrued preference:
End of Year 1 preference $8.00m
Additional Year 2 preference $8.64m
-------
Total preference $16.64m
All $16.64 million goes to the LP. Only then can the GP enter its catch-up tier.
Cash after returning capital $60.00m
LP preferred return ($16.64m)
-------
Cash left for catch-up and split $43.36m
A preferred return is generally a distribution priority, not a guarantee that an investment will earn 8%. If the fund realizes too little cash to pay it, the LP may simply receive less. The LPA determines how any shortfall is carried into future periods if the fund continues.
Also, an 8% compounded preferred return is not automatically an 8% XIRR hurdle when there are several contributions and distributions. Model the exact term in the LPA.
Why the GP Receives $4.16 Million Before the 80/20 Split
The LP now has $16.64 million of profit distributions, while the GP has received zero. If every remaining dollar were immediately split 80/20, the GP would never catch up on the profit already paid exclusively to the LP.
Our assumed agreement solves this with 100% GP catch-up: every dollar in the next tier goes to the GP until the GP represents 20% of cumulative profit distributed.
Let C be catch-up cash paid entirely to the GP.
C / ($16.64m + C) = 20%
C = $16.64m × 20% / (1 - 20%)
= $4.16m
You can check the result without trusting the formula:
LP profit already distributed $16.64m
GP catch-up $4.16m
-------
Total profit so far $20.80m
GP share: $4.16m / $20.80m = 20%
The GP has finally reached its 20% share of profit distributed so far. Only now does an ordinary 80/20 split of the rest maintain the agreed sharing ratio.
Of the $43.36 million that entered the catch-up tier, $4.16 million goes to the GP and $39.20 million remains.
The catch-up calculation uses the preferred return actually paid under the relevant cumulative LPA definitions; a multi-period model must carry forward prior preferred and catch-up payments rather than recalculate each year as though none occurred.
The Remaining $39.20 Million Finally Splits 80/20
Once the GP is fully caught up, every extra dollar follows the residual sharing ratio.
Remaining cash $39.20m
LP: $39.20m × 80% $31.36m
GP: $39.20m × 20% $7.84m
Put all four tiers together:
LP capital returned $100.00m
LP preferred return $16.64m
LP residual share $31.36m
-------
TOTAL LP $148.00m
GP catch-up $4.16m
GP residual share $7.84m
-------
TOTAL GP $12.00m
The LP's profit is $48 million and the GP's is $12 million. Our simplifying assumptions happen to produce an exact 80/20 split of the $60 million fund profit because the proceeds were enough to complete catch-up.
They would not produce that result at every exit value.
Change Only the Exit Proceeds: $112m, $120m and $160m
Keep every other assumption identical. It is still the same $100 million contribution, the same Year 2 distribution date, and the same 8% preference and 20% carry. Only the exit proceeds change.
| Exit proceeds | Capital to LP | Pref paid to LP | GP catch-up paid | Residual LP | Residual GP | Total LP | Total GP |
|---|---|---|---|---|---|---|---|
| $112m | $100m | $12.00m | $0.00m | $0.00m | $0.00m | $112.00m | $0.00m |
| $120m | $100m | $16.64m | $3.36m | $0.00m | $0.00m | $116.64m | $3.36m |
| $160m | $100m | $16.64m | $4.16m | $31.36m | $7.84m | $148.00m | $12.00m |
At $112 million, the fund cannot fully satisfy the $16.64 million preferred return. The LP receives every available dollar. No GP carry is paid.
At $120 million, the preference has been paid but there is only $3.36 million left. The GP takes it all because catch-up is not yet complete. The GP has received 16.8% of the $20 million fund profit, rather than 20%.
At $160 million, catch-up finishes and the residual split begins. The GP receives the full $12 million.
These are three regions of the waterfall. The breakpoints in our example are:
$100.00m Capital fully returned
$116.64m Capital + preferred return paid
$120.80m Capital + preference + full catch-up paid
> $120.80m Residual 80/20 split begins
That is the intuition an Excel waterfall needs before anyone writes a nested IF formula.
Build These Four Tiers in Excel
Use one input column and one calculation column. Amounts below are in millions of dollars. This is an entire one-exit waterfall, not a full fund administration model.
| Cell | Label | Value or Excel formula |
|---|---|---|
| B2 | LP capital contributed | 100 |
| B3 | Exit cash available | 160 |
| B4 | Years outstanding | 2 |
| B5 | Annual compounded preference | 8% |
| B6 | Carry percentage | 20% |
| B7 | GP share within catch-up tier | 100% |
Then paste the tier calculations below. Set Column A to the corresponding labels.
B10 =MIN(B3,B2) // Return of capital to LP
B11 =MAX(0,B3-B10) // Cash after capital
B12 =B2*((1+B5)^B4-1) // Preferred return entitlement
B13 =MIN(B11,B12) // Preference actually paid
B14 =MAX(0,B11-B13) // Cash after preference
B15 =IF(B7<=B6,NA(),B13*B6/(B7-B6)) // Cash required in catch-up tier
B16 =MIN(B14,B15) // Actual catch-up-tier cash
B17 =B16*B7 // GP catch-up receipt
B18 =B16*(1-B7) // LP share of catch-up tier
B19 =MAX(0,B14-B16) // Residual cash
B20 =B19*(1-B6) // LP residual
B21 =B19*B6 // GP residual
B22 =B10+B13+B18+B20 // TOTAL LP
B23 =B17+B21 // TOTAL GP
B24 =B3-B22-B23 // Cash reconciliation (must be zero)
The displayed comments explain each line; when entering formulas into Excel, copy only the expression beginning with =, not the comment after it.
For the base case, B22 must equal 148, B23 must equal 12, and B24 must equal 0.
The B15 formula allows a partial GP catch-up rate, provided the GP's catch-up-tier percentage in B7 is greater than the ultimate carry percentage in B6. Otherwise the tier cannot catch up by itself and the agreement needs different mechanics. This model assumes a single distribution, with no historical balances.
A second check verifies the fully caught-up result:
=IF(B3>B2,B23/(B3-B2),0)
At $160 million, the result is 20%. At $120 million, it will deliberately show 16.8% because the GP has not yet finished catching up. Do not flag that as a model error.
Run the $112m and $120m cases by changing only B3. No formula should need editing.
A 25% Carry Changes the Catch-Up, Not Just the Final Split
Return to the $160 million exit. Change only B6 from 20% to 25% and keep the catch-up-tier share at 100%.
Preferred return $16.6400m
Required GP catch-up:
$16.64m × 25% / 75% $5.5467m
Cash after catch-up:
$43.36m - $5.5467m $37.8133m
GP residual at 25% $9.4533m
Total GP $15.0000m
Total LP $145.0000m
The fund made $60 million profit, and the GP receives 25% of that profit: $15 million. We changed no capital or preference assumptions.
Now change a different input: use a 50% GP catch-up tier with the original 20% carry. The old version of this article used an incorrect shortcut for partial catch-up.
The correct target equation is:
(50% × Catch-Up-Tier Cash)
------------------------------------ = 20%
($16.64m + Catch-Up-Tier Cash)
Catch-Up-Tier Cash
= $16.64m × 20% / (50% - 20%)
= $11.0933m
GP receives $5.5467m in that tier.
LP receives $5.5467m in that tier.
After the full 50% catch-up tier, the remaining $32.2667 million splits 80/20. GP receives another $6.4533 million: still $12 million of total carry on the fully successful $160 million exit.
The economic difference appears before full catch-up. At $122 million exit proceeds, a 100% GP catch-up pays the GP $4.40 million; the 50% catch-up tier pays the GP only $2.68 million. Same project and carry percentage. Different negotiated waterfall.
This is why catch-up rate is a real term to negotiate, not a decorative input.
The GP's Own Capital Is Not Carried Interest
Our base case gives the GP zero invested capital so that every dollar paid to it is clearly carry.
Real GPs can also invest alongside LPs. That gives the GP two different economic positions:
- Investor position: return of its own contributed capital and its share of investment profits under the investor-class allocation; and
- Carried interest position: performance participation paid under the carry waterfall.
Do not add the GP's co-investment distributions into the carried-interest numerator.
For an illustrative fund in which LPs contribute $98 million and the GP contributes $2 million on identical investor-class terms, the carry pool can remain $12 million on the $60 million fund profit, assuming the same fully caught-up 20% structure. The remaining $148 million going to the investor class would then be allocated 98/2:
LP investor-class distributions $145.04m
GP investor-class distributions $2.96m
GP carried interest $12.00m
---------
Combined distributions $160.00m
That GP's total cash receipt is $14.96 million, but only $12 million is carry. Its $2.96 million investor distribution includes the return of its own $2 million contributed capital.
A production model should implement the particular LPA's capital, preferred-return and carried-interest definitions rather than assuming all GP co-investment participates identically.
Whole-of-Fund Versus Deal-by-Deal: Why Clawback Exists
The example so far uses a whole-of-fund waterfall, often called a European waterfall: under our assumed LPA, fund-level capital and preference are satisfied before carry.
A deal-by-deal waterfall, often called American, can pay carry on successful earlier exits before every other deal has been realized. Neither nickname tells you the manager's country, and neither label substitutes for reading the LPA's distribution, loss-offset and clawback provisions. ILPA publishes model LPAs illustrating both architectures.
Here is a separate, deliberately simplified clawback demonstration. We switch off the preferred return and fund expenses solely to isolate early carry.
The fund invested $100 million: $50 million in Deal A and $50 million in Deal B.
| Event | Deal A | Deal B | Fund position |
|---|---|---|---|
| Initial investments | ($50m) | ($50m) | ($100m) contributed |
| Deal A exits first | $100m | still held | $50m profit on A |
| GP receives 20% of A profit | $10m | — | Early deal-by-deal carry |
| Deal B later exits at a loss | — | $30m | $20m loss on B |
| Final fund realization | $100m | $30m | $130m proceeds; $30m net profit |
A simplified 20% whole-fund carry on $30 million net profit would be $6 million. But the GP already received $10 million when Deal A exited.
The illustrative overpayment is:
Carry already distributed to GP $10m
Ultimate 20% of net fund profit $6m
-----
Illustrative GP clawback $4m
Actual clawback is not always simply that subtraction. The LPA may adjust for taxes, expenses, prior distributions, unrealized losses, escrow arrangements, guarantees and timing. The $4 million illustrates why a deal-by-deal fund needs an end-of-fund reconciliation.
It also shows why copying one four-tier deal waterfall across every exit does not by itself create a correct American fund model. The model needs fund-wide memory.
The Second Worksheet: Make the Waterfall Remember Yesterday
The one-exit workbook above is the fastest way to understand the four tiers. A real fund often makes capital calls over several years, returns some principal early, sells assets at different dates and distributes income while the rest of the portfolio remains invested.
Add a period-by-period ledger with at least:
| Period | New capital | Opening unreturned capital | Capital returned | Pref accrued | Pref paid | Cum. pref paid | GP catch-up paid | Carry paid | Cash still distributable |
|---|
For each distribution date, start from the LPA's definitions. Some preferences accrue on contributed capital until returned; others depend on a specified IRR calculation. The fee and expense treatment may change the unreturned-capital test. Catch-up often depends on all prior preferred distributions and carry paid. Deal-by-deal funds need an investment-by-investment ledger and fund-level loss/escrow reconciliation.
Here is a mistake worth preventing: if the LP received $8 million preference in Year 1, do not calculate the full two-year preference in Year 2 and pay it again. Carry prior payments and the balance of the preferred-return entitlement forward.
An IRR-based hurdle is best tested on dated LP cash flows under the LPA's rules. In Excel, use XIRR to measure the resulting investor return, but do not rely on circular iteration as your first method for solving the waterfall. Allocate cash tier by tier, or use Goal Seek / an explicit solver for the actual contract-defined IRR threshold.
IRR, MOIC and the J-Curve Must Reconcile to the Same Cash
For our original $160 million example, the LP invested $100 million at Year 0 and received $148 million at Year 2.
LP MOIC = $148m / $100m = 1.48x
LP annualized return, with no interim cash:
= (148 / 100)^(1/2) - 1
≈ 21.66%
With actual dates and more cash-flow events, use:
=XIRR(LP_Net_Cash_Flows, Cash_Flow_Dates)
The GP received $12 million of carry without investing in the simplified example. Do not report a meaningful GP investment IRR or MOIC on a zero invested-capital denominator. If the GP co-invests, calculate an investor-class return on that investment separately and disclose carried-interest receipts distinctly.
The LP J-curve in our stripped-down example is almost comically simple:
| Year | LP cash flow | Cumulative LP net cash flow |
|---|---|---|
| 0 | ($100m) | ($100m) |
| 1 | $0m | ($100m) |
| 2 | $148m | $48m |
A real fund's curve gains its shape from staggered calls, fees and staggered exits. Do not populate a sophisticated-looking J-curve chart with invented yearly distributions that do not tie back to the waterfall. DPI and TVPI likewise need genuine paid-in capital, actual distributions and a supportable remaining NAV.
The Audit That Catches Most Waterfall Errors
Before this model is presented to an LP, lender or investment committee, its checks should explain where every dollar went.
For our four-tier example, make these checks visible:
Cash check
= Total proceeds - LP distributions - GP distributions
= 160 - 148 - 12 = 0
Profit check
= Total proceeds - contributed capital - total profit
= 160 - 100 - 60 = 0
Catch-up check, once fully caught up
= GP carry / (LP profit + GP carry)
= 12 / (48 + 12) = 20%
For the $120 million scenario, the catch-up check should not insist on 20%. Instead, it should confirm that (a) the preferred return was fully paid, (b) the remaining $3.36 million went into the catch-up tier, and (c) the residual tier did not begin prematurely.
The final question is contractual: do these checks still hold after you apply the actual fund's fee offsets, recycleable distributions, escrow, GP commitment and clawback rules?
A clean zero-reconciliation is necessary but not sufficient. A spreadsheet can distribute all $160 million perfectly and still allocate it to the wrong people.
Related reading: LBO model in Excel · IRR vs XIRR vs MIRR · Financial model sensitivity analysis · Project-finance cash waterfall
Primary references: ILPA Model LPAs — whole-of-fund and deal-by-deal · ILPA Principles on fund alignment and carried interest · ILPA Reporting Template (version 2.0)