Waterfall / Promote modeling
I have been trying to model this and am having trouble. Any help in terms of how to tackle this (a template or thought process) will be appreciated
Investor 90% $18,000
Sponsor 10% $2,000
Initial Equity $20,000
<span><a href="/finance-dictionary/what-is-internal-rate-of-return-IRR"><abbr>IRR</abbr></a></span> Investor Sponsor
Hurdle 3 12% 100% 0%
Hurdle 4 18% 85% 15%
Hurdle 5 22% 80% 20%
Final Split 70% 30%
Assume for $6000 cash flow for the next 10 years.
Thanks
delete
Treat the hurdle like you would model a revolver. Contributions increase balance, distributions decrease balance, and the preferred return accrues like an interest rate. Model it from the perspective of the Investor (in other words, assuming there is only one contribution of capital at Time 0, make that contribution $18,000 not $20,000).
On your first hurdle (which you call Hurdle 3?), you will have 100% of cash flow pay down the accrued balance until you've zeroed it out (use max/min functions). Then, when the first hurdle has been satisfied (i.e. the revolving balance has been paid down), all cash flow drops down to the next hurdle.
The next hurdle will function the same way as the first hurdle, except: (1) it accrues at 18%, (2) you have to incorporate all the contributions and distributions from/to the Investor already accounted for in the first hurdle, and (3) apply the cash flow that dropped down from the first hurdle to this hurdle, but make sure that you apply no more than 85% of that cash flow to this hurdle in the waterfall (again, max/min). By calculating what gets distributed to the Investor in this hurdle, you can figure out what the Sponsor gets based on the 85/15 ratio.
Same principal applies to all future hurdles, and if there's anything left after the last hurdle, you split the rest 70/30. Build checks for yourself to make sure it's working correctly. Sum up the Investor's cash flow through each hurdle and make sure the IRR ties to the hurdle. Sum up the total cash flows to investor and sponsor and make sure those tie to the project-level cash flows.
Analyst Case Study - Do I need to know waterfall modeling? (Originally Posted: 11/15/2014)
Hi all,
I have a final round case study with a RE firm that does direct equity and debt investments. They have given me heads up that I am expected to project cash flows, model capital structure, and model returns.
Is it common for case studies for analyst candidates to require a waterfall? If so, does anybody have some resources they are willing to share or helpful suggestions to prepare me for such modeling.
Any information or advice would be awesome! Thanks in advance guys.
Real estate hiring isn't like investment banking hiring where there is a common and set model for hiring. Every single real estate firm, office, team, individual will have his or her own method of testing (or not testing) candidates for different roles and different experience levels. It's impossible to say what they will want you to know. Waterfall modeling isn't that complicated in Excel if you understand the reasoning and terminology, but it's not super easy either, especially if you're a newbie.
You just have to ask yourself if it makes sense for them to ask you to do this or not. If you're entry-level and a history major and you've made it pretty far into the hiring process then I doubt they will give you crazy complicated stuff--if they expected you to be able to come in off the street and crush Excel or Argus at a high level then they probably aren't hiring entry-level history majors. If you're an experienced hire and they've brought this up in interviews then expect it.
Best thing for you to do is google a waterfall Excel model. There should be plenty floating out there.
Hey mate,
Working in structured finance in real estate. Model the waterfall like this (assuming you are referring to a development project where facilities have capitalising interestt):
1) Net Revenues (Gross rev net of selling expenses) 2) Senior Debt Repayment - In excel '=min(Net Revenues, sum(Opening balance, capitalised interest))' 3) Mezzanine Debt Repayment - In excel' =min(Net revenues-Senior Debt Repayment, sum(opening balance, capitalised interest))' 4) Preferred Equity - excel formula follows the same convention as above. 5) Equity - any remaining proceeds.
Let me know if you need help with anything else.
Ya no shit. The difficult issue in creating waterfall models is designing the max/min formulas that adjust for different scenarios and distributions of JV equity splits, based on varying timing of distributions and hurdle rates
Yes all those considerations will need to be addressed specifically in the cash flow waterfall. But won't be able to pre- empt specific arrangements between JV partners without knowing the specific details of a deal. Formulas need to be bespoke to each transaction. The above assumes all equity participants rank pari passu
Might as well prepare for it. I was once given an excel test with a waterfall but it was already half way set up for me and it was a take-home, so I worked through it.
prospie really im curious as to what structures (or how complex) people are giving during interviews.. care to provide any guidance on the general terms from what you're heard/seen?
In that case there wasn't anything unusual about the JV ... 95% / 5% invested, normal set of hurdles. I coudl be wrong, but if I remember correctly, I think it was calculated that the GP got nothing until the LP got 100% of principal back. So there was a big fat zero for the sponsor (despite positive cash flows) until the back end. I think the model itself was only like 2 tabs - whatever it was, it wasn't overwhelmingly complex and I've seen much worse.
edit: I just remembered, I think the waterfall had a series of manual calculations where you had to play with the numbers yourself to reach each hurdle. Pretty pathetic way of running the waterfall.
A standard scenario you need to be able to model: 1. JV with 90/10 equity split 2. LP gets a 8% pref 3. CF paid out pari passu after the pref is reached
Dispo splits 1. GP gets a 10% promoted interest once the LP hits his 8% pref. i.e. Gp gets 20 / LP gets 80 2. 15% iRR - GP gets 70 and LP gets 30
The key is just to create a line that tracks the capital balances over the hold period.
Make sure you know what the difference b/t return on capital and return of capital is.
Thanks, these look pretty simple.
Prospie - manual calcs sounds like cheating, and no model flexibility! odd
Hi everyone, I have a question about returning equity within a waterfall.
The sample model I am learning from, is returning equity to the investors as soon as it has anything more than the preferred return for that specific month.
For example:
Month X Post-debt NOI = 100 LP 1,2,3,4 each get 8% Pref = 75 Remainder = 25
The model then pays out the 25 to the investors, split based on their initial pro rata equity contributions, as a return of equity. It even ignores LP pref accrual accounts to return equity from extra cash flow from that month.
Now, this seems way off to me based on my understanding, which is that the waterfall is supposed to track the return to LPs, try to clear their 8% pref accrual balances, and therefore after achieving an 8% return to investors, move down to tier 2, which in this case is a promoted interest split until all investors have received a 12% IRR.
What I'm trying to do:
TIER 1
Preferred Return to LP Investors 8.0% Annual Interest Rate
TIER 2
GP Promoted Interest 10.0%
LP Investors 90.0%
UNTIL
Until ALL Investors get: 12.0% IRR
TIER 3
GP Promoted Interest 20.0%
LP Investors 80.0%
Am I right? Or is it normal to return equity when the pref return is still piling up in balances?
Thanks!
Thanks for that info. Much appreciated.
Do you happen to have a place that has the template? I understand what you said, but being able to see a template that is doing it would help!
Two Waterfall / Double Promote Scenario (Originally Posted: 03/26/2013)
I am modeling returns for a real estate project. The JV partnership structure is a two-waterfall/double promote method.
I have the first waterfall completed. However, I am stuck on what cashflows to use for the second waterfall and how to generate them.
1st = 10% Sponsor (Sponsor+Partner), 90% 3rd Party Invest. 2nd = 30% Sponsor (or 3% of total equity), 70% Partner (7% of total equity)
Is the solution to simply take the cashflows generated in step one the 1st Waterfalls where you calculate "CF Available to Sponsor"... (10%*period_cf)?
ignore post I found the solution...
What was it?
use the total cashflows to the sponsor from the 1st waterfall. depending on how you've calculated each hurdle/tranche, cashflows may might have to sum each one up or if its cumulative take the final cashflow.
Monthly Waterfall Model (Originally Posted: 01/12/2016)
Looking for a xirr waterfall model template in excel with some built-in checks. Anyone out there have anything good?
Monthly waterfall is the same build as an annual waterfall. Account for monthly interest compounding for your pref...
Waterfall modeling question - promotes (Originally Posted: 02/10/2015)
Scrambling for a deadline and need some help. The waterfall structure is 94/6 split to 8%, with the second tranche a 75/25 split to a 14%. My question is how do I determine how much of the 25 in the second tranche is promote vs. how much is "equity" that can pay down the balance? Any help would be greatly appreciated!
Not sure I understand your question. Just think about it as a pool of proceeds and who gets what cut of the proceeds. If the hurdles are 8% and 14% IRRs, I'm not sure what you mean by "equity" and "paying down the balance"
If you are trying to model the waterfall, you would need to know the promote beforehand...
ie. the promote would kick in once both parties receive 8% IRR and then another promote if/when the parties receive the 14%
Waterfall fundamental question (Originally Posted: 06/30/2009)
Folks,
Can someone point me to a resource that can explain to me the A to Z of Waterfall analysis in Investment banking ?
V
Buy the book.
one of the greater books in my finance collection...
Could you tell me the name of the book?
It's in the title of MoneyKingdom's post: "The Art of M&A"
http://www.amazon.com/Art-Fourth-Merger-Acquisition-Buyout/dp/007140302…
That's the fourth edition, which I believe is the most recent.
Thank you for making my otherwise mundane morning into a burst of laughter.
Hey, I looked into the Art of M&A. If you were interested in iBanking and had to buy 1 book, would that be a good one?
Ea et aliquam qui eos sapiente iste iusto. Consequatur voluptas velit magnam nesciunt et incidunt.
Necessitatibus et nobis nisi rerum quam. Nihil facere voluptatum repudiandae repudiandae corrupti. Sunt molestiae porro non impedit. Accusantium libero praesentium et nihil ut vel incidunt.
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...
Vero et aperiam ut quis mollitia nesciunt velit. Et voluptate cum expedita quod est.
Excepturi in rem nisi ad. Nihil ex est aliquid iure quia tenetur placeat.
Nemo corrupti quia aut sint sed earum nemo unde. Aut dicta aliquid eum eos vero dolorem facere.