Waterfall automation: The next frontier in investment management

It’s no secret that most investments are still managed using spreadsheets. Capital calls are calculated on spreadsheets, fundraising lists are maintained on spreadsheets, portfolio monitoring is done on spreadsheets…virtually every aspect of the business has an associated spreadsheet.

Among this jungle of spreadsheets, one of the most complex is the investment waterfall. In fact, these spreadsheets are typically so complex and challenging to use that most companies only have a handful of staff with the expertise to build and operate them.

Juniper Square recently concluded a months-long effort to automate the calculation of the investment waterfall within our software. As part of the R&D, we reviewed hundreds of waterfall spreadsheets and associated operating agreements, and learned a few things along the way.

We sat with Juniper Square’s Director of Product Operations Lyndsay Erickson (who led our waterfall feature development) to discuss the trouble with spreadsheets and the promise of modern solutions.

Waterfalls specify the priority in which investment proceeds (e.g., income and profits) are paid out to the GP and LPs, with the goal of aligning GP-LP interests.

While waterfalls come in countless structures, and nowhere is this more true than in real estate, here’s an example of a common arrangement:

• LPs have the first claim on any proceeds from the investment until they reach a certain return. This is known as “preferred return,” and the return will vary based on product type and risk, but for a typical real estate investment might be in the 6%-10% range. This is often the first hurdle, or tier, a manager must exceed before getting to claim any profit for themselves.
• Next, LPs will have the first claim on any distributions until they get all of their invested capital returned.
• Lastly, any distribution in excess of the above preferred return and return of capital gets split between the GP and LPs, with the LPs typically taking the majority of profits and GPs a minority. The split is commonly around 20% to the GP and 80% to the LP, but it could be as high as 40% to the GP and 60% to the LP, or even 50%/50% in exceptional circumstances. The share that goes to the GP is commonly referred to as the promoted interest, or “promote.”

Those familiar with the typical “2 and 20” model of the hedge fund world, where managers start splitting any profits immediately, will note a unique order of operations for real estate distributions. Real estate LPs usually expect a preferred return before GPs receive any profit splits. Consider this preferred return akin to interest earned in a bank account. Since real estate is most often a cash-flowing asset, investors expect a baseline yield to be paid before any splits can be contemplated.

It comes down to four main issues:

First, waterfalls can be extremely complex to model in Excel, particularly as you layer on tiers and calculate things like catch-ups that rely on multiple assumptions and circular logic.

Part of that complexity stems from the fact that the people entrusted with managing the investment waterfall are often not the same people charged with dreaming up its structure. One rule of thumb with waterfalls is that the only limit to their complexity is an attorney’s imagination!

Sometimes the calculations were just wrong (thankfully, not too often), sometimes they were incomplete, and often the full complexity of the waterfall couldn’t be captured in one spreadsheet so managers would resort to manually plugging numbers into their models based on one-off side calculations, which inherently increases the risk of errors.

And that leads to the second issue: Excel models have this unique combination of being both highly error-prone (it’s not hard to fat-finger the wrong number into a cell) and also highly difficult to audit, which causes mistakes to go unnoticed and compound over time.

Errors run the gamut: hard-coding values that shouldn’t be hard-coded, linking to the wrong cells, neglecting to expand formulas across a row or down a column.

Further, as models get handed off from one team member to another, we commonly find inconsistencies across calculations within the same investment over time. For example, one analyst might use a daily compounding frequency for IRR, then when passed to the next analyst, that assumption changes to monthly or quarterly.

In isolation, these may seem like small inconsistencies, but they have a rippling effect. If one date is off in a model, it throws off the balance in every subsequent period.

Third, Excel is also not ideal because it doesn’t scale. It’s a fine solution if you have one or two investments, but once you get to 50 investments, you’re in the position of running calculations across 50 different workbooks for every distribution. Now imagine an investor requests a consolidated view of their distributions across all investments. That’s a half-a-day exercise, if you can even pull it off.

Even within one investment, you may have a number of investors that each have different fees, or fee waivers, or side-letter agreements, which become very tedious to model out individually in Excel.

And the fourth issue is the problem of sharing. It’s hard for multiple team members to collaborate in one model. Only one person can be working on it at a time and you have the perennial issue of version control. And, as institutional investors have become more active in monitoring their investments (many like to replicate the waterfall calculation on their side, to ensure they are getting the correct share of profits), there’s no easy way to let investors dive into the calculations behind the waterfall without sharing the entire model, which compromises all of the other investors’ sensitive information.

All in all, it took nine months of R&D. We first studied hundreds of actual waterfall models from our clients, looking for patterns and consistencies that we could use to design a general waterfall framework.

Eventually it became clear to us that some of these waterfalls were not actually modeled to reflect what our clients were describing as their intended distribution methodologies. So at that point, we actually dug into the operating agreements themselves, mapping out the actual waterfall distribution language. That led us to the most common scenarios that would unlock value for as many managers as possible.

Then our product teams got to work modeling hundreds of waterfalls in Excel covering different scenarios. As we worked with our engineers to build out the feature, we engaged our customers throughout the process, showing them demos of beta versions of the feature to collect their feedback. We also tested our customers’ waterfall structures in our software to ensure consistent outputs with their Excel models.

