Waterfall Distribution Excel Template

One of the most difficult forms of business logic I'm asked to model is waterfall distributions. Doing this kind of template in Excel is perfect, but the logic can be much harder than you think to make dynamic. You've got IRR hurdles, simple preferred returns, preferred equity, complex preferred returns, monthly basis or annual basis, compounding or non-compounding, accrued or non-accrued, reference to when the initial investment is paid back (for LPs) also known as return of capital vs profit distribution. 

Relevant Templates:

There are all kinds of nuance here that I've seen and no single way is right or wrong as long as all parties agree on the operating agreement. Building GP catch-up logic on top of the existing waterfall may be the most difficult thing to do within waterfall distributions. There are many ways to 'catch up' as well. It could be a catch up of total cash distributions or a catch up in cumulative IRR, which are much different and spit out different numbers.

I've integrated the most used waterfall distributions into these real estate models, but these types of deal structures can be used across all types of industries.

Sometimes the hardest thing to do is make sure everyone is using the same terms to mean the same thing. If you have a party that is being advised to use an operating agreement that includes more advanced joint venture language, but they have never had experience with such a thing, the terms are going to be really difficult to communicate between these parties. This is where a financial model template can come in handy to help all parties understand what the language means and how things change based on equity requirements and distribution amounts over time.

What is a Waterfall Distribution Agreement?

In a waterfall distribution agreement between a Limited Partner (LP) and a General Partner (GP) in a private equity or real estate investment context, several key terms are important to understand. This agreement outlines how profits are distributed between the LPs (investors) and the GP (investment manager). Here are the main terms:

Capital Contributions: The amount of money invested by both LPs and the GP. This determines their stake in the investment.

Preferred Return or Hurdle Rate: A minimum rate of return guaranteed to LPs before the GP can receive their share of profits. It's typically expressed as an annual percentage.

Catch-Up Provision: A clause that allows the GP to receive all or a large portion of the profits after the preferred return is paid to LPs, until a certain allocation percentage is reached. This is designed to 'catch up' the GP's share to an agreed level.

Distribution Waterfall: The process by which the profits are distributed. It's usually structured in tiers:

  • First Tier: Return of capital contributions to LPs.
  • Second Tier: Payment of preferred return to LPs.
  • Third Tier: Catch-up distribution to the GP (if applicable).
  • Fourth Tier: Profit-sharing between LPs and GP based on an agreed ratio (e.g., 80/20 where 80% goes to LPs and 20% to GP).

Clawback Provision: A clause that allows LPs to reclaim distributions from the GP if it turns out that the GP was overpaid in earlier distributions (typically applies at the end of the fund's life).

Carried Interest: The share of profits (usually the profit beyond the preferred return) that the GP receives. This is typically a significant portion of the GP's compensation.

Look-back Provision: A mechanism used to calculate distributions based on either an internal rate of return (IRR) or simple rate of return, ensuring that LPs receive their preferred return before the GP gets their carried interest.

GP Commitment: The amount of investment made by the GP, which shows their confidence in the investment and aligns their interests with those of the LPs.

Side-by-Side Investment: Refers to situations where the GP invests their own capital in the fund alongside LPs, often on the same terms.

Subordination: A term that can be used to describe how different classes of equity are prioritized in terms of distributions. Senior classes are paid before junior classes.

Vesting Schedule: If the GP's carried interest is subject to a vesting schedule, it means that their right to this interest accrues over time, often linked to the fund's performance or lifespan.

Understanding these terms is crucial for both LPs and GPs, as they significantly affect the return on investment and the risk each party bears. Negotiating these terms requires careful consideration to ensure alignment of interests and clarity on the distribution of profits.

Confusion of Terms

  • Preferred Return or Hurdle Rate: This is often mistaken for a guaranteed return, but it's actually a threshold rate that the LPs must receive before the GP can participate in profit sharing. The calculation of the preferred return can be complex, especially when dealing with issues like compounding and whether it's calculated on a cumulative or non-cumulative basis.
  • Catch-Up Provision: This term can be confusing because it changes the allocation of profits after the preferred return is met. The basis for what determines the GP being 'caught up' is a point to make clear.
  • Carried Interest: Often just referred to as "carry," this is the share of the profits that the GP receives. The misunderstanding usually arises in how and when the carry is calculated and paid, and under what circumstances it might be adjusted or forfeited (such as in a clawback scenario).
  • Distribution Waterfall: The structure of the waterfall itself can be a source of confusion. The sequence of distributions – return of capital, preferred return, catch-up, and profit split – can vary in different agreements, and the specific terms can significantly impact the overall distribution.
  • GP Commitment: The extent and nature of the GP's commitment can be misunderstood, especially in terms of how it aligns the GP's interests with those of the LPs and whether it's subject to the same terms as the LPs' commitments.
  • Subordination and Inter-Creditor Agreements: The priority of payments and rights in the case of multiple classes of equity or debt can be complex and often requires careful legal interpretation.
  • Capital Account Maintenance: The maintenance and adjustments of capital accounts, which track each investor's contributions, distributions, and share of profits and losses, can be a source of confusion, especially in relation to tax implications.