This fully dynamic model allows users to explore numerous scenarios by entering data into simple, user friendly, color-coded cells. The model is used to calculate a “soft” preferred equity waterfall, allowing users to define how cash is shared between limited partners/investors and general partners/sponsors. However, unlike common equity investors, preferred equity investors have priority when funds are distributed.
The model includes 3 tiers in which cash is split in different ways. It allows users to structure deals between new and existing partners, a.k.a. joint ventures, and structure deals in a more individualized way, elucidating risk that a limited partner will take on.
This model enables users to enter contributions from partners and annual available cash flow and all logic flows from those inputs. Assumptions for percentage splits in each tier are customizable to fit users’ specific needs, depending on risk/ potential profit existing within the joint venture.
Visuals are included which show cash flow splits over a ten-year period. The ‘summary’ tab contains a discounted cash flow analysis for limited and general partners, as well as the final equity multiple and IRR, taking into consideration the results of all cash flows.
Note: The model is designed to go up to 10 years but it is easily adjusted to accommodate additional years (go to the last year and drag columns over to as long as needed).
Special Features - 3 Tiered Equity Waterfall:
Cash flows logic is determined as a result of the limited partner getting majority share of profits until they have been paid back in full (exact percentages are configurable). Then, the limited partner receives a more even share (share configurable) until a specified equity multiple is achieved. Beyond that, the general partner starts to receive a greater relative share (exact percentages configurable), as compared to the limited partner.
How each tier/ hurdle works within the waterfall:
Tier 1 splits cash at a defined percentage until the limited partner has received all of their equity. Typically, this deal would be somewhere around 70/30 or 80/20 in favor of the limited partner. If users wanted to model a “hard” preferred equity deal, then 100/0 would be entered until all the equity is paid back to the limited partner that was put in.
Tier 2 splits cash at a newly defined percentage until the limited partner have received the defined equity multiple. E.g., if the user inputs 1.75 it means cash is split at the defined rate of this tier until the limited partner has received their entire equity plus achieved a return on investment of 75%.
Tier 3 splits cash at a final defined percentage based on remaining cash following the first two hurdles. At this point, general partners would receive more and limited partners would receive comparatively less.
This model is simple to understand but contains complex logic which makes it a highly useful tool for individuals modelling out different scenarios when starting a joint venture which includes preferred equity.
Note: I am not a financial advisor and this is not financial advice. Use this template at your own risk.