Waterfall Returns Distribution in an LBO Model
Vložit
- čas přidán 24. 03. 2014
- 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 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:
youtube-breakingintowallstreet...
youtube-breakingintowallstreet...
This is brilliant hey - really love this guys videos - always clear and simple.
Thanks for watching!
Great video & very easy to follow. Appreciate it!
Thanks for watching!
At 12:04, instead of the min, max combination (the formula is a bit confusing), we can just take min(amount of IRR left to distribute in level 1, year 5 level 2 - year 5 level 1).
Thanks for adding that. Sure, there are usually alternate formulas. But we prefer MIN/MAX for many of these setups. If you find it confusing, a simple MIN might be better.
This is best explanation on the internet
Thanks! Glad to hear it. That's what we aim for. :)
This is beautiful ! thank you. Ali Khalil lawyer from Lebanon - Beyrouth
Thanks for watching
Great video! Where can I find the spreadsheet you reference at approximately 6:45 in to the video? Also, do you have other videos that go into more advanced waterfall calculations specifically for real estate? I've looked around your site and have not found anything as of yet.
+Synkai Harrison The spreadsheet at 6:45 is available in our Real Estate Modeling course, as are other more advanced tutorials on waterfall calculations. Please see the course outline for the Real Estate course, and you'll find numerous lessons on waterfall calculations.
Hi, this video is so helpful!!!! Could you please also advise the FULL catch-up scenario? Thanks a lot.
We hope to cover that in the future
Really nice explanation. Thank you
Thanks for watching!
When generally do you use waterfalls in modeling? I've seen a PPE waterfall & MLP drop down waterfall... how do you know when to use them?
Not all waterfalls are the same - with a PP&E schedule it is different and just refers to CapEx and depreciation over the years, not investor returns. You only use a waterfall for investor returns if the terms of the original investment state that different investor classes will receive different percentages depending on the deal's IRR... so you need to go back to the original documents and see what terms were outlined there.
Dear M&I,
Thanks for your video. I have a question regarding your excel formula.
When you calculate the amount that are distriibuted and split for IRR in a specific tier (lets assume tier 2), why dont you use a simpler formula Min($ in exit year corresponding with IRR 15% - $ in exit year corresponding with IRR 10%, amount of proceeds left to distribute for tier 2)? From your excel file, the formula should be MIN(I122-I99,I102). Is there any problem with my formula?
Thanks in advance.
Regards,
+Dieu Minh Tran Yes, you could do it that way as well. There is no difference in this case, we just set it up like this because it was a quick example and we were not trying to make all the formulas as short as possible.
Brilliant Sir
Thanks for watching!
Great video, thank you! Can I ask if this is typical of a 'european style' management incentive plan? Thanks
Thanks. Not sure if it is typical of a European-style incentive plan, but sometimes more complex plans are more common there.
Great video man. Trying to use your model to construct a waterfall payout but using cash on cash multiples rather than IRR thresholds. Cant seem to work it out, could you please share some insight?
It's really hard to say without seeing your Excel file. The mechanics should be very similar, so in all likelihood it is a small error somewhere. We do not do model reviews in this channel, but if you are a customer of our courses, you can submit your question and model via the BIWS contact page.
Great video, can you please provide us with the excel sheet, appreciate it.
bravo mir If you click on "Show More" and then scroll to the bottom, you will see the Excel files under "Resources."
Do you have something that would explain how the IRR affects the tax allocations per partners?
Thanks for your suggestion. Not at the moment, but we may cover that topic in the future.
how would your calculation change if you have a catch-up clause?
Kamal Al Mazam You would have to change the distribution order and allocate a certain % to the PE firm (or GPs if you're looking at a GP/LP split) such that above the hurdle rate, the investors get an IRR equal to the hurdle rate. And then once they receive that amount back, the normal split specified above the hurdle rate would apply. This isn't really applicable here, but if we set it up such that the LPs were required to receive a certain IRR before the GPs got anything it would make more sense... because then they would actually have something to "catch up" on.
Can you please provide the excel file for this video? I can't find it here. Thanks much!
Yes, please see the bottom of the description under "RESOURCES:" and the links there.
How if there is dividend distribution involved. Like each year all the cash after mandatory debt payment will be distributed as dividends? How to make the waterfall analysis?
It's more complicated then, and you have to track the running balance to investors, factoring in the dividends as well. Generally dividends are not enough to trigger other levels of the waterfall, so you just track this cumulative balance and then still divide up the proceeds upon exit. See some of our real estate examples.
what happens if on the 1st hurdle gives 100% LP: being preferred 9% non-compounded & in the middle of the project you have a refinance that on sum with previous years makes you had distributed 100% of the initial equity but the accrued 9% non-compounded?? What will the following preferred on LP during the second half of the project be?
a. the remaining accrued 9% non-compounded and after that go to 2nd hurdle
b. 9% of the initial equity each of the following years (same as first half of the project)
c. the remaining accrued 9% non compounded + 9% of that amount - CF
d. (?)
Thanks.
It depends on the terms of the waterfall schedule. This question is too detailed to answer in this free CZcams channel, so feel free to ask in our courses if you have signed up. There are limits to the types of questions we'll answer in comments on this channel.
great work. Can i get the excel to understand and to perform the working
Click "Show More." Scroll to the bottom. Click the links there.
Can I get tht excel sheet ur explaining so that I can understand more detail
Click "Show More" and scroll to the links at the bottom.
Wondering how we might visualize these results
A bar chart showing the returns to each investor group, with a separate chart for each band of returns most likely.