Understanding and overcoming challenges in waterfall model fund calculations.

Summary
For fund administrators seeking an alternative to manual (or Excel-driven) waterfall calculations, automating waterfall calculations at scale across funds using a scalable Waterfall Calculation Engine provides a host of high-level benefits, including

• Improved alignment of GPs and LPs
• Increased transparency and controls for investors and regulators
• Automated complex allocations and calculations
• Reduced operational expenses

Understanding Waterfall Calculation Complexity
Distribution waterfalls define the financial relationship between the fund (GP) and the investor (LP). The waterfall pulls everything together into an agreement that completes and connects the profit cycle. For this reason, the waterfall is, perhaps, one of the most important elements in fund accounting.

There are many types of waterfalls. Here’s a common real estate scenario:

• An LP has the first or “preferred” claim on proceeds until a certain return is reached
• LPs have first claim on distributions until they see a return of all invested capital
• Any distribution above “preferred return” and return of capital is split 80%-20% between GPs and LPs

At face value, the model seems simple enough and easy to create in Excel. If you have three or four investments and access to a junior business analyst fueled by bottomless energy drinks, Excel works perfectly fine.

Waterfall Calculation Challenges
However, when you add a few more funds and need to run calculations weekly across 20 or 30 workbooks for every scenario and provide every investor a consolidated view of all their distributions across all their investments, with different scenarios, side letter agreements, fee waivers, and more, it becomes tedious – if not impossible to model accurately (and on a timely basis).

This is when waterfalls really become complex. They take a life of their own with a wide variety of custom, and/or conditional structures containing tiers, catch-ups, if/thens, and other logic.

The waterfall calculations in different funds are (obviously) usually different. In some cases, different investors within the same fund may use different formulas as some investors may receive more different or preferential treatment.

All these layers of complexity – at scale – can create challenges for CFOs and fund administrators. This lack of standardization means that automation is a problem. COTS automation solutions simply lack the customization features to model the many complex details of an entire waterfall. Plug-and-play simply adds more complexity to the equation.

Building your own custom waterfall modeling solution underestimates the level of effort needed to maintain a custom application. This is why modeling waterfalls often defaults back to Excel – regardless of the risks (e.g., not-scalable, complexity, human error, no audit trail).

Excel Does Not Make The Problems Go Away
Excel does not make the problem go away (it just prolongs it). The importance of clearly communicating the terms of a partnership and ensuring that returns to GPs and LPs are consistent and correctly calculated in accordance with investor agreements has never been more important.

CFOs and Fund Managers need a configurable, automated solution that can cope with dynamic calculations for different workflows—and cash flows—in order to create a risk-averse process and eliminate the need for cumbersome spreadsheets.

Waterfalls prioritize how proceeds are paid to the GP and LPs, with the goal of aligning GP-LP interests.

XLS creates so many problems:

• Attorneys dream up a deal structure and then Excel must model it. As you layer in tiers of logic such as catch-ups and other assumptions, often the full legal complexity is impossible to model in Excel. This forces your XLS developers to manually input values or calculations which creates the risk of downstream errors.
• This leads to the additional issue of Excel models being error-prone due to fat-fingers or simply due to the fact that hard-coded values are overlooked later, formulas aren’t expanded later, and on and on.
• Human nature means errors get passed from one person to another with inconsistencies in calculations or assumptions within the same investment
• Excel is not a collaborative tool and it is not a scalable solution. Only one person can really work on it at a time AND SO this creates version control issues
• You can’t share the model because there is no easy to way to let investors review the calculations without exposing other investors’ information
• There is a big risk when files are so complex that most firms generally have only a few people that know how to build and operate these beasts

With funds facing so much scrutiny and with the fundraising market becoming so much more competitive, there must be a better way…

Introducing The Transparent Waterfall Calculation Engine From Thoughtfocus
ThoughtFocus has helped some of the world’s leading investors and private equity firms automate their waterfall calculations.

The Waterfall Calculation Engine is a transparent calculation engine. It can automate your waterfall calculation processing, generate multiple forecasts to evaluate investment scenario outcomes, and seamlessly integrates with Investran, Anaplan, and other platforms.

The ThoughtFocus Waterfall Calculation Engine supports American, European, Doomsday, Capital Claw, and other scenarios and has helped some of the world’s top private equity firms increase transparency and controls for investors and regulators while reducing operational expenses and improving returns.

Complete Waterfall Calculation Engine

• Supports cash, liquidation, and future scenarios
• IRR and Net Asset Value Calculations
• Configurable at fund, investor, and investment levels with loss and carry-forward allocations
• Side pocket, carried interest, clawbacks, incentives, and allocations
• Unlike black box solutions, the Waterfall Calculation Engine allows validation of all information, scenario modeling, fund-splits, doomsdays, and more
• Advanced reporting and data analytics support in-depth analysis and ad-hoc queries
• Improve tracking and reporting waterfall calculations at the partner and investment levels

For fund managers or CFOs seeking improved waterfall reporting at scale, ThoughtFocus can help. Reach out to our team of financial and technology solution experts at betterfuturefaster@thoughtfocus.com for more information and we’ll get back to you at the earliest.