Common Errors in Waterfall Calculations - Understanding Compounding in Excel

Compounding in Excel

One of the leading sources of error in the private equity world is waterfall calculations. Waterfall calculations are governed by dense legal documents, and the calculations often differ from traditional finance methods. One of the most common instances of this is the compounding. When most finance professionals think of compounding, they think of an accruing interest balance that will be added to the principal amount, usually on a monthly, quarterly, or annual basis. With waterfalls, the IRR based calculation requires a different method of compounding – one that can drastically change projected distributions if not properly implemented. In the below article, we will discuss how to identify these compounding requirements, and how to properly implement in a model.

Most waterfall legal documents require that waterfall distribution models truly include two types of compounding – which often confuses legal and financial professionals alike. Most legal documents state the IRR or preferred return as an annualized rate. Most of these same documents also specify that capital contributions and distributions are considered on a daily or monthly basis, and we know that almost all capital calls and distributions do not occur annually. This leads to confusion between legal and financial professionals alike when computing IRR and preferred return balances that fall outside of exact annual increments. How do we ensure that we are accurately applying annual return metrics to non-annual cashflows? There are two main concepts you must understand to accurately calculate IRR and preferred return balances for non-annual increments: Model Frequency and Hurdle Rate Compounding.

Model Frequency:

This is the minimum time period a waterfall distribution model must be able to calculate IRR or preferred return balances for. In the definition of Capital Call or Distribution, most Limited Partnership Agreements (“LPA”)  will specify when the capital activity is deemed to have been made. The most common are:

  • on the actual day of activity – that is when the capital call or distribution is funded; or

  • as of the first or last day in the month the capital call or distribution happened.

The waterfall model frequency will determine the minimum increment of time you have to calculate preferred return for. For example, if you treat capital calls and distributions as of their actual days, you could conceivably have to pay one day of preferred return if a capital call were made today, and a distribution made tomorrow. In this case, the minimum increment of time is 1 day.

Likewise, if you have all capital calls and distributions deemed on the first day of the month, the minimum time period you will have to calculate preferred return is 1 month. There is no way you could only pay 1 day – or 15 or 20 or 25-days’ worth of preferred return.

Model frequency matters because we must compound our annual preferred rate of return within the year, according to the model frequency. This is so that we can recognize any time period’s worth of preferred return accurately. Please note that this does not mean we are providing the Limited Partners with a daily or monthly preferred return, but rather taking the annualized preferred rate of return and applying it according to the LPA’s frequency. If we have an LPA that allows for 1 day of preferred return as discussed above, we need to adjust the annual rate of return to recognize that single day of preferred return.

To achieve this, we generally use the following formula in Excel:

(1+RATE)^(#Days/365)

This formula will allow us to recognize an annualized rate of return (represented by the RATE function) on non-annual time periods (represented by the #Days)

Example:

Let’s find preferred return on a $1MM capital contribution made on 1/1/2021 and distributed on 7/1/2021.

One year of preferred return on $1MM at 10% is obviously $100k, so will 6 months of preferred return be roughly $50k? To be more exact, there are 181 days between 1/1/2021 and 7/1/2021, which equals 49.58% of the year. Using simple interest style of calculation, we would have a preferred return balance of $49,589, calculated by taking $100k * 49.58%. Let’s check the cashflows using the XIRR function in excel:

Eric Bergin