Comprehensive Study Guide on Waterfall Modeling and Joint Venture Cash Flow Structures

Financial Model Overview and Levered Returns

  • Model Structure and Initialization

    • The model utilizes levered cash flows based on a assumed 55-year hold period for building the waterfall.

    • The primary metric for calculating levered returns is the Internal Rate of Return (IRR), calculated using the XIRR function in Excel.

  • Investment Multiples and Profit Calculations

    • The multiple on investment is calculated as the ratio of positive cash flows to negative cash flows:

      • The numerator is derived using a SUMIF function where criteria is set to >0> 0.

      • The denominator is derived using a SUMIF function where criteria is set to <0< 0.

      • Essentially: Multiple=Positive Cash FlowsPositive Version of Negative Cash Flows\text{Multiple} = \frac{\text{Positive Cash Flows}}{\text{Positive Version of Negative Cash Flows}}

    • Profit is calculated as a straight sum of all levered cash flows across the hold period.

Waterfall Structure and Hurdle 1 (12% Preferred Return)

  • General Waterfall Framework

    • The waterfall is constructed using a capital account perspective.

    • The model utilizes a "Beginning, Additions, Subtractions, and Ending" structure:

      • BOP (Beginning of Period): The starting balance, which is the previous period's EOP.

      • Accrual (Additions): Interest or preference being added to the unpaid balance.

      • Infusion (Additions): Additional capital provided to cover negative levered cash flows.

      • Payoff (Subtractions): Distributions of cash to reduce the capital account balance.

      • EOP (End of Period): The final balance after all additions and subtractions.

  • Hurdle 1 Parameters

    • Preferred Return (Pref/Hurdle): 12%12\%

    • Promote: 0%0\%. There is no promote until the first hurdle is cleared.

  • Infusions Logic

    • Infusions are required whenever levered cash flows are negative (shortfalls).

    • Formula: −min⁡(Levered Cash Flow,0)- \min(\text{Levered Cash Flow}, 0).

    • From the perspective of the capital account, an infusion is a positive entry (money entering the account), though it represents a cash outflow from the investor's pocket.

  • The Accrual Formula

    • Accruals are calculated to growth the capital account at the hurdle rate while accounting for the actual number of days in a year (Day Count Convention).

    • Base Growth Rate formula: (1+Hurdle Rate)Actual Days365(1 + \text{Hurdle Rate})^{\frac{\text{Actual Days}}{365}}

    • To find the specific accrual amount:         Accrual=BOP×((1+i)d2−d1365−1)\text{Accrual} = \text{BOP} \times ((1 + i)^{\frac{d_2 - d_1}{365}} - 1)

    • Where ii is the anchored hurdle rate and d2−d1d_2 - d_1 is the difference between dates. Subtracting the BOP at the end ensures only the growth (accrual) is captured, not the grossed-up balance.

  • Payoff Calculations

    • The payoff determines how much cash is distributed to reduce the capital account.

    • It is the minimum of the capital account balance (sum of BOP, accrual, and previous payoffs) and the available cash flow.

    • To handle negative cash flow scenarios, a MAX formula is used: max⁡(min⁡(Capital Account Balance,Cash Flow),0)\max(\min(\text{Capital Account Balance}, \text{Cash Flow}), 0). This ensures no payoff occurs if cash flow is negative.

