Building a waterfall model using AI means creating a tiered profit distribution structure that calculates how cash flows are split between General Partners (GPs) and Limited Partners (LPs) based on specific IRR hurdles. AI accelerates the process by generating formula logic for return tiers, preferred returns, and promote calculations—but only if you define the structure correctly upfront.
Relevant Articles
- Need to build the cash flow first? See our [Pro Forma Guide].
- Need specific LP/GP splits? See our [LP/GP Guide].
Working Example: Project "Canyon Ridge"
To see this in action, let's model a specific deal:
This is a standard value-add deal where the GP receives an increasing share of profits (the "promote") as higher IRR hurdles are met. All subsequent formulas reference these exact numbers.
Waterfall Structure Basics
A waterfall model distributes cash flows in a sequential, tiered structure. Think of it as a series of buckets that must be filled in order before any cash spills into the next tier. The first bucket is always the return of original equity to LPs. The second bucket is typically the preferred return. After that, cash flows into promote tiers where the GP's share increases.
The three-tier structure in Project Canyon Ridge works like this: Tier 1 returns all equity to LPs and pays the 8% preferred return on a 90/10 split. Tier 2 distributes remaining cash until the LPs achieve a 15% IRR, typically on an 80/20 or 70/30 split. Tier 3 distributes everything above a 15% IRR at a 60/40 or 50/50 split, depending on negotiation.
Most institutional models follow this pattern, but the exact split ratios vary by deal. You must define them explicitly before prompting AI. Generic instructions like "build a waterfall" produce generic output. Specific constraints produce accurate formulas.
When building this using AI, your first task is to define the tier boundaries. In Canyon Ridge, the tier boundaries are defined by IRR thresholds: 8%, 15%, and 20%. These are not arbitrary—they reflect the risk-adjusted return expectations of the LP and the performance incentive for the GP. If the GP only achieves an 8% return, they receive minimal promote. If they achieve 20%, they capture a larger share of upside.
The waterfall does not calculate operating cash flow. It assumes you already have a distributable cash flow amount—in this case, $12,000,000 from the sale proceeds. If you don't have that number yet, stop. Build your pro forma first. This model only handles distribution logic.
Decomposition (Splitting Hurdles & Tiers)
This is where most waterfall models break. Analysts try to calculate the entire distribution in one massive nested IF statement, and AI generates 400-character formulas that no one can audit. The solution is Decomposition—splitting the waterfall into separate calculation blocks for each tier.
Start by isolating the Return of Capital block. This is the simplest calculation: return the original equity contributions to each investor before any profit is distributed. In Canyon Ridge, the LP gets back $7,200,000 and the GP gets back $800,000. AI handles this easily if you prompt: "Create a block that returns original equity to LP and GP. LP equity = $7,200,000, GP equity = $800,000."
Next, decompose the preferred return calculation. The preferred return is 8% annually, compounded over 5 years. Instead of asking AI to "calculate the pref," give it the exact formula structure: "Calculate the compounded preferred return using LP equity * ((1 + 8%)^5 - 1)." For Canyon Ridge, this equals $7,200,000 * 0.4693 = $3,379,008. This amount must be paid to the LP before any promote is distributed to the GP.
After Return of Capital and Preferred Return are paid, the remaining cash flows into the promote tiers. But here's the mistake: most analysts calculate "remaining cash" once and apply it to all tiers. Wrong. Each tier consumes cash, and the remaining amount must be recalculated after each tier. This is why you must build separate blocks.
For Tier 1, the total distribution is Return of Capital ($8,000,000) + Preferred Return ($3,379,008). The LP receives $7,200,000 + $3,041,107 = $10,241,107 at a 90/10 split of the pref. The GP receives $800,000 + $337,901 = $1,137,901. Remaining cash: $12,000,000 - $11,379,008 = $620,992.
For Tier 2, calculate how much additional cash the LP needs to reach a 15% IRR. The LP's total return at 15% IRR over 5 years is $7,200,000 * ((1 + 15%)^5) = $14,478,643. The LP has already received $10,241,107 in Tier 1. The shortfall is $14,478,643 - $10,241,107 = $4,237,536. But only $620,992 remains. This entire amount goes into Tier 2 at an 80/20 split: LP gets $496,794, GP gets $124,198.
There is no Tier 3 distribution in this scenario because the cash is exhausted. The LP's final IRR is below 15%, so the GP earns only a modest promote. This is the power of Decomposition—each tier is calculated independently, making it easy to trace where every dollar goes.
When prompting AI, structure your request around these isolated blocks. Do not ask for "the full waterfall formula." Ask for "Tier 1 formula: Return of Capital + Preferred Return." Then ask for "Tier 2 formula: Remaining cash after Tier 1, split 80/20 until LP reaches 15% IRR." This approach produces clean, auditable formulas that can be verified.
For more on this technique, see our [Decomposition framework guide], which breaks down complex financial models into discrete calculation units.
Specification (Defining Promote Logic)
AI cannot guess your promote structure. If you prompt "build a waterfall with promote," you'll get a generic 80/20 split with no hurdles. The solution is Specification—defining the exact logic, splits, and thresholds before you generate a single formula.
Start with the promote split ratios for each tier. In Canyon Ridge, the promote splits are 90/10 in Tier 1, 80/20 in Tier 2, and 70/30 in Tier 3. These are not standard—they are negotiated deal terms. You must specify them explicitly. Your prompt should state: "Tier 1: 90% LP, 10% GP. Tier 2: 80% LP, 20% GP. Tier 3: 70% LP, 30% GP."
Next, specify the hurdle logic. A hurdle is the IRR threshold that must be met before moving to the next tier. In Canyon Ridge, the first hurdle is 8% (the preferred return), the second is 15%, and the third is 20%. The hurdle is always calculated on the LP's equity, not total equity. This distinction matters. Prompt: "Hurdles are based on LP equity of $7,200,000. Hurdle 1 = 8% IRR. Hurdle 2 = 15% IRR. Hurdle 3 = 20% IRR."
You must also specify whether the preferred return is cumulative or simple. In most institutional models, the preferred return compounds annually. If the LP does not receive their 8% in Year 1, the shortfall carries forward and compounds. This is called a "cumulative" or "accruing" preferred return. Prompt: "Preferred return is 8% annually, compounded. If not paid in a given year, the shortfall accrues and compounds."
Another common specification issue: lookback provisions. Some waterfalls include a "lookback" or "catch-up" clause, where the GP retroactively captures promote on earlier distributions once a higher hurdle is achieved. Canyon Ridge does not have a lookback, but if yours does, specify it: "After the LP achieves a 15% IRR, the GP receives a one-time catch-up payment equal to 20% of all prior distributions."
Finally, specify the distribution sequence. Most waterfalls follow this order: (1) Return of Capital, (2) Preferred Return, (3) Hurdle 1 Promote, (4) Hurdle 2 Promote, (5) Hurdle 3 Promote. But some deals vary the sequence. For example, some structures pay the GP's return of capital last, not first. Others distribute the preferred return before the return of capital. Specify the exact sequence in your prompt.
When you provide this level of detail, AI generates accurate formulas. Without it, AI guesses—and those guesses rarely match your deal terms. For a deeper dive into this concept, review our [Specification framework guide], which teaches how to translate deal terms into machine-executable constraints.
Preferred Return and Hurdles
The preferred return is the minimum annual return the LP expects before the GP earns any promote. In Canyon Ridge, the preferred return is 8% annually, compounded over 5 years. This means the LP must receive their original $7,200,000 plus $3,379,008 in profit before the GP earns more than their pro-rata 10% share.
The calculation is straightforward: LP Equity × ((1 + Pref Rate)^Years - 1). For Canyon Ridge: $7,200,000 × ((1.08)^5 - 1) = $7,200,000 × 0.4693 = $3,379,008. This is the minimum profit the LP expects. If the deal returns less than this, the GP receives only their 10% pro-rata share—no promote.
A common error is calculating the preferred return on total equity instead of LP equity. Wrong. The preferred return is a LP-only benefit. The GP does not receive a preferred return on their $800,000 contribution. The GP earns their return through the promote, not the pref.
Hurdles are IRR thresholds that unlock higher promote splits. In Canyon Ridge, the first hurdle is 8% (the pref), the second is 15%, and the third is 20%. Once the LP achieves each hurdle, the GP's share increases. The hurdle calculation is: LP Equity × ((1 + Hurdle Rate)^Years). For the 15% hurdle: $7,200,000 × ((1.15)^5) = $14,478,643. This is the total return the LP needs to achieve a 15% IRR.
The difference between the preferred return and the hurdle is critical. The preferred return is a minimum profit paid before any promote. The hurdle is a total return threshold that unlocks a higher promote split. In Tier 1, the LP receives their pref at a 90/10 split. In Tier 2, additional cash is distributed at an 80/20 split until the LP hits the 15% hurdle. If there's still cash remaining, Tier 3 distributes it at a 70/30 split.
In Canyon Ridge, the total distributable cash is $12,000,000. After paying $11,379,008 in Tier 1 (Return of Capital + Pref), only $620,992 remains. This is not enough to bring the LP to a 15% IRR, so all of it goes into Tier 2 at an 80/20 split. The LP receives $496,794, the GP receives $124,198. Total GP promote: $337,901 + $124,198 = $462,099.
If the deal had generated $18,000,000 in sale proceeds instead of $44,000,000, the distributable cash would be $18,000,000 (assuming zero debt). After Tier 1, $6,620,992 would remain. The LP needs $4,237,536 to hit the 15% hurdle. That entire amount goes into Tier 2 at 80/20: LP gets $3,390,029, GP gets $847,507. Remaining cash: $2,383,456. This goes into Tier 3 at 70/30: LP gets $1,668,419, GP gets $715,037. Total GP promote: $1,900,445.
This example shows why you cannot calculate the waterfall until you know the total distributable cash. The promote depends entirely on how much cash remains after each tier. AI can build the logic, but you must define the tier boundaries and split ratios first.
LP vs GP Return Calculations
The LP's return and the GP's return are calculated differently because they have different capital contributions and different rights to cash flow. The LP contributes 90% of the equity and receives the preferred return. The GP contributes 10% and earns a promote based on performance.
In Canyon Ridge, the LP contributes $7,200,000 and receives a total of $10,738,901 ($10,241,107 from Tier 1 + $496,794 from Tier 2). The LP's total return is $10,738,901 - $7,200,000 = $3,538,901. The LP's IRR is calculated as: IRR = ((Total Return / Equity)^(1/Years)) - 1 = (($10,738,901 / $7,200,000)^(1/5)) - 1 = 8.33%.
The GP contributes $800,000 and receives a total of $1,262,099 ($1,137,901 from Tier 1 + $124,198 from Tier 2). The GP's total return is $1,262,099 - $800,000 = $462,099. The GP's IRR is: (($1,262,099 / $800,000)^(1/5)) - 1 = 9.56%.
Notice that the GP's IRR is higher than the LP's IRR, even though the LP has the preferred return. This is because the GP earns a promote—additional cash beyond their pro-rata share. In this case, the GP's pro-rata share would be 10% of the total profit. Total profit = $12,000,000 - $8,000,000 = $4,000,000. The GP's pro-rata share is $400,000. But the GP actually receives $462,099, which is $62,099 more than their pro-rata share. This excess is the promote.
The promote calculation is: GP Total Distribution - (GP Equity + GP Pro-Rata Profit). For Canyon Ridge: $1,262,099 - ($800,000 + $400,000) = $62,099. This is the GP's incentive fee for outperforming the 8% preferred return.
If the LP achieves a higher IRR, the GP's promote increases. Let's revisit the scenario where sale proceeds are $50,000,000, generating $18,000,000 in distributable cash. The LP receives $14,299,555 total, achieving a 14.71% IRR. The GP receives $3,700,445, achieving a 36.0% IRR. The GP's promote is now $1,900,445, significantly higher than the base scenario.
This asymmetry is the purpose of the waterfall: align the GP's compensation with LP performance. The better the deal performs, the more the GP earns. But the GP only earns a promote if the LP's return exceeds the preferred return threshold.
When building this in AI, you must specify these calculations separately. Do not ask AI to "calculate returns." Ask for "LP IRR based on $7,200,000 equity and total distributions of $10,738,901 over 5 years." Then ask for "GP IRR based on $800,000 equity and total distributions of $1,262,099 over 5 years." Then ask for "GP promote = GP total distribution - (GP equity + (Total profit * GP pro-rata share))." This ensures each calculation is isolated and traceable.
A common mistake: analysts calculate the GP's IRR on the GP's total distribution, which includes both equity return and promote. This inflates the GP's IRR and creates confusion. The correct approach is to separate the GP's equity return from the GP's promote. The equity return is calculated like the LP's return. The promote is the excess above the pro-rata share.
Testing Waterfall Accuracy
Once AI generates your waterfall formulas, you must verify them. A waterfall model has three failure modes: (1) the total distributed cash does not equal the total available cash, (2) the LP's IRR does not match the intended hurdle at tier boundaries, and (3) the GP's promote is miscalculated.
The first test is the Zero Test. Sum all LP distributions and all GP distributions. The total must equal the total distributable cash. For Canyon Ridge: LP total = $10,738,901, GP total = $1,262,099. Sum = $12,001,000. Wait—this is $1,000 more than the $12,000,000 available. This indicates a rounding error in one of the tier calculations. Go back and check each tier formula. Most errors occur in the IRR hurdle calculations where compounding introduces fractional cents.
The second test is the Hurdle Verification Test. At each tier boundary, calculate the LP's IRR manually and confirm it matches the intended hurdle. In Canyon Ridge, after Tier 1, the LP has received $10,241,107 on a $7,200,000 investment over 5 years. The IRR is: (($10,241,107 / $7,200,000)^(1/5)) - 1 = 7.30%. This is below the 8% hurdle, which is correct because the LP has only received the return of capital and the pref, not the full pref compounded across all years. Recalculate: LP equity + compounded pref = $7,200,000 + $3,379,008 = $10,579,008. The LP received $10,241,107, which is less. The shortfall is due to the GP receiving 10% of the pref. This is correct.
The third test is the Promote Verification Test. Calculate the GP's promote manually: GP total distribution - (GP equity + GP pro-rata profit). For Canyon Ridge: $1,262,099 - ($800,000 + ($4,000,000 * 10%)) = $1,262,099 - $1,200,000 = $62,099. This matches our earlier calculation. If it doesn't match, one of your tier formulas is wrong.
A fourth test that institutional models use is the Sensitivity Test. Change the total distributable cash to extreme values and confirm the waterfall still behaves correctly. Set distributable cash to $8,000,000 (just enough to return equity). The LP should receive $7,200,000, the GP should receive $800,000, and no promote should be paid. Set distributable cash to $50,000,000. The LP should hit all three hurdles, and the GP should capture maximum promote. If the model breaks at these extremes, your tier logic has a flaw.
When prompting AI to build verification formulas, specify each test explicitly. Prompt: "Create a Zero Test that sums LP distributions and GP distributions and subtracts total distributable cash. The result must equal zero." Then prompt: "Create a Hurdle Test that calculates the LP's IRR after Tier 1 and confirms it equals 8%." This forces AI to build audit trails into the model.
For more on verification techniques, see our [Verification framework guide], which covers error-checking strategies for financial models.
Next Steps
You now have the structure to build a waterfall model using AI: define the tier boundaries, specify the promote splits, decompose the calculations into separate blocks, and verify the results with Zero Tests and Hurdle Tests. This approach works for any waterfall structure, whether it's a 2-tier, 3-tier, or 5-tier model.
The most common next question: "How do I handle annual distributions instead of a single sale?" The logic is identical, but you must calculate the preferred return year-by-year, compounding any unpaid pref into the following year. The waterfall still distributes cash sequentially through the tiers, but now you're running it once per year instead of once at exit.
If you need to model operating cash flow before distribution, return to the [Pro Forma Guide]. If you need to model complex LP/GP structures with multiple classes of equity, see the [LP/GP Structure Guide]. The waterfall sits downstream of both—it distributes whatever cash the pro forma generates, according to whatever equity structure you've defined.
The key insight: AI accelerates formula generation, but only if you define the structure first. Decomposition separates the tiers. Specification defines the splits. Verification confirms accuracy. These three steps—applied in sequence—produce waterfall models that institutional investors can audit and trust.
