How to Design Great Waterfall Models:

11 tips and clever Excel tricks that will make your model ironclad and help inspire confidence in your LPs

By Mariya Stefanova, PEAI

Investor scrutiny on carried interest calculation has become rather commonplace in late years, with some of the large US institutional Limited Partners (LPs) prepared to get deep into the weeds and even audit their GPs’ waterfall models themselves, an arrangement usually stipulated in a side letter. In all fairness, that is rather an unpopular approach that only the bravest of LPs would take, as reviewing somebody else’s model (typically in Excel) could be an ungrateful task. However, it marks a trend and it’s a warning sign to GPs that they need to step up their game in building more robust waterfall models.

At our ‘Build-a-waterfall-model’ boot camp, we teach step by step how to design great best-practice waterfall models, so we’ve decided to share some tips and equip you with a few clever Excel tricks that will make your model shine and impress anyone who intends to review it – an LP, the auditors, or any other stakeholder that can potentially be interested in it. Unfortunately, no amount of Excel tricks will help GPs if compliance with the LPA’s waterfall provisions is violated, but a well-built waterfall model can lead to a robust carried interest calculation, and thus it can help the GP build confidence in the LPs’ eyes. So, let’s roll up our sleeves and start preparing waterfall models that inspire confidence in the GP’s ability and willingness to transparently, fairly, and accurately (as close as possible to the LPA’s waterfall provisions) calculate carried interest.

Software providers are trying to convince us that only a system, and not Excel, can be robust. We tend to disagree. Excel is a great product with amazing functionalities if you know how to use its full potential. You can create an ironclad waterfall model in Excel with plenty of controls in place that can beat any system with its flexibility and transparency, not to mention low price (in fact it would probably cost you close to nothing as you would already have it installed on your computers anyway), no additional implementation cost and valuable time associated with learning and implementing a new system. There are mostly upsides, except for perhaps speed, should you have a lot of data in it, but there are ways around this too. Bottom line, due to all these reasons, as well as the ‘black-box’ concerns GPs often have with waterfall software platforms, Excel is usually the product of choice for most GPs, and PEAI is on a quest to help GPs justify the use of this great product, where appropriate, and defy the misconception of late years that only a system can do the job, so hopefully our tips will arm you with ammunition to design a robust reliable Excel waterfall model.

In this technical article, we’ll give you a few tips on:

1. How to design your waterfall model as if it’s a specialist private equity (PE) system
2. How to define your ‘Static Data’ data points to boost the model’s robustness
3. How to design your ‘Flat/Row Data’ in an efficient easily-searchable manner
4. How to create pre-defined drop-down menus to be used in your flat/row data
5. How to create and use data filters to perform efficient reviews of your model
6. How to design interactive graphical Dashboards
7. How to create a Navigation Diagram with hyperlinks to each worksheet for easy navigation
8. How to use conditional formatting for your Error & Validation Checks
9. How to use What-if Analysis Tools (Goal Seek, and Scenario Manager) to quickly calculate Preferred Return and Run Carry Scenarios
10. How to use Data Tables to calculate Realised Carry for Multiple Individual Distributions at the same time
11. How to use some Date Functions to make the model ‘smarter’ and more dynamic

PEAI Publications