All Industries, Financial Model, General Excel Financial Models

10-year Financial Projections, 5-year Financial Projections, Cash Flow Analysis, Cash Flow Projections, Cash-on-Cash Multiple, CFO, Dashboard, Excel, Financial Analysis, Financial Model, Financial Modeling, Financial Planning, Financial Projections, Financing, Hedge Fund, Investment Multiple, Investor Cash Flows, Private Equity, Private Equity Waterfall Distribution Models, Scenario Analysis, Sponsors, Venture Capital

 

Video Tutorial:

Recently added a DCF Analysis, including NPV.

A joint venture waterfall that uses IRR hurdles to determine how distributable cash is split between the GP (general partner / sponsor) and LP (limited partner / investor) is a common way for real estate, oil, gas, and other smooth income streams operations to be structured. It gives an incentive for the GP to do a good job at providing higher returns to the LP.

The entire set of logic / formulas that do all the work is all on a single page. That will make this a very easy plug-in to any financial model that you have. All you have to do is paste it into a model and link the contributions / distributions to the very top. All the formulas will run off of that.

The template has up to 3 IRR hurdles and the beauty of such a structure is easily making the first hurdle a hard or soft preferred return. If you want to make it a hard pref. then simply make the first cash flow split be 100%/0% for the LP vs. GP. That will guarantee the LP gets 100% of the cash flows up until they have achieved an IRR that meets the first hurdle. For a soft pref. you can make the first hurdle split be pari passu. You can also make the split whatever you want and it doesn’t have to be 100/0 or pari passu.

I did add some final aggregate summaries of the data on other tabs for visualization purposes only and no actual distribution logic exists there. It is just a final IRR and cash position view.

The model has up to 10 periods, including a period of 0. If you want to extend it to be more periods, simply drag all the formulas in the last column over and adjust the hurdle to be equal to the length of time the period is (monthly/yearly).

One thing this cash flow distribution model does really well shows the detail of what is happening at each hurdle as well as the check rate in order to ensure the LP is getting the defined IRR within that tier (assuming that tier was reached). The template then flows nicely down to an aggregate per tier to easily see how much the LP/GP gets per tier all in one view.