Versatile Preferred Return & Waterfall Distribution Fund Model Template

Description
I have created a versatile fund model template that you can use to model a wide range of fund structures, whether it's in real estate, oil and gas, or other private equity investments. All you need to do is plug in the initial investment and the expected distributions for each year. By default, the template offers a 10-year view, but you can extend this by dragging the last column of formulas and adjusting the IRR and equity multiple formulas accordingly.


If you're converting this to a monthly view, remember to adjust the preferred return to a monthly rate instead of an annual one. Since this template uses a simple preferred return (pref) structure and not IRR hurdles, you can divide the annual rate by 12 to get the correct monthly rate.


Template Logic:

• Preferred Return Payment: 100% of cash flows initially go toward paying the preferred return to the Limited Partners (LPs).
• GP Catch-Up Option: There's an option for a General Partner (GP) catch-up where 100% of cash flows go to the GP until they have received a defined proportion of the distributions up to that point (tier 1).
• Cash Flow Split at Tier 1: After the GP catch-up, cash flows are split at tier 1 until the initial investment has been fully repaid to the LPs. If you prefer that 100% of cash flows go to the LPs until they have received both the preferred return and 100% of their investment back, you can set tier 1 at 0% for the GP and 100% for the LP. This adjustment would also nullify the first GP catch-up since their rate would be 0% of the distributions up to that point.
• Second GP Catch-Up: Once the LPs have been fully repaid, there's an option for a second GP catch-up to reach a defined percentage of the total distributions up to that point.
• Final Distribution Split (Tier 2): After the second GP catch-up (if activated), the remaining cash flows are split at the final tier 2 distribution rate.

Throughout this process, you have the flexibility to:

• Choose whether to accrue unpaid preferred returns.
• Decide if unpaid preferred returns should be compounded.
• Select whether distributions to the LP beyond the preferred return should reduce the LP's equity balance.

The template includes discounted cash flow (DCF) analysis with Net Present Value (NPV) calculations for both the LP and GP. Final outputs provide the Internal Rate of Return (IRR) and Equity Multiple for both parties involved in the venture. You can reuse this template as you analyze different deals and adjust your assumptions accordingly. Additionally, GP fee income can be added on top of the available distributions and entered as income for the GP.


All the logic is contained on a single tab, making it easy to port into different workbooks without causing reference issues.


Model also included in the all access template bundle.