Preferred Return Model in Excel - Up to 10 Years

This joint venture cash flow waterfall template shows the resulting distributions of an investor and sponsor based upon an investor preferred return. All the user has to input in is the project contributions (equity requirements) and distributions per year and everything flows from that.

$45.00 USD

The template will be immediately available to download after purchase. Note, this template is included in the real estate and joint venture bundles. Version 1 and 2 included in purchase above.

Version 1

Version 2 (Extra Profit Hurdle triggered by Full Equity Repayment)

preferred return model

More on Updates

In the updated version above, you will see I added a yes/no toggle so you can easily see the logic adding any unpaid pref. returns from years where there was a shortfall. This automatically flows to an accrual fund that is kept track of. Once there are enough cash flows available, the accrual fund gets paid out to investors prior to any profit sharing. Also, when that happens, the logic allows you to decide if you want the unpaid pref. to compound onto the balance so that the preferred return is calculated based on the total equity plus unpaid preferred returns as of the end of the previous year. Once the unpaid pref. is fully repaid, the equity balance drops back down accordingly.

You can also toggle to have the year completely re-set so that shortfalls don't get accounted for at all and the preferred return won't accrue either.

I have recently added an equity multiple output metric.

The model goes up to a period of 10 years. You can account for cash outflows happening beyond year 0 as well as distributions happening at the same time.

In general, the model starts by determining the preferred return value for the investor based upon their total equity contributions as of a given year. One option I added is the ability to determine if you want any shortfalls (if the total distributions available are less than the investor preferred return amount) to be added to the investor equity.

After this preferred return is calculated, there is an amount of cash remaining to be distributed to the sponsor and investor based upon their profit share. Sometimes the profit share may not be pari passu (share-based on relative contributions) and so I added a second set of inputs to manually determine the profit share to the investor and sponsor. Those percentages apply to the remaining cash and define the final cash flows added to each party.

There is also a set of inputs that define the % of project-level contributions coming from the investor vs. the sponsor. That is typically used as the profit-sharing basis of cash flows after the preferred return, but I added a secondary reference for that in case it is something different than pari passu.

Finally, I added a cumulative cash position so each party can see when they break even as well as each party's final IRR (internal rate of return). There are two visuals that go along with the data. They are the cash flow to each party per year and the cumulative cash flow of each party.

In general, the point of a preferred return venture is to give the investor some degree of confidence that they earn a certain return value before the sponsor is able to participate in profit sharing. This is typically done because the investor is putting up a lot more capital.

So, to summarize, you will:
  • Enter project level contributions/distributions.
  • Enter Equity contribution percentages for sponsor/investor
  • Enter post preferred return percentages for sponsor/investor
  • Enter preferred return
  • Cash flows then populate.
Deep Dive Into the Preferred Return Excel Template and All Scenarios:

Special Note:

I do have a few different custom preferred return models with a GP catch-up. One is based on catching up to x% of the total profit the LP has received and the other is based on catching up to x% of the total distributions. They are a more specific and complex, but if you want to have a call and review them to see if they are right for your situation, just let me know: and price is per request.