The Private Equity Waterfall Spreadsheet: Why Tiered Promotes Break Your Return Model

Marcus had $850,000 committed across three growth-equity funds. After four years of distributions and capital calls, his Google Sheet said the portfolio was tracking a 17.4% net IRR. Then his fund administrator sent the audited K-1s and the actual figure was 12.1%. The $43,000 of "missing" return was sitting in a place the spreadsheet had never modeled: the promote tiers.

Two funds had 80/20 splits with a 7% preferred return and a 20% catch-up. The third had a European waterfall that flipped to 70/30 above a 2.0x MOIC. Marcus's IRR formula treated every distribution as if it landed in his pocket at a single carry rate. Real waterfalls do not behave that way. By blending three different tier structures into one average, the spreadsheet quietly overstated his net IRR by more than five percentage points a year.

The fix was not a better spreadsheet. The fix was recognizing that a waterfall is not one formula — it is a sequence of formulas, and each tier changes how every dollar above it gets split.

The 80/20 Promote Is Not 80/20

Most LP-side documentation describes a fund as "80/20," which sounds like a single rule: keep 80 cents on the dollar for LPs, hand 20 cents to the GP. That description is incomplete. A real waterfall usually has three or more tiers:

• Return of capital: 100% to LPs until they have received their contributed capital back.
• Preferred return: 100% to LPs until they have earned an agreed hurdle (often 7% or 8% IRR-equivalent on contributed capital).
• GP catch-up: 100% to the GP until they have "caught up" to a target share of profits — commonly 20%.
• Carried-interest split: the headline 80/20 (or sometimes 70/30 or even 60/40) on every dollar above the catch-up.

Each tier stacks on top of the previous one. A 7% pref with a 20% catch-up means the GP receives nothing on the first chunk, then takes 100% of the next chunk to bring their share to 20% of all profits distributed so far, and only after that does the 80/20 split kick in. The same fund can produce dramatically different LP outcomes depending on whether total profits finish at 1.5x, 2.0x, or 2.5x MOIC.

A Concrete Walkthrough

Suppose an LP commits $1,000,000 to a fund that returns $1,800,000 over its life — a 1.8x gross MOIC. At first glance $800,000 of profit split 80/20 should send $640,000 to the LP and $160,000 to the GP. But a tiered waterfall changes that.

Tier 1 — Return of capital: The first $1,000,000 of distributions returns contributed capital. The LP receives the full $1,000,000. The GP gets $0.

Tier 2 — Preferred return (7% on $1M for 5 years = ~$403,000): The next ~$403,000 of distributions goes 100% to the LP. The GP still gets $0.

Tier 3 — GP catch-up (target = 20% of profits distributed so far): Profits so far total $403,000. To give the GP a 20% share of that profit (~$80,600), the next $100,750 of distributions goes 100% to the GP. The math is awkward: $100,750 to the GP / $503,750 total profit = 20%.

Tier 4 — 80/20 split: Remaining distributions pass through the headline split. After $1,503,750 has been distributed, $296,250 is left. Of that, $237,000 goes to the LP and $59,250 goes to the GP.

Final tally: LP receives $1,640,000 (a net 1.64x on $1M deployed), GP receives $160,000. The 80/20 split is exactly correct at 20% of post-catch-up profit, but the LP multiple is 1.64x — not 1.64x of "gross profit," 1.64x of contributed capital. Without modeling tiers, the spreadsheet might have read 1.64x as "we did 64% of the headline 80/20 deal," which is misleading. The LP gave up the pref-only distributions and the catch-up to get there.

Why Spreadsheets Break

The most common spreadsheet approach is a single =SUM(dividends) / SUM(capital) ratio wrapped around an IRR formula. That ratio pretends every dollar of distribution arrives under the same split. It works for plain vanilla deals without a promote, but it lies about two important facts:

1. Tier timing: A return-of-capital tier means the first dollars back do not constitute profit — they are not split. A pref tier means early profits are LP-only. A catch-up means the GP receives a chunk that is mathematically 100% to them. Combining these into one ratio averages out the differences and inflates LP IRR any time the fund finishes below its catch-up boundary.
2. Cumulative MOIC bands: Funds with European waterfalls re-rate the GP split at certain MOIC thresholds. Common breaks are at 1.5x, 2.0x, and 3.0x. A spreadsheet with a fixed 20% assumption will understate GP carry above the upper bands and overstate it below the lower bands.

Marcus's portfolio was the worst of both worlds. One fund had finished at 2.0x exactly, so a small amount of profit sat right at the catch-up boundary — a region where a one-cent shift in the MOIC moves hundreds of basis points. The spreadsheet rounded it. The waterfall did not.

