Modeling a deal-by-deal waterfall step by step

The tier arithmetic is per deal: proceeds from a realisation fill return of capital, then preferred return, then catch-up, then the residual split. The gates that decide whether the tier arithmetic is even permitted to run are fund-wide, and that asymmetry is the entire modelling problem. A spreadsheet built as one row per deal with a carry formula at the end will be right on the first exit and wrong from the second onward.

ILPA's prescription for non-whole-fund models is a list of four requirements and each is a separate column in a working model. Return of all realised cost for the investment. Continuous make-up of partial unrealised impairments and write-offs — continuous, meaning the make-up is re-tested at every distribution rather than trued up once. Return of all fees and expenses to date. And valuation of unrealised investments at the lower of cost or market, which is a deliberately conservative convention that suppresses carry when the remaining portfolio is marked up and does not suppress it when the portfolio is marked down.

The modified deal-by-deal construction adds an interim clawback cohort on top, and ILPA reports it as its own category. Thirty-one per cent of respondents at five-billion-dollar-plus growth and buyout funds saw non-standard waterfall provisions such as hybrid or choice structures, so a model that only supports the two textbook shapes will not survive contact with the market it is being built for.

Structurally, everything below the limited partner return-of-capital and preferred return tiers is subordinated: the general partner's carry ranks behind those claims by construction. Some jurisdictions add a tier above all of them — in United Kingdom-style agreements the waterfall's first tier pays the general partner its unpaid priority profit share, which is the management fee structured as a profit share, before limited partner loan repayment, preferred return, catch-up and the residual split. A model built only against United States drafting will mis-order a United Kingdom fund on the first line.

Column one is realised cost, cumulative. Column two is the make-up balance: the aggregate of partial impairments and write-offs across the portfolio not yet recovered out of subsequent proceeds. Column three is fees and expenses to date, which in United States drafting are commonly folded into the return-of-capital tier rather than treated separately — a drafting difference that changes the size of the hole and therefore changes when carry first becomes payable.

Column four is the preferred return accrual, which runs on capital rather than on deals and therefore has to be apportioned. Column five is the catch-up, which is not a rate applied to proceeds but the solution to the target condition: carry percentage multiplied by the sum of profit already distributed and the catch-up, set equal to the catch-up itself. Column six is the residual split. Column seven, the one most models omit entirely, is the running hypothetical-liquidation position that the interim clawback tests against.

Two conventions inside the catch-up column are worth fixing before the model is built rather than after. The rate governs speed rather than destination — the sourced rates are eighty per cent in the ILPA model and one hundred per cent in United States drafting — and the tier is capped by available proceeds, which means the general partner does not always reach its stated percentage. A model that hardcodes twenty per cent of profit as the carry line will be wrong on exactly the distributions where the number matters.

The distinction between carry accrued and carry earned belongs in the model as two separate lines, because the ILPA Reporting Template treats them as two separate standardised items: carried interest accrued against unrealised profits, and carried interest earned on realised profits inclusive of the amount held in escrow. A model that reports a single carry figure cannot reconcile to a capital account statement built on that template.

A deal-by-deal model that stops at the residual split has modelled the payments and not the exposure. Interim carry is the reason clawbacks exist: the manager receives carried interest before final liquidation and therefore before the fund's aggregate net gains can be calculated, on the working assumption that unrealised investments will produce at least their carrying value.

The interim clawback is computed on a hypothetical final distribution at then-current values — a deemed liquidation in which the fund's assets and liabilities are settled at the valuation date and the whole waterfall is re-run from the beginning. ILPA's deal-by-deal model provides for that test at the first anniversary of the end of the investment period, on general partner removal, on any limited partner giveback, and annually after the investment period.

There is a harsher variant of the same test used in escrow release, and it belongs in the model as a second scenario: the same computation run on the assumption that all unrealised investments are realised for zero consideration, all remaining undrawn commitments are drawn, invested and realised for zero consideration, and the partnership has no assets available for distribution. The gap between the then-current-value result and the zero-consideration result is the honest measure of how much of the general partner's realised carry is actually at risk.

ILPA's warning about what interim clawback provisions actually do belongs in the model documentation. Some provisions so labelled merely calculate a hypothetical over-distribution and correct it through adjustments to future distributions rather than requiring the return of prior carry — which is a materially different cash flow and should be modelled as one.