Recently updated with a Key for primary input cells.
This model is designed to plug into any financial model where you know the cash contributions and distributions per year and per preferred equity vs. common equity. The model goes out for a period of 10 years and can easily be drug out as many years as you want or have less years.
Preferred equity is different than just having a preferred return sit on top of an IRR hurdle waterfall Preferred equity has a claim on equity and means any cash flows available will go to draw down that preferred position until it is 0 prior to anyone else receiving a dime. There is often a preferred return associated with preferred equity, but there doesn’t have to be. If there is not, then there is going to be an equity kicker (share of profits) which is usually smaller. It is smaller because the position has less risk since the equity is paid back with priority.
If you do enter input for the preferred return, you can choose to have unpaid past returns accrue to the preferred equity position or not.
After the preferred equity has been satisfied, any remaining cash flow will go to a secondary waterfall, which was designed to be split based on IRR hurdles as you would often see between an investor and sponsor. Having this separate from preferred equity adds to the flexibility and amount of use cases the model can work with as well as providing maximum financial strategy.
There are annual summaries for all 3 parties, including yearly cash flow (visual plus values), cumulative cash position (visual plus values), and IRR as well as equity multiple.