Equity waterfall with an IRR hurdle in Excel

How the waterfall is set up

The LP puts in 90% of the $10,000,000 equity and the GP 10%. The deal pays $500,000 to $650,000 a year and $15,600,000 in year 5, including the sale. The split:

• Tier 1: 90/10, pro rata to the equity, until the LP has earned an 8% IRR on its capital.
• Tier 2: everything above the hurdle 70/30. The GP's 30% instead of 10% is the promote.

Real deals often have two or three hurdles, for example 8%, 12% and 15% with rising GP splits. Each extra hurdle is one more balance of the same kind.

Step 1: the LP hurdle balance

The balance starts at the LP's contribution, $9,000,000. Each year it grows at the hurdle rate and falls by the LP's tier-1 cash: =C3*(1+$B$10)-D3*$B$11. The yearly cash is less than 8% of the balance, so the balance keeps rising: $9,270,000 at the start of year 2, $9,516,600 in year 3, $9,737,928 in year 4 and $9,931,962 in year 5. Unpaid return is carried forward and compounds, exactly like an IRR.

Step 2: tier 1 cash

Tier 1 pays the deal cash, but no more than what clears the hurdle. Because the LP gets only 90% of each tier-1 dollar, the deal must send the LP's need divided by 90%: =MIN(B7,C7*(1+$B$10)/$B$11). In years 1 to 4 all cash is tier 1. In year 5 the LP needs $9,931,962 × 1.08 = $10,726,519, so tier 1 takes $11,918,355 of the $15,600,000 and the LP balance is exactly zero afterwards. At that point the LP has earned exactly 8% IRR.

Step 3: the cash above the hurdle and the promote

The remaining $3,681,645 is split 70/30. The LP's cash in each year is tier 1 × 90% + tier 2 × 70%, which gives $13,303,671 in year 5. The GP's promote is the extra 20 points it receives above its pro rata 10% on the cash above the hurdle: $3,681,645 × 20% = $736,329.

Worked example: 8% LP IRR hurdle, 70/30 above it

A fictional apartment joint venture. Column B is the deal's equity cash flow, rows 10 to 12 are the deal terms and rows 13 to 19 the results.

Worked example: 8% LP IRR hurdle, 70/30 above it

	A	B	C	D	E	F
1	Year	Deal cash flow	LP hurdle balance, start	Tier 1 cash	Cash above hurdle	LP cash flow
2	0	-10,000,000				-9,000,000
3	1	500,000	9,000,000	500,000	0	450,000
4	2	550,000	9,270,000	550,000	0	495,000
5	3	600,000	9,516,600	600,000	0	540,000
6	4	650,000	9,737,928	650,000	0	585,000
7	5	15,600,000	9,931,962	11,918,355	3,681,645	13,303,671
8	Total	7,900,000		14,218,355	3,681,645	6,373,671
9	Terms and results	Value
10	LP IRR hurdle (tier 1)	8.0%
11	LP share, tier 1 (pro rata)	90%
12	LP share above the hurdle	70%
13	Deal IRR	13.38%
14	LP IRR after promote	12.28%
15	Equity multiple, deal	1.79x
16	GP total cash received	2,526,329
17	GP promote (above its pro rata share)	736,329
18	GP share of total profit	19.3%
19	LP hurdle balance after the sale (check)	0

Educational only, not investment, legal or tax advice. Real waterfalls follow the operating agreement, which may define hurdles, catch-ups and timing differently.

Step 4: check the result

• LP IRR: =IRR(F2:F7) = 12.28%. It must land between the 8% hurdle and the 13.38% deal IRR, because the promote comes out of the return above the hurdle.
• GP share of the profit: the GP receives $2,526,329 for its $1,000,000, a profit of $1,526,329 out of a total deal profit of $7,900,000, or 19.3%. Sponsors and LPs often quote this effective share.
• Balance check: the LP hurdle balance after the sale is 0. If it is negative, tier 1 paid too much.

The deal also returns a 1.79x equity multiple. Many LPs ask for an equity multiple hurdle on top of the IRR hurdle, because a quick sale can clear an IRR hurdle with little profit in dollars. For the basics of the IRR itself, see how to calculate IRR in Excel, and for the property income behind the cash flows, cap rate calculation.

American vs European waterfalls and other terms

This is a single-deal waterfall. In a fund with several properties, an American (deal-by-deal) waterfall pays the promote on each deal as it is sold, while a European (whole-fund) waterfall pays it only after the LPs have received their capital and preferred return across the whole fund, which is safer for LPs. Other terms change the math: a catch-up lets the GP receive most of the cash after the hurdle until it reaches its target share, the hurdle may be tested on LP or on total equity, and the pref may be simple or compounding. Read the operating agreement and model what it says.

Common mistakes

• Forgetting to gross up tier 1. The LP gets only 90% of each tier-1 dollar. Divide the LP's need by its share, or the hurdle is never quite reached.
• Using a simple pref when the agreement says IRR. An IRR hurdle compounds unpaid return. A simple 8% a year on capital gives a different, usually lower, tier 1.
• Testing the hurdle on deal cash instead of LP cash. Check which the agreement uses. The hurdle balance here is tested on the LP.
• Calling all GP cash the promote. The GP's pro rata share is a return on its own capital. Only the cash above that share is promote.
• Not checking the LP IRR. It must sit between the hurdle and the deal IRR. A result outside that range points to a formula error.
• Hard-coding the splits in formulas. Keep hurdle rates and splits in input cells so you can test other terms in seconds.