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 |
sorry just had a chance to view your responses. I see why you are focusing on the catch up provision being the interviewer specifically mentioned it. I was just surprised as I've been through a couple of these myself, and have seen how a lot of other large developers run their interviews...and none of them have a catch up model question -- you may have landed the unlucky shop that does!
Even so, just make sure you have the basics mastered...If I'm hiring, I'd rather have someone that knows the nitty gritty of modelling 90% of the deal terms we come across rather than someone who has a basic grasp of the 90%, but can model the shit out of that one off peculiar term sheet we're never going to agree to. But alas, I understand since he mentioned it specifically, you'd be a fool to not prepare for it.
Per your return of / on equity comment and pari passu...I'd be careful about lumping all of this together. Here's how I see MOST waterfall deals shake out:
Regardless whether the distributable proceeds are from cash flow, a refi, or a sale...they go as follows: -(i) pay the Sponsor and investor any unpaid and current preferred return @ 8% on a pari passu basis -(ii) pay the sponsor and investor their capital contributions pari passu -(iii) go into the IRR hurdles based on the Investor's capital contributions..usually 2 hurdles is plenty, sometimes youll see 3 at more institutional shops -(iv) have the residual split...call it 70/30
OTHER term sheets differentiate between operational cash flow and a capital event...meaning that the "return of capital" hurdle is only paid out in the case of a refi or sale. During the hold period, and available cash flow skips this hurdle and goes straight to the IRR hurdles. For education, I recommend running a waterfall both ways to see how each effects sponsor and investor returns...so that you can better understand WHY people ask for the clauses they do.
Sometimes things get very specific...
I've seen companies who take on 100% of the recourse risk being the developer/sponsor, where the investor doesnt share in any of hte recourse risk. To be compensated for taking this risk, they receive a % of the outstanding recourse balance as the first distribution in the waterfall, even before the pref...
Some companies do intangible land contributions. Some will use their developer fee and pledge it as equity...to limit the true out of pocket cash they need to contribute up front.
In a short period of time you'll be able to do these in your sleep,,,but I would go back to mastering the basics. Regarding the catch up - nothing wrong with goal seek...the manager might be impressed you know how to use excel in that regard. I do like iterative calcs (above writing a macro) because it removes that element from whoever is having to use it.
good luck!