XNPV in Distribution Waterfall Calculation

View fullsize

Whether you are the General Partner or the Limited Partner, an accurate Distribution Waterfall calculation is crucial to the fulfillment of your investment agreement.

What is a Distribution Waterfall?

A Distribution Waterfall is a method of distributing money between the General and Limited Partners involved in an investment. Typically, the waterfall disproportionately allocates distributions to the General Partners after certain returns have been achieved in an effort to incentivize the General Partners to maximize returns. Distribution Waterfalls are governed by the legal documents of the partnership such as Joint Venture Agreements and Limited Partnership Agreements.

What is XNPV?

As a quick refresher, the XNPV function calculates the Net Present Value of a series of cash flows that occur at irregular intervals. For a more detailed recap on the XNPV function check out our other blog, Excel Functions for Private Equity: XNPV.

=XNPV(rate, values, dates)

Rate: the rate at which you would like to discount the values at. Typically, this rate is the pre-calculated Weighted-average Cost of Capital for your project, or the return required for a Distribution Waterfall. Rate is a required parameter.

Values: a series of cash flows that corresponds to a schedule of payments in dates. Cash flows are discounted based on a 365-day year. Investments or cash outflows are represented by negative values, while income or cash inflows are represented by positive values. Values is a required parameter.

Dates: a schedule of dates that corresponds to the cash flow values. The first date in the series must correspond to the beginning of the series of cash flows. Dates is a required parameter.

Operating tip: When operating the XNPV function, make sure that the columns you have entered cash flows and dates into line up exactly!

What Role Does XNPV Play in Waterfall Calculation?

Generally, a Distribution Waterfall dispenses distributions based on a tier system. Criteria for tier distributions state the conditions that must be satisfied for a certain group of investors before additional money can be distributed to other investors. Usually, the Limited Partners are the first to receive distributions up until a certain threshold, from where General Partners begin to receive the share originally agreed upon.

When looking at the return criteria you need to satisfy in order to distribute to the next tier, the XNPV function in Excel provides an accurate dollar-figure amount down to the day. Specifically, when looking at the total cash flow available from an investment, we can use XNPV to calculate the exact dollar amount that the project will need to generate to fulfill that tier’s return requirement. Similarly, the XNPV function can also tell us how much more money must be distributed to reach that tier requirement (i.e. preferred return of 8%).

Example:

This is a basic waterfall example for a single investment. We can see on the left that the Limited Partners are 80% of the capital structure, so the General Partners are the residual 20%. Also, our first hurdle is an 8% IRR, so that means that the LP’s must receive an 8% IRR before any money can flow to the next waterfall tier. For this calculation in row 15, we are using the XNPV function in combination with the MIN function to return the minimum between 80% of the total cash flow available and the Net Present Value of the cash flows leading up to our current date. This value indicates the dollar amount that the LP’s need to satisfy the 8% IRR.

Diving deeper into this formula, we can see that XNPV is using the 8% IRR as the discount rate to discount our previous cash flows up to this point (E15:L15), matched with our dates (E8:L8). When looking at the “((1+D15)^(1/365)^(M8-E8)” part of the formula, this is simply taking the NPV times 1 + our discount rate (our 8% IRR hurdle in this case) raised to the number of days between the two time periods (M8 and E8) divided by 365. Additionally, we flip the XNPV to negative in the formula for better comprehension because our XNPV would be negative until we reach the hurdle.

Finally, we see in M15 that the LP’s need $529,701 to satisfy the 8% IRR. Once this is satisfied, we see that the cash flow for this tier becomes zero and the excess money begins to flow into the next tier.

Distribution Waterfall Calculation, XNPV, and Real Estate

At Top Shelf Models, we incorporate XNPV into all of our Distribution Waterfall models for the most precise calculations. The XNPV function is flexible and allows for quarterly, monthly or even daily distribution calculations. By providing the dollar amount needed to fulfill tier criteria for waterfall distributions, the XNPV function provides more transparency to all investors involved.

---

About the Author

Eric Bergin is the founder of TSM. He realized that there was a need for real estate financial models that were more than just generic templates. He wanted to create a personalized product for his customers that would ensure success for them and their company. Please reach out to him if you have any questions about XNPV in distribution waterfall calculations or if he can help you with your modeling needs.