Being able to model an idea requires a really good understanding of what it is being modeled. With preferred equity, we must understand a few different things in order to even attempt to build something that is generally useful to LPs and/or GPs as they try to figure out a feasible deal structure for their joint venture.
Preferred equity in the context of a real estate or oil/gas syndication deal means there is a claim on equity of some sort. The way I modeled the logic in this preferred equity waterfall template was that 100% of the initial investment must be fully repaid plus the preferred return due. After that, the remaining cash flow flows to common equity. Sometimes there may be a preferred equity kicker that allows the LP to earn some percentage of the upside of cash distributions after all other preferred equity requirements have been satisfied.
The preferred return is usually an annual rate that is applied to the beginning balance of equity. What I give the option to do in my model is to accrue any preferred return shortfalls and add that to the basis for the preferred return due in the next period (compounding). If 'no' is selected on this part, then the preferred return will simply accrue, but not compound and so the interest owed per period will not go up, however whenever there are enough cash flows, the unpaid returns will be repaid.
Also, I considered if there is a reset each period or not. If 'yes', then that means any unpaid returns reset and don't ever get repaid. If there is more cash flow than needed for the preferred return due, it will reduce the equity balance, but if there is not enough, whatever is available is paid to the LP and nothing else changes for the basis of the next period.
Just modeling the above is tough. You have to track how much of the initial equity has been repaid over time and the resulting equity balance based on these repayments. The goal / target of the first part of this logic is to use any excess cash flow each period in paying down the equity that has been invested after any preferred returns have been satisfied.
The equity balance needs to change depending on the options selected for the deal (as far as compounding or not and resetting each period or not) and all of this needs to work based on arbitrarily different cash invested and cash available to pay out in the deal.
I would say the most difficult thing to do in this model is the interest accruals and determining, without having any circular references, what is available to distribute vs. what gets used to pay down any unpaid returns. Allowing that to work dynamically took a long time for me to structure properly.
An interesting aspect of the model I built for this is a secondary cash flow waterfall that sits right under the preferred equity leg. It is a common equity waterfall based on IRR hurdles and a secondary LP/GP distributions schedule. What happens with that is any remaining cash flows that are available after the preferred equity has been fully repaid will go to a secondary waterfall. The initial contributions are split between the initial preferred equity leg, the LP of the common equity leg, and the GP of the common equity leg. In this sense, you kind of have a mezzanine debt structure that sits on top of a promote waterfall for the rest of the parties involved.
More Joint Venture Waterfall Templates:
