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:

As you can see, the XIRR is higher than the necessary 10.00%.

Now lets try using the (1+RATE)^(#Days/365) formula. Using this formula, the preferred return balance is $48,397, calculated by taking [(1+10%)^(181/365)*1,000,000-1,000,000 = $48,397]. Let check these cashflow in excel using the XIRR formula.

As you can see, we get to the exact target rate of 10.00%. This is because we compound within the year, so we can recognize the annual rate of return on any given day.

Hurdle Rate Compounding:

The next crucial step to understanding preferred return and IRR calculations in waterfall distributions is understanding hurdle rate compounding, specifically as it relates to nominal and effective rates of interest. It is important to note that compounding within the year as described in the Model Frequency section, and hurdle rate compounding described here are two very different mechanisms. Hurdle rate compounding deals solely with converting a nominal target annual rate of return to an effective target rate of return.

LPAs will commonly state a rate of return that LPs are to receive, and then specify the compounding frequency for that rate of return. When this is the case, it is important to understand that the first rate of return is nominal, and you must use the compounding periods to find the effective rate of return.

Take the below language for example:

The above language stipulates that the Limited Partners are to receive an 8% rate of return, compounded monthly. This means that to find the Limited Partners true rate of return, we must convert the 8% nominal rate into an effective rate of return. The easiest way to do this is using the below excel function:

=EFFECT(Nominal Rate,# of Compounding Periods)

In the above example, the nominal rate of 8% converts to an effective rate of 8.3%. This means that we would use the methodologies discussed in the Model Frequency section to ensure that the LPs receive an 8.3% effective rate of return, regardless of when capital contributions and distributions were made.

In conclusion, when an LPA specifies compounding periods, the only effect is to the target effective rate of return. Any specific compounding periods will increase the effective rate of return the LPs reach on their capital contributions. In order to achieve this effective rate of return, we must compound according to the minimum time frequency specified in the LPA. If the capital contributions and distributions are treated daily, we must compound daily. This will allow us to reach this effective rate of return on any day of the year. If the contributions and distributions are treated monthly, then we will compound monthly, and so on. This does not mean that the effective rate of return that the LPs receive is a daily compounded rate, but rather we can recognize annual or monthly compounding on daily basis.

Before founding 3E in 2016, Managing Member Eric Bergin was Director at Rockpoint Group, where he was responsible for for the Finance Group, as well as acquisitions, asset management, and investor reporting activities.