PE Waterfall Model in Excel: Build GP/LP Distributions (2026)
A managing director once told me that more PE deals fall apart over waterfall disputes than valuation disagreements. That tracks — private equity waterfall models are deceptively complex, and one misplaced formula can shift millions between GPs and LPs. Yet most analysts build them from scratch every time, reinventing the wheel with each new fund structure.
This guide walks you through building a complete PE distribution waterfall in Excel, from capital contributions through carried interest. You'll get real formulas, a clear tier-by-tier framework, and the logic behind American vs. European structures. The distribution waterfall is the final accounting step once your LBO model has projected exit proceeds — it determines exactly who gets what and when. Whether you're modeling your first fund or auditing an existing waterfall, this is the reference you'll keep coming back to.
What Is a Private Equity Distribution Waterfall?
A private equity distribution waterfall is the contractual framework — defined in the Limited Partnership Agreement (LPA) — that dictates how fund proceeds flow to investors. It establishes the priority, timing, and magnitude of every dollar distributed from the fund to its Limited Partners (LPs) and General Partner (GP).
The term "waterfall" comes from the visual metaphor: cash flows down through sequential tiers, filling each bucket before spilling into the next. Each tier has specific conditions that must be met before distributions cascade further.
Why Does the Waterfall Structure Matter?
The waterfall directly determines who gets paid, when, and how much. It aligns incentives between GPs (who manage the fund) and LPs (who provide capital). A well-structured waterfall ensures:
- LPs recover their capital before the GP earns performance fees
- The GP is incentivized to generate returns above a minimum threshold (the hurdle rate)
- Both parties share in the upside once performance benchmarks are met
💡 Pro Tip: Always read the actual LPA before building your model. Waterfall terms vary significantly between funds — cookie-cutter templates can produce materially wrong distributions.
The Four Tiers of a Standard PE Waterfall
Most private equity waterfalls follow a four-tier structure. Here's how each tier works before we build the formulas.
graph TD
A[Total Distributions Available] --> B[Tier 1: Return of Capital]
B --> C[Tier 2: Preferred Return]
C --> D[Tier 3: GP Catch-Up]
D --> E[Tier 4: Carried Interest Split]
B -->|100% to LPs| B1[LP receives contributed capital back]
C -->|100% to LPs| C1[LP receives preferred return - typically 8%]
D -->|100% to GP| D1[GP catches up to 20% of total profits]
E -->|80/20 Split| E1[Remaining profits split LP/GP]
| Tier | Name | Recipient | Typical Terms | Purpose |
|---|---|---|---|---|
| 1 | Return of Capital | 100% LP | Full capital returned | Capital protection |
| 2 | Preferred Return | 100% LP | 8% IRR hurdle | Minimum return guarantee |
| 3 | GP Catch-Up | 100% GP | Until GP has 20% of total profits | Aligns GP compensation |
| 4 | Carried Interest | 80% LP / 20% GP | Pro-rata split | Shared upside |
ℹ️ Note: The 8% preferred return and 20% carry are industry conventions (often called "2 and 20"), but these percentages are negotiable and vary by fund.
How Do You Set Up the Waterfall Model in Excel?
The foundation of any waterfall model is a clean layout with clearly separated inputs, calculations, and outputs. Start with a simple fund structure before adding complexity.
Step 1: Define Your Assumptions
Create an Assumptions section at the top of your worksheet:
Cell B2: Fund Size = 100,000,000
Cell B3: GP Commitment (%) = 2%
Cell B4: GP Commitment ($) = =B2*B3 → 2,000,000
Cell B5: LP Commitment ($) = =B2-B4 → 98,000,000
Cell B6: Preferred Return = 8%
Cell B7: Carried Interest (%) = 20%
Cell B8: GP Catch-Up (%) = 100%
Cell B9: Investment Period (Yrs) = 5
Cell B10: Fund Life (Yrs) = 10
Step 2: Build the Cash Flow Timeline
Set up a timeline across columns. Each column represents a year (or quarter, depending on your model's granularity):
Row 14: Year → 0, 1, 2, 3, ... 10
Row 15: Capital Calls → Negative values (cash out from LPs/GP)
Row 16: Distributions → Positive values (cash returned)
Row 17: Net Cash Flow → =Row16+Row15 (calls are negative)
Row 18: Cumulative Cash Flow → Running sum of Row 17
⚠️ Warning: Use a sign convention and stick to it. The most common approach: capital calls are negative (cash leaving investors), distributions are positive (cash returning to investors). Mixing conventions is the #1 source of waterfall errors.
Step 3: Allocate Capital Calls Between GP and LP
For each period's capital call, split it according to the commitment percentages:
Row 20: LP Capital Called = =Row15 * (LP_Commitment / Fund_Size)
Row 21: GP Capital Called = =Row15 * (GP_Commitment / Fund_Size)
Row 22: Cumulative LP Called = Running sum of Row 20
Row 23: Cumulative GP Called = Running sum of Row 21
How Do You Calculate Each Waterfall Tier in Excel?
This is where the model gets interesting. Each tier uses MIN and MAX functions to ensure distributions don't exceed available cash at each level.
Tier 1: Return of Capital
The first tier returns 100% of invested capital to LPs and the GP in proportion to their contributions. No profit is distributed yet.
=MIN(Distributions_Remaining, Cumulative_Capital_Called - Cumulative_Capital_Returned)
In Excel, assuming distributions remaining is in cell F30 and cumulative capital data is tracked:
LP Return of Capital:
=MIN(F30, MAX(0, Cumulative_LP_Called - Cumulative_LP_ROC_Prior))
GP Return of Capital:
=MIN(F30 - LP_ROC, MAX(0, Cumulative_GP_Called - Cumulative_GP_ROC_Prior))
The key formula pattern for every tier is:
Amount_This_Tier = MIN(Cash_Available, MAX(0, Tier_Entitlement - Already_Received))
This pattern prevents over-distribution and handles partial-year distributions correctly.
Tier 2: Preferred Return (Hurdle Rate)
After capital is returned, LPs receive a preferred return — typically 8% per annum on their invested capital. This is calculated on a cumulative, compounding basis.
LP Preferred Return Owed (cumulative):
=LP_Capital_Contributed * (1 + Pref_Rate)^Years - LP_Capital_Contributed
Preferred Return This Period:
=MIN(Remaining_After_ROC, MAX(0, Cumulative_Pref_Owed - Cumulative_Pref_Paid))
💡 Pro Tip: Use XIRR or XNPV instead of simple compound interest when the LPA specifies an IRR-based hurdle rather than a simple interest hurdle. IRR-based hurdles account for the timing of capital calls and distributions, which matters significantly for funds with irregular cash flows.
For a simplified model with annual periods and a single drawdown at Year 0:
Cell F35 (Pref Owed Cumulative):
=LP_Commitment * ((1 + Pref_Rate)^Year - 1)
Cell F36 (Pref This Period):
=MIN(F33, MAX(0, F35 - E38))
Cell F38 (Cumulative Pref Paid):
=E38 + F36
Where F33 is the cash remaining after Tier 1 distributions.
Tier 3: GP Catch-Up
The catch-up provision is the most commonly misunderstood tier. Its purpose: after LPs receive their preferred return, the GP "catches up" so that the GP's share of total profits (not just remaining profits) equals the carried interest percentage.
Here's the math. If carry is 20% and the catch-up rate is 100%:
Total Profit = Preferred Return Paid + Catch-Up Amount + Tier 4 Profits
GP Target = 20% of Total Profit
For a 100% catch-up, the GP receives all distributions in this tier until they've received an amount equal to:
Catch-Up Amount = Preferred_Return_Paid × (Carry% / (1 - Carry%))
In Excel:
Cell F40 (Catch-Up Entitlement):
=Cumulative_Pref_Paid * (Carry_Pct / (1 - Carry_Pct))
Cell F41 (Catch-Up This Period):
=MIN(F39, MAX(0, F40 - E42))
Cell F42 (Cumulative Catch-Up Paid):
=E42 + F41
Where F39 is cash remaining after Tier 2.
Example: If LPs received $8M in preferred return and carry is 20%, the GP catch-up amount = $8M × (0.20 / 0.80) = $2M. After the catch-up, total profits of $10M are split $8M LP / $2M GP — exactly the 80/20 target.
Tier 4: Carried Interest (Residual Split)
Everything remaining after the catch-up is split according to the carry arrangement — typically 80% LP / 20% GP:
Cell F44 (LP Share Tier 4):
=F43 * (1 - Carry_Pct)
Cell F45 (GP Share Tier 4):
=F43 * Carry_Pct
Where F43 is the cash remaining after Tier 3.
Putting It All Together: Total Distributions
Sum each party's distributions across all tiers:
Total LP Distribution = LP_ROC + LP_Pref + LP_Tier4
Total GP Distribution = GP_ROC + GP_CatchUp + GP_Tier4
Add a check cell to verify the total matches:
=Total_LP_Distribution + Total_GP_Distribution - Total_Distributions
This should equal zero. If it doesn't, you have a leak in your waterfall.
American vs. European Waterfall: What's the Difference?
This is one of the most common questions in PE fund modeling — and getting it wrong changes distributions dramatically.
| Feature | American (Deal-by-Deal) | European (Whole Fund) |
|---|---|---|
| Calculation Basis | Each investment individually | Entire fund portfolio |
| When GP Gets Carry | After each deal exits | After all capital is returned |
| LP Protection | Lower — GP earns carry on winners even if fund loses money | Higher — ensures total fund profitability first |
| Clawback Risk | Higher — GP may owe money back | Lower — carry only on net profits |
| Complexity | More complex (track per deal) | Simpler (fund-level calculation) |
| Common In | US-based funds, venture capital | European funds, buyout funds |
European Waterfall in Excel
The model we built above is essentially a European waterfall — it operates at the fund level. All capital must be returned across the entire fund before the preferred return kicks in.
American Waterfall in Excel
An American waterfall requires a separate waterfall calculation for each investment. The structure adds a layer of complexity:
For each deal (columns):
1. Track capital invested in this deal
2. Track distributions from this deal
3. Run the 4-tier waterfall for this deal independently
4. Sum GP carry across all deals
Then add a clawback mechanism:
If SUM(GP_Carry_All_Deals) > 20% * MAX(0, Total_Fund_Profit):
GP_Clawback = Excess amount owed back to LPs
⚠️ Warning: American waterfalls require a clawback provision to protect LPs. Without it, a GP could earn carry on early winners while the overall fund loses money. Always model the clawback — most LPAs require it.
How Do You Handle Multiple Hurdle Rates?
Some funds use tiered hurdle rates, where the carry percentage increases as returns exceed higher thresholds. This is sometimes called a "ratchet" or "tiered carry" structure.
graph LR
A["0-8% IRR<br/>0% Carry"] --> B["8-12% IRR<br/>20% Carry"]
B --> C["12-15% IRR<br/>25% Carry"]
C --> D[">15% IRR<br/>30% Carry"]
To model this in Excel, extend the waterfall with additional tiers:
Tier 2A: Preferred Return to 8% (100% LP)
Tier 3A: GP Catch-Up to 20% of profits in Tier 2A
Tier 4A: 80/20 split on returns between 8% and 12%
Tier 3B: GP Catch-Up to 25% on incremental profits
Tier 4B: 75/25 split on returns between 12% and 15%
Tier 3C: GP Catch-Up to 30% on incremental profits
Tier 4C: 70/30 split on returns above 15%
Each tier uses the same MIN/MAX pattern, just with different thresholds:
Tier_Amount = MIN(Cash_Remaining, MAX(0, Upper_Bound - Lower_Bound - Prior_Distributions_In_Tier))
Common Mistakes in PE Waterfall Models
After auditing dozens of waterfall models, these are the errors I see most frequently:
1. Ignoring the Time Value of Money
The preferred return should compound over the actual investment period. Using simple interest when the LPA specifies compound interest (or IRR) will understate LP entitlements.
WRONG: =Capital * Pref_Rate * Years
RIGHT: =Capital * ((1 + Pref_Rate)^Years - 1)
2. Forgetting the GP's Capital Contribution
GPs typically co-invest 1-5% of the fund. This capital participates in Tier 1 (return of capital) and often in the preferred return. The GP wears two hats: investor (for their committed capital) and manager (for carried interest).
3. Miscalculating the Catch-Up
The catch-up formula Pref × (Carry% / (1 - Carry%)) assumes a 100% catch-up rate. If the LPA specifies a partial catch-up (e.g., 80%), the formula changes:
Partial Catch-Up = Pref_Paid × Catch_Up_Rate × (Carry% / (1 - Carry%))
4. Not Including a Verification Check
Always add a reconciliation row:
=Total_Distributions - LP_Total - GP_Total
This must equal zero for every period. A non-zero value means cash is appearing or disappearing in your model.
5. Circular References with IRR-Based Hurdles
If the preferred return is defined as an IRR hurdle, you may encounter circular references because the distribution amounts affect the IRR, which affects the distribution amounts. Enable iterative calculations in Excel:
File → Options → Formulas → Enable iterative calculation
Set maximum iterations to 100 and maximum change to 0.001.
⚠️ Warning: Circular references in waterfall models can cause Excel to crash or produce incorrect results if iterative calculation is not configured properly. Save your work before enabling this feature.
Building Return Metrics: IRR, MOIC, and DPI
No waterfall model is complete without return metrics for both the GP and LP — and the same XIRR mechanics that underpin DCF model valuations apply here to calculate IRR-based hurdle compliance:
LP IRR:
=XIRR(LP_Cash_Flows, Dates)
LP MOIC (Multiple on Invested Capital):
=SUM(LP_Distributions) / SUM(LP_Capital_Calls)
DPI (Distributions to Paid-In):
=Cumulative_Distributions / Cumulative_Capital_Called
TVPI (Total Value to Paid-In):
=(Cumulative_Distributions + Remaining_NAV) / Cumulative_Capital_Called
💡 Pro Tip: Always calculate IRR and MOIC separately for LPs and the GP. The GP's return includes both their co-investment returns AND carried interest, which creates a much higher effective return than LPs receive. Presenting only blended fund returns obscures this difference. When the exit is a strategic acquisition rather than an IPO, the proceeds entering the waterfall depend on what the acquirer was willing to pay — a figure driven by the merger model's accretion/dilution analysis that determines whether the deal is EPS-accretive for the buyer at the offered price.
Sensitivity Analysis: Stress-Testing Your Waterfall
Use Excel's Data Table feature — the same technique covered in our sensitivity analysis guide for financial models — to see how distributions shift under different scenarios. Realistic exit multiple ranges are typically anchored by comparable company analysis that surveys what similar businesses are currently trading at in public markets:
| Exit Multiple | LP IRR | GP IRR | LP MOIC | GP MOIC | GP Carry ($M) |
|---|---|---|---|---|---|
| 1.0x | 0.0% | 0.0% | 1.00x | 1.00x | $0.0 |
| 1.5x | 8.4% | 8.4% | 1.50x | 1.50x | $0.0 |
| 2.0x | 14.9% | 22.1% | 1.90x | 2.50x | $4.0 |
| 2.5x | 20.1% | 35.8% | 2.30x | 3.50x | $8.0 |
| 3.0x | 24.6% | 48.7% | 2.70x | 4.50x | $12.0 |
Notice how the GP's return accelerates faster than the LP's once the hurdle is cleared — that's the carry's leverage effect.
To build this table:
- Set up a column with exit multiples (1.0x to 3.0x)
- Link your model's exit assumption to a single input cell
- Use Data → What-If Analysis → Data Table with the exit multiple cell as the column input
- Output columns reference your LP IRR, GP IRR, and carry amount cells
Fund managers taking a quantitative approach to capital allocation — deciding how much to commit across deal types, vintages, or strategies — can extend this returns analysis using portfolio optimization with Excel's Solver to find the efficient frontier across expected IRRs and correlation assumptions.
Frequently Asked Questions
What is the standard preferred return in private equity?
The industry standard preferred return (hurdle rate) is 8% per annum, though it varies by fund. Some venture capital funds use 6%, while certain infrastructure or real estate funds may use 10%. The rate is negotiated between GPs and LPs during fundraising and codified in the LPA.
How does the GP catch-up work in a PE waterfall?
After LPs receive their preferred return, 100% of subsequent distributions go to the GP until the GP's cumulative share equals their carried interest percentage (typically 20%) of total profits. For example, if LPs earned $8M in preferred return, the GP catches up with $2M so total profits split 80/20.
What is the difference between a hard hurdle and a soft hurdle?
A hard hurdle means the GP only earns carry on returns above the hurdle rate. A soft hurdle means once the hurdle is cleared, the GP earns carry on all profits from dollar one (via the catch-up mechanism). Most PE funds use soft hurdles with a catch-up provision.
Can I use XIRR for the preferred return calculation?
Yes, and you often should. When capital is called at irregular intervals, a simple compound interest calculation won't match the IRR-based hurdle defined in most LPAs. Use XIRR to calculate the actual IRR of LP cash flows, then compare it against the hurdle rate to determine if the preferred return has been met. For a complete breakdown of when to use IRR, XIRR, or MIRR — and the common errors that inflate fund returns by hundreds of basis points — see our IRR vs XIRR vs MIRR guide for Excel.
How do I model a clawback provision in Excel?
Add a final reconciliation step after all distributions. Calculate the GP's actual carry percentage of total fund profits. If it exceeds the contractual carry rate, the difference is the clawback amount. Formula: =MAX(0, Total_GP_Carry - Carry_Pct × Total_Fund_Profit). Track this as a contingent liability until the fund is fully liquidated.
Wrapping Up
Building a PE waterfall model from scratch teaches you more about fund economics than any textbook. The MIN/MAX formula pattern, the catch-up math, and the American vs. European distinction are concepts that transfer directly to real deal work. For sponsor-backed companies with preferred stock, option pools, and management rollover equity, the same MIN/MAX logic governs the company-level liquidation waterfall — a cap table model in Excel handles those preferred-versus-common allocations before proceeds reach the LP/GP split.
If you find yourself rebuilding these formulas repeatedly, tools like VeloraAI can generate waterfall tier formulas from natural language descriptions — just describe the distribution logic, and the AI produces the Excel formulas. It's particularly useful for modeling non-standard structures where the LPA deviates from the typical four-tier framework.
The best waterfall model is one that matches the LPA exactly, reconciles to zero every period, and makes the fund's economics transparent to every stakeholder. Build it right, and it becomes the single source of truth for your fund.