Waterfall Model

[Yonah Sturmwind]

I am trying to model a deal for someone involving a silent equity investor (LP) with a waterfall analysis, I do not have much experience with that type of modeling. The LP is providing 90% of the funds and the Principals are providing 10%. The agreement states for there to be a 5% pref; afterwich all remaining cash is split at 75% LP and 25% Principal. There is a 7% IRR hurdle where it becomes a 50/50 split.

Does anyone have any advice for modeling this in excel? Or does anyone have a template waterfall with hurdles analysis that I could look at?

TIA 

[Chin P.]

Sorry, I don't have any handy tools for you. I used to see and review a fair number of cash distribution waterfalls in the private equity/asset management world and the math really isn't all that complicated for any particular situation. Since you're talking about an Excel model, I think the trick is automating the calculations in a way that will handle all possible situations--which can be a bit more difficult. I once spend way too much time automating selling shareholder participation for an IPO where the private equity investors were providing an opportunity for employees to offload a pro-data portion of their stock options to match what the PE guys were selling. Don't ask me why they had the attorney doing it, I kept hinting that there had to be someone better than me who does this type of thing for a living for way cheaper than my rate but was ignored by the client. You should try to build your model as best you can, but you've got to be methodical in stress testing the accuracy of the model in all sorts of cash distribution levels designed to trigger and not trigger each step of the waterfall to ensure that your model is accurate. Once you've tested your model this way, you can rest easier that the model will correctly calculate distributions. You should also have "fresh eyes" stress test it also. It's like reviewing your own writing, you may miss stuff since you're so wrapped up in the model and another or a few set of eyes will catch stuff you may have missed. Hope this helps. P.S. Just to be safe, this isn't meant to be legal advice and if you feel like you need legal advice you should contact your own attorney.