Let’s say you have an existing waterfall model in Excel that is built out to support 5 tiers (hurdles). The equity JV waterfall structure is cumulative and compounding in nature, and provides for the following:
- first (Tier 1), distribution of an 8% look-back IRR-based annual preferred return, measured off of the Investor’s cash flows, split pro-rata to how dollars were invested. In this case, 90% to investor and 10% to sponsor.
- second (Tier 2), a 5% promote to the sponsor above an 8% investor IRR through a 12% investor IRR
- third (Tier 3), a 10% promote to the sponsor above a 12% investor IRR through an 18% investor IRR
- fourth (Tier 4), a 20% promote to the sponsor above an 18% investor IRR through a 25% investor IRR
- last (Tier 5), a 40% promote to the sponsor for all cash flows above a 25% investor IRR.
The inputs (formatted in bold blue font) for the above-described structure may be set up in Excel something like this:
Can you use this spreadsheet set-up and the accompanying waterfall calculation lines as is on a preliminary basis to model a waterfall structure with fewer than 5 tiers (assuming all else equal), or do you need to first delete out the extraneous tiers both here and in the waterfall itself?
Luckily, the answer is you can use what you’ve got. Let’s assume the structure is as follows:
- first (Tier 1), distribution of a 10% look-back IRR-based annual preferred return, measured off of the Investor’s cash flows, split pro-rata to how dollars were invested. In this case, 90% to investor and 10% to sponsor.
- second (Tier 2), a 20% promote to the sponsor above a 10% investor IRR through a 20% investor IRR
- last (Tier 3), a 45% promote to the sponsor for all cash flows above a 20% investor IRR.
Here’s how your inputs should look:
The inputs for the now-extraneous tiers (boxed in red — the originally named Tiers 3 and 4) should match the inputs for Tier 2 (boxed in orange). The originally-named Tier 5 (which is still displaying labeled as Tier 5 above) is now actually Tier 3 because the originally named Tiers 3 and 4 are essentially leap-frogged in the calculation of the waterfall given the hurdles are based on a look-back logic. You will eventually update the labeling of Tier 5 such that it is labeled Tier 3, hide the columns for the extraneous tiers in the table above, and hide the rows for the extraneous tiers in the waterfall calculation lines.
Valuate removes all the work and potential for human error from this process by allowing effortless changing of the number of tiers.
In this way, Valuate is the perfect tool for quickly comparing JV returns based on different structures.
If you’re interested in learning more about single transaction equity JV waterfalls and the guts of the look-back method, buy our Level 3 Bootcamp self-study product here. If you want an in-house training or private coaching with these types of lessons and more, email [email protected].