Hurdle 2 (16% Hurdle and 15% Promote)

  • Key Assumptions

    • Hurdle Rate: 16%16\%

    • Promote: 15%15\%

  • Standalone Hurdle Construction

    • Hurdle 2 is built as a standalone calculation starting from year zero, but it layers in "Previous Payoffs" from Hurdle 1.

    • This ensures that the accrual continues until the higher preference is met.

  • Promote Priority ("The Promote Eats First")

    • When a promote exists, the promote receiver (GP) gets paid their percentage before the remaining cash can be used to pay down the capital account.

    • Therefore, only "post-promote dollars" are available for the capital account payoff.

    • Formula for payoff limit: Remaining Cash Flow×(1−Promote %)\text{Remaining Cash Flow} \times (1 - \text{Promote \%}).

  • Separating Cash Flows

    • Cash flows for this specific hurdle are calculated by summing infusions, previous payoffs, and current payoffs.

    • The GP and LP receive these cash flows pro-rata based on equity ownership (e.g., 90%/10%90\% / 10\%

    • To isolate the cash specifically for Hurdle 2, the cumulative cash flows from Hurdle 1 are subtracted from the result.

  • Promote Cash Flow Calculation

    • Because the LP and GP cash flows represent the post-promote dollars (85%85\% in this case), they must be grossed up to find the promote amount.

    • Promote Cash Flow=(LP Cash+GP Cash1−Promote %)×Promote %\text{Promote Cash Flow} = (\frac{\text{LP Cash} + \text{GP Cash}}{1 - \text{Promote \%}}) \times \text{Promote \%}

Hurdle 3 (20% Hurdle and 25% Promote)

  • Hurdle 3 Parameters

    • Preferred Return: 20%20\%

    • Promote: 25%25\%

  • Calculation Adjustments

    • Previous payoffs now includes the sum of payoffs from both Hurdle 1 and Hurdle 2.

    • The accrual uses the 20%20\% rate.

    • Current payoff availability: Remaining Cash Flow after Hurdle 2×(1−25%)\text{Remaining Cash Flow after Hurdle 2} \times (1 - 25\%).

Hurdle 4 (Last Hurdle: 500% Hurdle and 40% Promote)

  • The Terminal Hurdle Concept

    • The last hurdle uses an intentionally high hurdle rate (e.g., 500%500\%

    • This ensures the capital account accrues so rapidly that it can never be fully paid off by the cash flow, thereby forcing all remaining cash to be distributed according to the final promote split.

  • Final Distributions

    • Once everything is distributed in Hurdle 4, the "Remaining Cash Flow" line should solve to zero, indicating every dollar from the levered cash flow has been allocated.

Post-Waterfall Analysis and GP vs. LP Return Profiles

  • Deal Level Metrics (Pre-Waterfall)

    • IRR: 29.5%29.5\%

    • Multiple: 3×3 \times

    • Total Profit: 2,175,000kJ2,175,000 kJ

  • LP Return Profile

    • IRR: Approximately 25%25\%

    • The LP IRR is lower than the deal level IRR because the promote structure dilutes their returns in favor of the GP as higher hurdles are met.

  • GP Return Profile

    • IRR: Approximately 53%53\%

    • The GP receives both their pro-rata cash flow (10%10\%) and all promote dollars.

    • On an investment of 100,000kJ100,000 kJ, the GP generated 650,000kJ650,000 kJ in profit. This demonstrates the power of the promote to incentivize performance.

  • Incentive Alignment

    • LPs (often asset managers or private equity firms) prioritize guaranteed returns for their investors.

    • They are often willing to sacrifice upside (dilution from 30%30\% deal return to 25%25\% LP return) to de-risk the deal and motivate the operating partner (GP) to hit performance targets.

Transitioning to a Monthly Waterfall Model

  • Joint Venture Assumptions

    • Equity Split: LP 90%90\%, GP 10%10\%

    • Hurdle 1: 12%12\% Pref, 0%0\% Promote.

    • Hurdle 2: 16%16\% Pref, 15%15\% Promote.

    • Hurdle 3: 20%20\% Pref, 25%25\% Promote.

    • Hurdle 4: 500%500\% Pref, 40%40\% Promote.

  • GP Cash Flow Splits

    • The GP split represents the total percentage of cash the GP receives in a given hurdle.

    • Formula: GP Split=Promote %+(GP Equity %×(1−Promote %))\text{GP Split} = \text{Promote \%} + (\text{GP Equity \%} \times (1 - \text{Promote \%}))

    • Example for Hurdle 2 (15%15\% promote, 10%10\% GP equity):         15%+(10%×(1−15%))=15%+8.5%=23.5%15\% + (10\% \times (1 - 15\%)) = 15\% + 8.5\% = 23.5\%

    • In the fourth hurdle, the GP earns 46%46\% of the cash flow despite only investing 10%10\% of the capital.