Preferred Return Model in Excel - Up to 10 Years

This waterfall model shows the resulting cash flows of an investor and sponsor based upon a preferred return existing for the investor. All the user has to input is the project contributions (equity requirements) and distributions per year and everything flows from that.




Pricing
You will be sent the template after purchase.

Update (adding logic to account for pref. shortfalls and compounding vs. simple accrual)



The Update
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.

I have recently added an equity multiple.

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 come 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.