Hi All,
Hoping someone can assist as I have an interview test coming up which I believe is going to focus on the technical side of JV structures (modelling test...yay!). I am currently working for a developer which does not engage in JVs so I am not very experienced in the process but I've been educating myself over the holiday period.
I started off with, and learned to create from scratch (lots of credit to Adventures in CRE), a typical pari passu, 4-tier equity waterfall model (all fine). I then decided to add some bells and whistles and threw in the Pref return of capital for LP function (all fine again...I think). But now I'm tackling the addition of a catch up provision which I am struggling with...I understand the concept but hit a road block trying to model it.
Question; How to do I go about modelling catch up without the use of 'goal seek' and/or 'solver'?
I've seen people ask the same question on these forums, but unfortunately for me, the answers look to be hidden behind PMs. I have managed to calculate it with goal seek/solver but I'd prefer not use those tools in an interview test.
I've attached an excel file for my workings (sorry about the sloppiness, aesthetics is not a high priority right now) so people can see where I am coming from. I've input quite a few different scenarios and everything seems to be running as intended so far but like I said; not an expert on this side of the modelling.
Would be delighted if anyone could offer any help or maybe even additional advice on this.
Bonus Question; Any other technical aspects of the JV i should be looking to conquer (I'm fine with EM calcs), like modelling provisions such as look back or claw back? Company acts as GP on it's ventures if it's of any benefit to the question.
Thanks chimps!
| Attachment | Size |
|---|---|
| Capital360_Waterfall Attempt 62 KB | 62 KB |
I opened the file and almost immediately realized your pref calculations are wrong. Well, either they are wrong or your dates are incorrect. The basic IRR function assumes annual cash flows. Using your example...from jan-feb you are only getting 1 month worth of an 8% pref (have you mastered the use of compounding and non-compounding, and the different compounding time uses aka monthly annually etc.?). To be nitty gritty, the formula needs to = "beginning balance" * ((8%/365)*(# of days between jan and feb...or whatever date period you are using)...some people just use 8%/12...but it's a matter of time before some prick analyst is going to get up your ass that the 3rd decimal point in one year doesnt EXACTLY equal 8%...so mine as well set it up right. And no kidding, it builds confidence with partners being that skilled at modeling. JV's means model sharing. And if I know the associate/analyst is skilled enough to model that level of detail, I naturally trust the rest of the model is more or less running correctly.
To not completely get off topic...I think you're going about it the right way - but jumping too far ahead. I'd be surprised if they ask about "catch up provisions" in the interview for your level position. And they would likely ask many other questions that are much more common in deal structures before anything about catch ups -- such as difference between compounding and non compounding preferred rates of return....modelling the waterfall with return of capital just coming from a capital event and not cash flow to ensure operational cash flow is truly return ON equity and a capital event is return OF equity. These are the points that are more common to be hashed out -- and based on looking at your model, I think you could bear to master the basics a little better.
Hope this helps and keep at it. Good luck! Don't be discouraged -- I think you'll find these interviews are rarely as technical as they seem. If for no reason that every development shop runs their JV's usually in a specific way to them, and you're going to have to just learn it when you get there...
Howdy, thanks a mil for the detailed response.
Yeh my bad, I knew that...I usually model monthly cash flows but to shorten the spreadsheet I brought it down to yearly and forgot to change the date system. I'm an XIRR type of guy with the ((1+ Rate)^(1/12)-1) calc instead...I'm always 0.01% out on my IRR calc because I presume 2020 being a leap year I miss the 1 day interest (could be wrong).
I did a meet and greet already with one of the team and at the end I tried to suss out what would be involved in the modelling test and he said something along the lines of "Oh a simple catch up provision and that sort of thing"...so that is what has me stressed out about covering those sort of aspects on the waterfall models. I did mention that my PE distribution modelling wasn't on par with my project level modelling but whether he took it on board or not I don't know.
To be honest I've not idea what I am going to be presented with, I'd be the only analyst at the place (5 man team) apart from an asset manager who does the odd bit of modelling for them. I'm just trying to cover quite a few different avenues. As it stands I don't see myself being the best person for the job but sure I'm going to go in and do the test and see what type of standard they are looking for.
Also to answer your question about the goal seek...the short answer is no there is not. Depending on how the deal is structured you can enable iterative calculations and write the formulas so that they are singularly circular - this is what I have done in the past to avoid hoping the JV partner analysts understands and can use goal seek.
Some other things I also caught..again just looked at it for 2 minutes so i apologize for assuming anything or not catching more..
-your distributions are not pari passu...you may have purposefully done this as per the catchup clause, but more often than not jv deals are struck as pari passu even if a catch up exists. meaning, that if there is $100 to distribute in a period, each partner would receive their equity contribution % * $100.
-definitely use XIRR when doing your checks...you're seeing 12% and thinking all is correct when in reality it's way off because you're using an annual formula for monthly cash flows, as mentioned above. Also, this helps when modeling actual deals as most distributions are made quarterly, but compounding is usually annually and sometimes monthly.
-we know the IRR checks arent working per the above comment, but if these were annual dates and not monthly the later hurdle IRR checks aren't tying out to the hurdle rate. IRR hurdles are almost always calculated on the INVESTOR's capital contributions. Thus if they are the ones receiving the catch up, you'll have to know how to interpret the waterfall language to ensure the catch up distributions are being accounted for when calculating the hurdle.
LASTLY - please remember to follow the KISS principle. Too often I see analysts and fresh grads spend hours going over free online waterfall models and modules, and to keep the professionalism of WSO intact, I'll use the phrase "put excel models on a pedestal". It's extremely important to not lose sight of what you are trying to model. Remember that these are real people and real dollars that are being distributed, so just think it through how it has to actually occur.
That's my rant on that -- as you can tell, I'm very sick of college football and expelling my attention on other things...
here's some websites that explain the catch up provision well...but as I said earlier make sure you understand all the other more common intricacies of a waterfall first.
https://www.getrefm.com/in-plain-english-the-real-estate-private-equity…
https://origininvestments.com/2017/12/28/what-are-private-equity-waterf…
AND THE MOST IMPORTANT OF THEM ALL, is a REFJ article... http://media.straffordpub.com/products/real-estate-joint-ventures-water…
Good to know, I was just chancing my arm at trying to find a simple solution.
Cell C28 is an on/off switch for pari passu or no. I may have gone about it wrong but I've given the model the option to split equal return of capital or that the LP 100% return of capital (+ 8% interest) until the first hurdle rate is met. Once the LP gets all their capital and interest back, 100% of cash starts being distributed to the GP until they reach their 8% return hurdle.
Thanks, as someone looking to the break into REPE I find this thread extremely interesting. @Capital360 - can you post an updated model?
I've spotted a few errors and made some changes, I'll share it again once I've had the time to run it through a few different scenarios and am happy that it's functioning as should.
Magni culpa dolor eos aliquam optio. Esse et ducimus nam eos nam. Commodi vitae quaerat harum ut est.
Suscipit odio eveniet consequuntur aspernatur. Dolores maxime quos voluptatum repellat provident voluptatibus ut iure. Beatae rerum iste voluptas repudiandae eveniet doloribus. Mollitia exercitationem natus accusamus error nihil.
Non totam exercitationem perspiciatis velit tenetur. Ea ea aut dolores. Est corporis quibusdam cumque cupiditate est voluptas possimus.
Blanditiis esse recusandae et ea quia placeat aspernatur. Quidem omnis quod recusandae velit assumenda ad laudantium. Nemo quia eaque enim molestiae. Aut enim magni exercitationem consequatur culpa.
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...