This model is used to calculate preferred equity. Preferred equity investors have a claim on the equity put into a venture, forgoing a larger potential overall return for low risk, consistent cash flow. Typically, the preferred equity payments are made before any other investors, or equity, are paid. This model allows you to toggle many factors and values to see different payout set-ups, i.e., allowing users to look at potential cash flow scenarios given different funding structures. This model was built to account for complex and simple funding structures.
Special Features - Subordinate IRR Hurdle Equity Waterfall:
This model includes an equity waterfall combined with the preferred equity model. The preferred logic sits on top and then remaining cash flows are used to calculate distribution of remaining funds. Users can split these in a variety of ways, with or without hurdles, defining how much is split between the investors, sponsor groups, and common equity groups. The final summary shows each group and their total cash flows, accounting for distributions and contributions each year.
If you don't wish to use the top layer preferred equity, that can be zeroed out and the general 3 hurdle IRR hurdle waterfall can be used for all the cash flows.
Also, the top layer preferred equity can have a kicker percentage defined that gives more upside to the pref. beyond the preferred rate of return.
Visuals are included for the final cash flow summary of each leg.
Note: I am not a financial advisor and this is not financial advice. Use this template at your own risk.