I was speaking with someone at NOEW about investment banking, and the financial models we use. I explained that we used DCF, LBO, and M&A models to help us determine how well a company might do in the future. He then caught me off guard and asked if we worked with waterfall models. Waterfall models (I thought to myself) What are those? Since getting home I have looked into them, but I have never seen one or created one.
Who uses these, and how do you create one? I would like to learn more about them so I am not caught with my pants down when someone mentions that type of model again.
My interpretation of a "waterfall" model isn't actually a model, but rather an equity allocation, where one calculates different breakpoints. Typically use this analysis when performing equity allocations, valuing contingent considerations, and sometimes firm value.
Do you know the process of creating one?
It's very specific on the equity instruments and terms of management compensation used in a given deal. Typically each firm has their own "standard" model that key assumptions / inputs will get plugged into.
Basically a fancy split between sponsor and management incentive comp
It's used quite a bit in restructurings.
Waterfall is used in carried interest calcs for PE.
It is used to calculate promotes in real estate
My bank's private placement team uses them a lot. Whenever we need to do our own analysis we just ask for their templates and tweak as needed / have them walk us through it.
Waterfall question (Originally Posted: 05/03/2016)
Hoping someone can help me out with this, it is driving me crazy and I feel like I am completely overthinking it.
I've attached a waterfall I've been working on. 3 tiers, the 2nd is one is what is tripping me up when I try to stress test it.
Tier 1: Regular 6% preferred return to both GP and LP
Tier 2: 50 / 50 split until the GP receives 20% of the cumulative project profit. The existing formula works if the cashflows are only in the final year when the 20% hurdle works (as seen in the "Key" tab). However, when I stressed it to show sizable cashflows in the 2nd to last year, the formula hits the 20% hurdle even when cashflows are not sufficient to paydown that amount (i.e. negative excess cashflow in row 48). The cell I stressed is F48, highlighted in yellow. I am looking for a formula that will only split the actual available cashflows and also stop subsequent payouts once the 20% profit hurdle is met.
Bananas for whoever can help me from banging my head against my desk.
is your question tied to why is F31 = $6 then why are F35 & F36 totaling in the thousands for distribution?
I generally use IRR lookback approach, but I get it is a test model.
god what a nightmare. i finally just had to give up.
Can you change the way you're modeling Tier II? If you set it up similar to Tier I, your accrued return line item would become "accrued cumulative profit" or whatever it's technically called, and then the distribution would be 50% of that amount capped at 20% cumulative profit. Think that would work.
I'm not going to dig through your model but Indexmatch is correct. Model Tier II same way as Tier I. (Excess CF beyond Tier II return metric - Excess CF beyond Tier I return metric)*split = Tier II returns for both GP and LP. Any excess CF of Tier 2 is obviously split via tier III distribution.
On a side note, that is one very weird waterfall. Promotes are based on LP hurdles. GP preferred return is non-existent unless the GP is comprised of another partnership.
Agree with all the above comments. Also you'll want to make your model dynamic so it accounts for the compounding timeline of the CFs. Ie is the IRR compounded monthly, quarterly, annually? I've seen all three, though quarterly is more rare. Point being make sure you have dates for the compounding to draw off of, or it will always assume annual compounding
Why are your percents not formatted as percentages? They have % signs but they are whole numbers. For example, in cell C19 you account for this by dividing the GP Equity Share (5%) by 100. I guess it works out the same but it makes everything much more complicated. Never seen that before...weird...
With regard to your question, I would create a separate line in Tier 2 that calculates what 20% of the total profits would be at the end of each year. So, it would just be 20% times an IF formula where if the sum of the cumulative Tier 1 distributions (Line 29) and the cumulative Excess CF after Tier 1 (Line 31) is positive you would sum those, otherwise 0.
That new line tells you what you would need to distribute to the GP to reach the 20% of total profit hurdle at the end of each year. The GP Tier 2 Distribution (Line 38) is the MIN of the new line (in that year) minus any previous Tier 2 distributions or 50% of the Excess CF after Tier 1 in that year.
In principal, I think that should get you close...may need a little tweaking. I wasn't able to try it out myself...didn't want to go through the brain damage of dealing with the percentages. lol
Officiis quibusdam ex in quasi in quis aut laboriosam. Distinctio quibusdam assumenda ea voluptatibus porro perspiciatis dolor cumque.
Occaecati officia velit aut perferendis. Quo odit id est illo. Et quo nobis corporis eum eius voluptatem.
Ad ut ipsum autem soluta autem fugit. Esse voluptatem dolor reprehenderit id.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...