Where the Promote Eats the Return

The damage is not symmetric. Below the pref, the GP gets nothing and the LP captures the full return. Above the pref, the GP starts taking a slice that scales with how aggressively the fund beats the hurdle. A fund that returns 1.8x gross to the LP might net to 1.64x. A fund that returns 1.2x might net to 1.07x — the GP gets almost nothing because the pref absorbs nearly all of the profit.

The result is a nonlinear curve: each dollar of gross outperformance produces fewer dollars of LP net outperformance once the GP starts collecting carry. A 7% pref / 20% carry structure can take 15 to 25 percent of total profit to the GP at moderate outperformance and 30 percent or more at high outperformance. If the fund does really well (say 3.0x), the GP can collect above 25 percent of all profits despite the "20%" headline, because the catch-up lets them accelerate into the carry band earlier.

A Framework for Honest LP Tracking

The good news is that tiered waterfalls, despite their complexity, can be modeled deterministically. Here is a four-step framework for keeping your LP return honest without rebuilding the entire spreadsheet from scratch each quarter.

1. Pull the LPA section on distributions. Identify the tier order (typically ROC, pref, catch-up, carry split) and any MOIC breakpoints for re-tiering. Note the pref rate as an IRR hurdle, not an annual yield — it compounds.
2. Replay distributions in date order against contributed capital. For each distribution event, ask which tier it falls into given cumulative LP capital returned and cumulative accrued pref at that date.
3. Compute net-to-LP per period. Sum the LP share of every distribution by date. Those dated net amounts become the cashflow series that feeds your LP-side IRR.
4. Reconcile against capital statements. Sponsor capital account statements report the cumulative LP share directly. Compare your modeled LP net to the statement. If they diverge, the tier order or pref accrual math is wrong — never adjust the IRR to match the statement.

The framework requires a dated cashflow series per fund, a pref accrual tracker, and tier logic. That is far more than a single IRR cell, but it is exactly what a sponsor's fund administrator already runs. The right tool hands you the LP-side cashflow series so you do not have to recalculate it every quarter.

EquityMonitoring Treats Waterfalls as Data, Not Math

EquityMonitoring stores every capital call and distribution as a dated entry with a flow type — CALL for contributions, ROC for return of capital, DISTRIBUTION for profit distributions, and FEE for management fees or carry. Because each transaction is dated, the LP-side IRR is computed directly from your net cashflow stream using numpy_financial.irr, exactly the way a sponsor's fund admin would model it. The remaining-capital column tracks your outstanding commitment as ROC entries reduce it, while fees and costs sit separately in their own category so they never pollute the pref calculation.

That separation matters when waterfalls differ across your portfolio. Two funds with the same headline 80/20 split can produce different LP IRRs if one has a 7% pref and the other has an 8% pref, or one uses an American-style waterfall (deal-by-deal) and the other uses European (fund-as-a-whole). Because EquityMonitoring does not bake a single split assumption into the math, you can keep the gross cashflows the GP reports intact and overlay your own LP-side distribution split as a what-if scenario. The forecast page even lets you project future distributions at a flat monthly value against your outstanding capital so you can estimate the IRR impact of returning to pref-only versus sliding into carry.

Where Returns Quietly Disappear

Three patterns show up repeatedly in waterfalls that hurt LP returns without showing up in the headline 80/20 number. First, a fund that flips its split from 80/20 to 70/30 at a 2.0x MOIC — every dollar above the breakpoint hands 30 cents to the GP. Second, a GP catch-up rate above 100% (some funds catch up at 50% of profits until the GP reaches 25% of cumulative profit, which front-loads the catch-up). Third, a hurdle rate that compounds annually but is described in the LPA as a "simple pref" — the same wording, dozens of basis points apart.

If your spreadsheet uses one IRR cell for the whole portfolio, these differences vanish into an average. If your tracker stores dated cashflows per fund, each waterfall's quirks become visible the moment one fund stands out in the analytics view. That visibility is what lets passive LPs negotiate side letters, re-up selectively, or simply stop chasing headline MOIC numbers that underdeliver once catch-up and carry tiers apply.

Self-hosting the same tool on your own Kubernetes cluster — via the same Helm Charts that power the cloud offering — is straightforward if you want your fund cashflows to never leave your own infrastructure. The deployment uses the same containers as the public image, so tier modeling and IRR math stay identical whether you run it hosted or on-prem.

Ready to see how tiered waterfalls are quietly costing you basis points? Sign up at equitymonitoring.com and import your first fund's cashflow history. Your LP net IRR will be the first honest number you have ever tracked.