I think the challenge with the past approaches was that software companies tried to build their solutions to accommodate every possible waterfall scenario. The result was software that was highly configurable, but so complex as to be virtually impossible to use. Imagine sitting down in the cockpit of a 747 airliner–that’s how many buttons, knobs, and switches you’d have to configure to get the software to work. Faced with that, and having Excel as a comparably easy-to-use alternative, it’s no surprise that managers just stuck with Excel. The problem was trying to be all things to all people.

We deliberately excluded the highly complex needs of our institutional fund managers from the design of our waterfall solution. Institutional managers will often have waterfalls with complex logic applying not only to the fund, but also to the interactions with individual fund assets, increasing the complexity by orders of magnitude.

Instead, we focused on the needs of the majority of our customers, which, once you take out the outliers, are somewhat more consistent and easier to solve. We had the benefit of having hundreds of customers whose waterfalls we could survey, and so we could take a data-driven approach to ensuring we were addressing the most common waterfall scenarios that would cover 90%+ of our customers.

And we worked hand-in-glove with our customers to develop this feature, as we do all of our new features, so we could be sure that we were truly solving their problems. For the customers we were targeting with this feature, the waterfall was the last spreadsheet to be automated, and doing so meant they could drop spreadsheets entirely and just run all of their investment operations out of Juniper Square.

The most obvious benefit is ease and speed. In Juniper Square, it takes about 30 seconds to configure an entire waterfall! It simply requires pointing and clicking to select rules in the software that are pre-built for common investment waterfall structures. This configuration can be done once, upfront, for the entire life of the investment, and for as many unique investor groups required.

Then there’s the advantages of reliability and accuracy. You have the peace of mind that your waterfalls will be calculated consistently based on your chosen methodology. This eliminates the risk of multiple different firm members changing calculation methodologies over time, or the kinds of fat-fingering errors that throw off balances and cause embarrassing or costly mistakes.

There’s also the reliability gains from consolidating information. Modeling waterfalls in Excel relies on managers physically transferring data across disparate systems and spreadsheets. In a common scenario, a manager will manually extract contribution and distribution amounts and dates from their accounting software, plug those numbers into their Excel models, and then write complex macros or merely manually copy and paste outputs to report out to their investors, most often via PDF. Any time you’re transferring data from one system to another, you’re inherently putting the integrity of your data at risk.

Conversely, when your calculation engine is one software system, that becomes your system of record for the core accounting data used to make these waterfall calculations. And it’s the same system of record used for your investor reporting. So consolidating your information in one place not only offers major time savings, it also dramatically reduces risk of reporting errors.

Additionally, when it comes to auditability, comparing Excel versus a software platform is like night and day. Since Excel models are subject to so much manual manipulation, the only way to truly audit your calculations is to individually click into every cell in a workbook. Conversely, in Juniper Square, there’s no need to audit for common user mistakes like inadvertently hard-coding formulas or referencing the wrong cells. You can trust the calculations because they’re automated in the software code. And beyond that, Juniper Square even provides a full accounting of how the calculations are working.

Lastly, there’s the benefits of sharing and collaboration. Many different team members can be working on different waterfalls at once, without colliding with each other, increasing overall team productivity.

As we worked with our customers to develop this feature, we got clear feedback that customers did not want the calculation engine to be a “black box.” Managers were saying “I’m nervous about plugging inputs into a system and having that system spit numbers back at me that I’m supposed to trust without being able to verify the results.”

It’s understandable that managers are cautious about something as important as distribution amounts. But I think the larger fear probably reflects the fact that the existing solutions aren’t offering much transparency into the calculations.

So to address the “black box” concern, we purposely built a number of self-audit capabilities, including a calculation results page that essentially mirrors all of the detail you’d see behind an Excel model, allowing you to track calculations for each tier over time.

Beyond that, here’s a little insight into the QA process. In building the feature, we concurrently used our hundreds of Excel-modeled waterfalls covering a wide variety of scenarios to create test code that our engineers could run to test that the software is working properly. In fact, remarkably, the test code base for our waterfall feature is actually bigger than the code base used to design the entire feature itself!

If there’s one thing we learned in the development process, it’s that if it’s too good to be true, it probably is! When reviewing vendor solutions, ask probing questions about the software’s ability to support the particularities of your waterfall structure, and how you can audit the results.

And of course, if you’re going to move out of Excel, ensure the solution you choose will actually save you time.

Lastly, the best way to underwrite whether the software really works is to check references. Ensure that other firms have successfully been able to fully automate their waterfall calculations using the software. There’s no better way to know whether a software system truly works than to ask those who already use it.

Lyndsay Erickson is Director of Product Operations at Juniper Square. Prior to joining Juniper Square, she spent four years at Spear Street Capital on the investing and asset management team. Prior to Spear Street Capital, Lyndsay worked at Morgan Stanley in the Investment Banking Division and for Morgan Stanley’s Real Estate Investing group in New York City. She graduated from Stanford with a B.S. in Mathematics and M.S. in Management, Science and Engineering.