Preferred Return Template – Options for Shortfall Recovery and Compounding or Simple

Video Tutorial:

Updates:
– Added option to reduce LP equity basis with distributions above the preferred return (yes/no selector)
– Added a DCF Analysis for each participant (including NPV).

This template was designed with private equity in mind. Typically, they have deals with investors, and typically, the investor would be a limited partner group, and the general partner would be a private equity firm. However, any two parties can come together in a joint venture and set up a deal. What we have here is something designed to show what happens if the investor side (limited partner(s) has a preferred return that must be paid to them prior to any profit-sharing with the general partner.

The waterfall goes > preferred return > profit share. The profit share variables are able to be defined, and you can set them as pari passu or some other split that both parties agree upon. This template has the option to toggle the carry forward of any preferred return shortfalls, and they become a priority to pay down with future cash flows prior to further profit splits. If you choose, you can set it so any shortfalls are wiped clean each year.

Also, it is possible to toggle the logic that calculates the preferred return. It can either target the compounding balance (equity plus shortfalls) or just the investor equity.

Two visuals were added. One shows the yearly cash flows for each part, and the other shows the cumulative cash flows.

The IRR, equity multiple, and cumulative cash position were also included. The cumulative cash position allows for both the general partner and limited partner to see when they have been paid back their entire contribution as well as how much they receive beyond that.

The cash flows are all on an annual basis and go for up to a 10-year period. It would not be hard to drag the formulas over to go as many years as you want.