Advanced Waterfall Modeling and Fourth Hurdle Accrual Analysis

The fourth hurdle serves as a placeholder to signify the final terminal point of the capital account waterfall.
The preferred return accrual rate for this fourth hurdle is set to $500\%$ .
The promote level associated with this fourth hurdle is established at $40\%$ .
Construction of this section involves utilizing previously built formulas from earlier hurdle levels to maintain consistency in the accrual model.

When duplicating calculations from prior hurdles, the use of formula pasting via the shortcut alt + e + s + f ensures that formatting is preserved while logic is transferred.
Beginning and Ending Balances: These must be verified to ensure they flow correctly from the preceding terminal balance of the previous hurdle.
Equity Infusion: This metric remains consistent with the inputs established in the master assumption set.
Preferred Return (Pref) Accrual:
- The calculation references the beginning balance and the specific $500\%$ accrual rate.
- The formula accounts for the time period (months) and subtracts the beginning balance to isolate the current period's accrual.
- Due to the high placeholder rate ( $500\%$ ), the resulting accrual figure is significantly large.
Payoff Tracking:
- Previous Payoff: This must accurately track payoffs from the third hurdle (specifically referencing lines 150 and 151).
- Current Payoff: This is calculated by taking the sum of the capital account and multiplying it by the post-promote percentage, defined as $(1 - \text{Promote})$ . For the fourth hurdle, this utilizes $(1 - 0.40) = 0.60$ or $60\%$ of dollars.
- The calculation must also verify the remaining cash flow available after the distributions from the third hurdle have been satisfied.

Hurdle Number Four LP Cash Flow:
- This is determined by taking the sum of the cash flows within the capital account (previous payoffs, current payoffs, and equity infusions).
- This sum is then multiplied by the Limited Partner (LP) equity percentage, which is $90\%$ .
- Any previously paid LP cash flows are subtracted to arrive at the cumulative cash flow after the specific hurdle rate.
Hurdle Number Four GP Cash Flow:
- The General Partner (GP) cash flow follows the same logic as the LP, but utilizes the GP equity percentage of $10\%$ .
Promote Cash Flows:
- These are calculated by "grossing up" the LP and GP cash flows.
- The formula divides the cash flows by the post-promote factor $(1 - \text{Promote})$ and then multiplies by the promote percentage ( $40\% \text{ in this case}$ ).

Cumulative Cash Flows: Current hurdle cash flows (including GP and LP distributions and GP promotes) are added to the cumulative totals from the third hurdle.
Remaining Cash Flow: This is the levered cash flow (found in line 85) minus the cumulative cash flow distributed through the waterfall.
Even when a hurdle shows no cash flow (e.g., in month 60), the model should be extended to the full duration of the analysis to ensure all formulas are active and the sum of current payoffs remains zero when no funds are available.

The summary section aggregates the detailed calculations into three primary views:
- LP Cash Flows: A direct reference to the cumulative LP cash flows after the fourth hurdle.
- GP Cash Flows (Before Promote): A reference to the GP's straight equity distributions (specifically line 179).
- GP Cash Flows (After Promote): The sum of the GP's equity cash flows and the promote cash flows. This reflects the total compensation to the GP, as they receive both their pro-rata equity share and the performance-based promote.
Data for these summaries is typically pulled from the range of lines 185 to 199.

Performance is evaluated using the XIRR formula, which accounts for the timing of cash flows relative to specific dates.
Deal Level (Levered) IRR: $19.2\%$ .
LP Post-Promote IRR: $18\%$ .
GP Pre-Promote IRR: $18\%$ .
GP Post-Promote IRR: $28\%$ .
The discrepancy between the deal level IRR ( $19.2\%$ ) and the LP IRR ( $18\%$ ) is due to dilution; cash flow is disproportionately allocated to the GP through the promote structure.
The promote allows a GP to achieve significantly higher returns ( $28\%$ ) compared to the base deal return ( $19.2\%$ ).
Risk Symmetry: While the promote provides upside to the GP, the LP typically has priority in the distribution waterfall, meaning the LP is paid before the GP receives any promote in more challenging deal scenarios.

Waterfall modeling is a standard component of real estate and private equity modeling tests used during recruitment interviews.
The logic and formulas for building a waterfall remain essentially the same whether the model is built on an annual or monthly periodicity.
Mastery of these calculations requires building the model from a blank sheet multiple times to ensure the mechanics of the capital account and the promote gross-up are fully understood.