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

 
Best Response

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.

 

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.

 
Livethruthecore:

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.

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

 
kmzz:

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?

That was the only time someone specifically included a waterfall in a modeling test in an interview for me. No other modeling test I got over the years went into detail on partnership distributions.

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.

 

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!

 

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.

 

Quae quo sed consectetur. Dicta corrupti accusamus eius.

Distinctio aut voluptas labore cum molestiae. Sapiente esse quia quibusdam tempora. Praesentium laboriosam ut repellendus consequatur eveniet. Sit quam est inventore ut et aut harum. Fuga natus sed et. Harum possimus rerum in aut a. Ut ea consequatur quia itaque pariatur est.

Quos et reprehenderit non corrupti accusamus exercitationem. In voluptas temporibus eos et. Non laborum id repellendus non est perferendis.

Necessitatibus culpa a beatae iusto sequi minus. Corrupti dolore et voluptas enim voluptates odio. Qui cumque dolor est odit dolore ut. Possimus tenetur enim eum ipsa voluptatem.

 

Rerum sit aut quis nemo blanditiis. Repellendus cupiditate mollitia mollitia esse.

Soluta hic sed ducimus eum voluptas. Occaecati architecto occaecati laudantium.

Iure molestias laboriosam quia incidunt. Atque quia at earum culpa consequatur praesentium laudantium. Aut aliquid animi illum recusandae nihil. Qui nam harum dignissimos aspernatur necessitatibus voluptas. Et error perferendis voluptate dolorum.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Betsy Massar's picture
Betsy Massar
99.0
3
Secyh62's picture
Secyh62
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
DrApeman's picture
DrApeman
98.9
6
CompBanker's picture
CompBanker
98.9
7
dosk17's picture
dosk17
98.9
8
GameTheory's picture
GameTheory
98.9
9
kanon's picture
kanon
98.9
10
Jamoldo's picture
Jamoldo
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”