Preferred Equity Model - Up to 10 Years - Includes Common Equity Waterfall

In the last series of preferred return templates, I said I was going to do a preferred equity version. Well, I have done so and this is it. There is common equity that sits below the preferred. I think the logic here is super cool as well as useful. It lets the user look at possible cash flows given various project funding structures.




Pricing
The template will be sent to you after payment.

Note, in the video I had a DIV error when I plugged in 100% for the pref. equity and 0% for the sponsor/investor common equity. That scenario is now possible. Anything that remains after the pref. cash distribution goes to the sponsor. Also, if you look at the video, you will see if you put contributions into year 1, 2, etc..., the % for the pref. is going off the % for common. That has now been updated to the right reference cell.

There is a lot of functionality with this model. It is built to do a lot of complex things, but also handle more simple funding structures.

Why do some investors like to do preferred equity deals?

The reason why is because of risk vs. return. Preferred equity (in most cases) means that all cash flows available will go to pay back the preferred equity amount before any other cash is distributed. You can have a preferred return on the amount of preferred equity and in that case (as you will see in the video above) the cash amount is calculated for that first and any remaining cash goes to pay back the equity.

You can also have an equity kicker or profit kicker on the preferred equity side of things. In that case, after the equity has all been paid back, the preferred leg still gets a share of profits and that share is usually smaller.

The above logic means that preferred equity investors give up some of the potential upside for more confidence that they will at least get their money back and possibly a defined annual return before anyone else in the stack gets paid a penny.

You can see this play out in the model where you have a 10-year scenario with a larger cash infusion after year 4 and then a larger exit in year 10. By year 4, the preferred equity is paid up and then the remaining cash is split to the investor/sponsor common equity. That may or may not be a greater return than the preferred equity as it just depends on the final results of the deal and what the exit value is.

This model allows for zeroing out of anything and it will still work. You can remove the preferred equity and just have the waterfall logic with IRR hurdles between the common equity or you can have just the preferred equity with no contributions from common equity investors or general partners. The general partners would then receive anything after the preferred equity kicker.

You could have just the preferred equity and a sponsor with no common equity limited partner.

You could have only one of the three legs.

I added visuals for each entity / pool that shows annual cash flow as well as cumulative cash flow. IRR and equity multiple metrics are also displayed per pool.

There does not have to be a preferred return, meaning the cash flow may just be 100% to pref. equity and when that is fully paid back the remaining is split based on a defined kicker the pref. gets.

There is logic to allow any unpaid preferred returns to accrue onto the equity balance and that will raise the pref. return amount as well as the total equity that must be paid back before anyone else receives cash.

*Note, this model is really easy to extent for more years if you want. All you have to do is go the the last year and drag those columns over as long as you want.