Preferred Equity - Waterfall Excel Model

Description
Model Updates:

 - Added the option to accrue but not compound unpaid preferred returns (shortfalls) from previous periods. Previously you could either have unpaid returns accrue or not and if they did, they would automatically add to the equity basis thereby compounding the unpaid returns. Now there is an option within the accrual selection.

 - Added a Discounted Cash Flow Analysis for each participant (including net present value).

Preferred equity is different than a preferred return. A preferred return is commonly associated with preferred equity, but not always. I have done preferred return templates in the past, but never one that has a separate group for preferred equity and common equity along with a sponsor. The non preferred equity parties have waterfall logic for them that involves IRR hurdles and promotes.

This template will let you define the amount of equity that is 'preferred' and you can defined a preferred return on that equity as well as if there is an extra equity kicker attached to that preferred equity. What that means is the model ensures the preferred equity is fully re-paid, along with the preferred return (if one is entered) before any other cash is split to the common equity.

You can define a sponsor and investor as secondary common equity or just have a sponsor as the secondary group.

The model goes for up to 10 years and all the user does is drop in the cash requirements per year and cash distributions per year. If you wanted to extend this for more than 10 years, it would just be a matter of dragging all the formulas in the 10th year over as far as you wanted to go.

I included visuals to see all 3 parties (preferred equity, common equity/investor, and the sponsor.

Any cash that is available after the preferred equity has been paid will go through a secondary tab that has a fully integrated IRR hurdle-based waterfall model that splits between an investor and sponsor.

This model should be dynamic enough to handle nearly any kind of sophisticated cash distribution scheme that is common in the realm of preferred equity and joint venture cash distribution schemes. This sort of model is common in real estate joint ventures, but may be involved in other kinds of business ventures as well.

There is IRR and equity multiple results for each group as well.

This template is also included in three bundles:
- All Models Bundle: https://www.eloquens.com/tool/P8Y4TX4v/finance/financial-forecasting-models/financial-models-120-useful-and-usable-logic
- Real Estate: https://www.eloquens.com/tool/vJdYH9R7/finance/real-estate/the-complete-real-estate-bundle
- Joint Venture: https://www.eloquens.com/tool/MQJ8h73v/finance/joint-ventures/joint-venture-template-bundle