Preferred Return Calculator - Simple Interest with 3 Hurdles

Description
This joint venture cash flow waterfall template separates the return of equity and the preferred interest earned by the LP (Investor).  There are 10 periods shown, but if you have more, simply drage the last column of formulas over as long as you want. Also, if the periods are not annual, simple divide the annual return rates by the number of periods in a year. If you are modeling monthly periods, divide the rate by 12, if quarterly, divide by 4. Since it is non-compounding, there is no other fancy math required.

Note, these hurdles are not IRR hurdles. Also, there is no compounding of interest. The preferred return due will accrue if unpaid each year, but the equity basis is just going to rely on any contributed or distributed equity (non-compounding). 

There is a big difference between preferred return and preferred equity. A preferred return is just defining a claim on the return earned over time (not counting the return of capital). No matter which way you slice it, the deal will only have so much cash to distribute.

In this model, you can define up to three simple interest hurdles for the LP. As the total interest received by the LP rises, the amount of cash going to the GP (sponsor/operator) can rise. This is considered a promotion of the cash flow share.  The idea is to incentivize the GP to get better returns for the LP in exchange for high shares of cash flow as the LP return grows (access to upside).

Since the equity contributions/distribution logic is separate, the user can define if/how equity is returned and the cash available afterwards will flow through the hurdles and automatically split between the LP and GP in each period based on the total interest received by the LP. The user will enter available cash flow to distribution in each period.

Note, the way the logic works is by tracking the total interest accrued over time at each of the defined interest rates. Once a given interest rate has been achieved, meaning the total accrued preferred return due is met at one of the hurdle rates, the split engages until the total accrued interest that is due if the rate had been higher for the entire time is paid. Once that is satisfied, the next hurdle is calculated and if there is any cash after that, you can split the available distributions with one final split rate.

The final IRR will be shown at exit for the LP and GP. If the GP did not contribute funds, they have no IRR.


Remember, there are a lot of ways to strcuture these types of deals. This is just one way, not the only way.

This template is also included in two bundles:
- All Models Bundle: https://www.eloquens.com/tool/P8Y4TX4v/finance/financial-forecasting-models/financial-models-120-useful-and-usable-logic
- Joint Venture Waterfalls: https://www.eloquens.com/tool/MQJ8h73v/finance/joint-ventures/joint-venture-template-bundle