I've worked on a lot of models and templates that contained cash flow waterfalls with multiple IRR hurdles, single IRR hurdles, multiple preferred return hurdles with simple interest, single hurdle preferred returns and everything you can think of in-between. There are some commonalities between good waterfalls as far as how you should lay out the formatting so that it is easy for investors to understand as well as anyone that needs to view them.
Check out these templates with IRR Hurdles and preferred returns for actual examples with working formulas. The key to making these work and making them easy to understand is an art. It often requires deep granularity and breaking out every single piece of logic and calculation into its own section / row.
General Best Practice Template Formatting
- Sequential Structure: Organize the waterfall in a sequential manner from top to bottom to reflect the order of cash flows. Start with the initial investment and progress through each level of returns, showing how profits are distributed among stakeholders. Doing it this way makes it easy to check that the cash flows are being split at the proper distributions rate for each level as well as to check if the hurdle was indeed met for the level. You can define the project level cash in and out per period and have a percentage of negative cash flows contributed by the LP and GP. You will have the positive cash flows of the project targeted for figuring out what is available to distribute for the various hurdle levels. It is important to separate the contributions for each party as the basis for the return is going to be on the LP's initial investment, whatever that may be.
- Instructional color formatting: Make sure hard coded input cells are designated with light yellow cells containing blue text and formulas are black text with no color shading in the cell. Also, remove gridlines and insert your own lines for cleaner formatting.
- Summary Totals: Depending on exactly what type of hurdles you are dealing with, it can often make sense to provide subtotal calculations in the far left-hand side of the template that totals the result of each hurdle level. Additionally, it is helpful to make a final summary that shows the cash flows in each hurdle all in one spot at the bottom or top of the spreadsheet. This is because each hurdle level reference and calculation is going to have multiple rows to come up with the actual distribution amount and so it helps to summarize the key output of each distribution level all in one place for the GP and LP so it can be viewed all at one time. This is where the waterfall becomes visible.
- Labeling: Clearly label each section of the waterfall to provide a concise description of its purpose. For example, include labels such as "Investment Capital," "Preferred Return," "Return of Capital," "Promote/Carried Interest," and "Investor Share." The user needs to be able to easily track down the calculations they see and make sense of what it means in their own head. Without proper labeling it can be difficult to digest the numbers.
- Calculation Method: Include a brief explanation of how each level of return is calculated. For example, if there is a preferred return, clearly state the rate or percentage used in the calculation. This transparency will help investors understand the underlying mechanics of the waterfall. If you have seen multiple operating agreements, you will know that the language describing how the preferred return is distributed can be complex and sometimes unique. Attaching specific details can make it easier for the user to follow along with what the formulas and logic are actually doing.
Here's a recent update I did to one of the more popular templates I have for preferred equity deals.
