Waterfall Returns Distribution in an LBO Model

What is a "Waterfall Returns" Schedule? CONCEPT: In a leveraged buyout or any deal where an investment firm acquires another company, they'll often own close to 100% of it...
By http://breakingintowallstreet.com/ "Financial Modeling Training And Career Resources For Aspiring Investment Bankers"

Table of Contents:

1:04: Example of Management Promotes / Waterfall Returns

3:29: Rationale for Management Promotes and Giving Away Ownership

4:25: Step-by-Step Modeling Process for Waterfall Returns

6:35: Excel Setup

7:12: Level 1 IRR Calculations

10:05: Level 2 IRR Calculations

12:38: Level 3 IRR Calculations

13:55: Level 4 IRR Calculations

14:23: How the Waterfall Distribution Affects IRRs to Everyone

17:35: Recap and Summary

What is a "Waterfall Returns" Schedule?

CONCEPT: In a leveraged buyout or any deal where an investment firm acquires another company, they'll often own close to 100% of it...

But sometimes management will retain a small portion, or another investor group might retain a certain portion.

Sometimes it ends there - but sometimes, that smaller group gets ADDITIONAL ownership and a higher stake upon exit if the investment performs well.

This is called a "management promote" (if it's the management team that receives this as an incentive).

EXAMPLE:

A new leveraged buyout takes place, and the PE firm structures the deal to heavily incentivize the management team:

For an IRR up to 10%, PE firm gets 95% and management team gets 5% of the proceeds.

Then, for the portion of the IRR between 10% and 15%, the PE firm gets 90% and the management team gets 10%.

For the portion of IRR between 15% and 20%, the PE firm gets 85% and the management team gets 15%.

Then for the IRR above 20%, the PE firm gets 80% and the management team gets 20%.

A PE firm might do this to create a "win win" scenario - yes, it loses some of its IRR by giving up a % to the management team... but if all goes well, the team should outperform and help the PE firm achieve a higher overall IRR.

How Do You Model This Scenario?

1) Make assumptions for the initial investment and proceeds upon exit, plus the ownership percentages.

2) Make assumptions for how the proceeds split changes at different IRR levels.

3) For each "tier" of IRR, take the initial investment and calculate the amount of net proceeds upon exit that would correspond to that IRR.

Example: $1,000 initial investment, and 10% IRR tier - multiply by (1 + 10%), then multiply that number by (1 + 10%), and so on until the exit year.

4) Determine the split of proceeds within that tier.

If the actual proceeds are $1,500, for example, and $1,611 would correspond to a 10% IRR, you're done - just split the $1,500 between the PE firm and management team in a 95% / 5% split.

But if it goes beyond that $1,611, you just split up the $1,611 according to those numbers and then save the rest for the next tier.

5) Determine the proceeds to distribute in the next tiers.

For $3,000, for example, you'd distribute $1,611 and save ($3,000 - $1,611) for the next tiers.

If you're at the 10% level and you get something below $1,611, you'd set the "proceeds for the next tiers" number to $0 (use a MAX function for this).

6) Keep doing this for each tier of IRRs until the end.

The formulas get trickier as you move up because you need to use MIN and MAX to ensure that you don't get negative or nonsensical values.

In Level 2, for example, the "Amount to Distribute and Split" is:

=MIN(Net Proceeds That Correspond to 15% IRR in Year 5 minus Net Proceeds That Correspond to 10% IRR in Year 5, MAX(Total Net Proceeds minus Net Proceeds That Correspond to 10% IRR in Year 5, 0))

So you're taking the lesser of the proceeds between 10% and 15% IRRs, or the total remaining amount that can be distributed AFTER the Level 1 distributions.

And that same type of logic continues as you move down, until the last tier.

RESOURCES:

http://youtube-breakingintowallstreet...

http://youtube-breakingintowallstreet...