Preferred Return Model: Multiple Hurdle Triggers and Two GP Catch-up Options

All Industries, Financial Model, General Excel Financial Models

10-year Financial Projections, 5-year Financial Projections, Accounting, Business Template, Calculator, Cash Flow Analysis, Cash Flow Projections, Cash-on-Cash Multiple, CFO, DCF Model, Discount Rate, Excel, Financial Analysis, Financial Due Diligence, Financial Feasibility, Financial Model, Financial Modeling, Financial Planning, Financial Projections, Hedge Fund, Interest Accrual, Investing Cash Flows, Investment Multiple, Investor Cash Flows, Investor Presentations, Investors, IRR (Internal Rate of Return), Joint Venture, Know-How, NPV (Net Present Value), Private Equity, Private Equity Waterfall Distribution Models, Return on Invested Capital (ROIC), Return on Investment (ROI), Scenario Analysis, Sensitivity Analysis, Sponsors, Venture Capital

Video Overview:

You may use this template to help model out a wide range of fund structures. It could be real estate, oil and gas, or other private equity investments in various companies. All you have to do is plug in the initial investment and then the expected distributions each year. By default, it is a 10-year view, but you can use it for more by dragging the last column of formulas over and making sure to adjust the IRR / equity multiple formulas to also extend out accordingly. If you are converting this to a monthly view, then also be sure to adjust the preferred return to a monthly rate instead of an annual rate. Since this is a simple pref. and not IRR hurdles, you can divide the annual rate by 12 to get the proper monthly rate.

Template Logic:

100% of cash flows go to pay the pref. return first.
Then, there is a GP catch-up option where 100% of cash flows go to the GP until they have received a defined proportion of the distributions up to that point (tie 1).
Then the cash is split at tier 1 until the initial investment has been fully repaid. If 100% of cash flows go to the LP until they have received the pref. + 100% of their investment back, you would simply set tier 1 at 100%/0% for GP/LP. That would also null the first GP catch-up as their rate to catch up would be 0% of the distributions up to that point.
After tier 1 (the equity would have been fully repaid to the LP) there is another catch-up for the GP to reach a defined percentage of the total distributions up to that point. After that is reached (if the second GP catch-up is turned on), the remaining cash flows are split at the final tier 2 distribution rate.
All the while that this is going on, the user can choose to accrue unpaid preferred returns or not, compound unpaid preferred returns or not, and select to have distributions to the LP beyond the preferred return reduce LP equity balance or not.
LP/GP DCF Analysis are included with NPV.

Final outputs include IRR and Equity Multiple for both sides of the venture. You can use this template over and over as deals are analyzed and assumptions adjusted. Note that GP fee income would sit on top of the available distributions and be entered as income for the GP.

All logic is contained on a single tab for easy porting to different workbooks without causing reference problems.

This template is also included in the all models bundle.