$45.00 USD
Latest Template Updates:
- Added the ability for unpaid returns to accrue AND not compound. It also has the option to compound unpaid returns.
- Added a DCF Analysis to this for each participant of the deal.
Update (added an option to not compound unpaid returns)
Update (added a key and some ease of life formulas / text for more clarity)
More Joint Venture Cash Flow Waterfall Templates:
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 extend 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.
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 extend 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.
Questions and Answers From Customers Who Purchased/Inquired About This Template:
Customer Question:
I have a question as to why it calls out “Preferred Return Due” on the common equity tab in the distributions. Can you please advise.
Answer:
The "preferred return due" is just a calculation to figure out the amount of the return that must be paid that period to cover the 'interest' if you want to think of it like that. Any 'interest' that is not paid in that period automatically compounds in the common equity waterfall to the 'balance' in order to know how much is due so the IRR hurdle is met. This is just a compounding return.
So, on that 'preferred return due' row, you will see it is referenced in tier 1 (or whatever tier you are on) in order to know the actual distribution calculation amount. The lower of the preferred return due + any outstanding balance (equity basis used for calculating the pref. return each period) or the amount available to distribute is used to populate the actual distribution.
The waterfall then stops distributions in a given tier once the balance in that tier goes to 0 (meaning there is no more balance as a basis for returns and the given IRR threshold has been achieved).
As you move down the tiers (tier 2 / 3) the 'Preferred Return Due' is calculated based on the IRR threshold for that tier and the equity basis is measured against all contributions less distributions to that point from all above tiers. The amount available to distribute after all tiers above have been satisfied is measured against the total balance due plus any distributions from previous tiers to figure out how much should be distributed in that period for that tier.
The model will always solve for the exact amount of cash to distribute in order to achieve a given IRR, depending on what the defined hurdle rates are. It sort of works like a layer cake and the final layer shows all distributions in total (the bottom summary).
More Joint Venture Cash Flow Waterfall Templates:
- Preferred Return
- Preferred Equity (equity multiple hurdle)
- General Cash Flow Waterfall: 3 IRR Hurdles
- General Cash Flow Waterfall: 3 IRR Hurdles and GP Catch-up Provision
- Preferred Return - Simple Interest / Multiple Hurdles / Non-Compounding
- Preferred Return Fund Tracker
- Cumulative Distributions
- Mixed-Use Real Estate
- Multi-family Real Estate Acquisition
- Joint Venture Model - IRR Hurdles / Waterfall
- Hedge Fund Fee Model
- Self Storage: Multi-Fund
