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.
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.
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.
It would dilute the PE firm across all levels. The exact percentage would depend on the exercise price of the options vs. the common share price in the exit, so you would have to set up a calculation for that.
@@financialmodeling Can u tell me any of the videos where u have done that. Let's say the beginning PE sponsor percentage is 51%. Management is 49%. What happens if in the exit I give out 2% of esop to the management. Will the new pe percentage shareholding be 51%-2%=49% or will it be 51%/1.02= 50% ?
@@gowthamdhanasekaran3908 It depends on the exercise price since the proceeds from the options' exercise affect the equity pool, but if you assume a $0.00 exercise price, the PE firm's ownership is reduced by 2%, so it's now 51% / (1 + 2%) = 50%.
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.
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.
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.
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 UA-cam 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.
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.
This is brilliant hey - really love this guys videos - always clear and simple.
Thanks for watching!
This is best explanation on the internet
Thanks! Glad to hear it. That's what we aim for. :)
Great video & very easy to follow. Appreciate it!
Thanks for watching!
Brilliant Sir
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 beautiful ! thank you. Ali Khalil lawyer from Lebanon - Beyrouth
Thanks for watching
Really nice explanation. Thank you
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.
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.
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.
Hey what would happen if we give out esop at the end to the management if we reach a certain IRR threshold? How that would change the end ownership %?
It would dilute the PE firm across all levels. The exact percentage would depend on the exercise price of the options vs. the common share price in the exit, so you would have to set up a calculation for that.
@@financialmodeling Can u tell me any of the videos where u have done that. Let's say the beginning PE sponsor percentage is 51%. Management is 49%. What happens if in the exit I give out 2% of esop to the management. Will the new pe percentage shareholding be 51%-2%=49% or will it be 51%/1.02= 50% ?
@@gowthamdhanasekaran3908 It depends on the exercise price since the proceeds from the options' exercise affect the equity pool, but if you assume a $0.00 exercise price, the PE firm's ownership is reduced by 2%, so it's now 51% / (1 + 2%) = 50%.
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
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.
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.
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.
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.
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."
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 UA-cam 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.
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.
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.
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.