# XIRR Guessing Games and Distribution Waterfalls

CAUTION: The following article includes math. Please do not be alarmed. The math will not harm you (unless, of course, you fail to consider it and draft documents that incorrectly incorporate its concepts) and a knowledge of advanced math or financial accounting principles is not necessary to understand the basic concepts covered by the article.

Internal rate of return (IRR) frequently appears in distribution provisions of partnership and LLC entity agreements. This use of IRR is a fairly recent innovation and a deviation from the traditional use of IRR as a metric to compare various investments and investment opportunities. Repurposing IRR for use in distribution waterfalls appears to make computing distribution hurdles something of a guessing game and may provide unscrupulous members of entities an opportunity to claim shares of distributions to which they are not entitled.

The following IRR distribution waterfall provision, with an 18 percent IRR hurdle for capital-contributing members, is a simple form of the type of provision that appears in many entity agreements:

Distribution Waterfall. The Manager shall distribute Available Cash at the end of each calendar quarter in the following order of priority:

First, to the Members, pro rata to the extent necessary to cause each Member to have received an 18 percent internal rate of return per annum, compounded quarterly, on such Member’s Capital Contributions; and

Second, 20 percent to the Managing Member and 80 percent to the Members in proportion to their Capital Percentages.

Of course, distribution waterfalls can become much more complex, with multiple IRR hurdles and varying proportions of promote sharing with the manager at each hurdle level. In fact, the complexity can become mind-numbing.

In addition to agreeing upon the waterfall provisions, the parties must agree upon a definition of IRR, which adds another level of complexity to IRR distributions. Parties appear to believe (perhaps naively) that they can reduce complexity by providing that they will compute IRR hurdles using Microsoft Excel’s XIRR function. They may adopt the following definition of IRR that incorporates the XIRR function:

Internal Rate of Return or IRR: That rate (assuming quarterly compounding), which, when applied as a discount rate as of any date, results in the present value of such Member’s Capital Contributions being equal to the present value of distributions received by such Member, taking into account the actual dates of such contributions and distributions. The IRR shall be determined using the Microsoft Excel XIRR function.

That definition of IRR raises interpretive issues, such as what the parties intended the compounding period to be, but these are the topic of a future article by the author and will not be covered here. Instead, this article will focus on the conceptual issues resulting from the use of the Microsoft Excel XIRR function in the definition of IRR. The XIRR function has many redeeming features when used to determine the IRR of projected future cash flows, which is the purpose for which it was designed. (It appears to have glitches, however, even when used for its intended purpose, which is the topic of a future article by the author.) This article shows that the XIRR function holds up much less favorably as a shortcut tool for computing IRR hurdles, which are based upon actual prior cash flows. To appreciate the consequences of using IRR and the XIRR function in distribution waterfalls, one must first consider the traditional application of IRR.

Traditional Use of XIRR to Compare Investment Alternatives

The finance definition of IRR is the rate that causes the net present value of cash outflows and inflows to equal zero. The definition in the sample language above is based upon this definition. If the present value of contributions (the member’s cash outflows) equals the present value of distributions (the member’s cash inflows), the net present value of those cash flows will be zero. Investors typically use IRR to evaluate and decide among multiple investment alternatives. To understand the use of IRR as a tool for evaluating investments, one must consider how basic time-value-of-money formulas apply to a series of cash flows.

A series of four cash flows help illustrate the computation and traditional use of IRR. Assume Ivana Trumpet is considering contributing \$1,000,000 to LLC, a limited liability company, on January 1, Year 1, and anticipates receiving the following distributions:

 Date Projected Cash Flows (distributions) December 31, Year 3 \$550,000 December 31, Year 5 \$700,000 December 31, Year 6 \$1,500,000

Trumpet can use this information and the following net-present-value formula to consider what rate will make the net present value equal zero. where:

• CF = a cash flow.
• r = the rate of return.
• n = the numbers of years until a cash flow occurs.

Assuming Trumpet uses annual compounding and treats outflows as negative values and inflows as positive values, she would use the formula as follows to determine the IRR of the series of cash flows: This equation is too complicated to rearrange to solve for r directly. Instead, Trumpet would have to guess until she finds a rate that causes the equation to be true (i.e., a rate that causes the net present value of the cash flows to equal zero). See Bradley T. Borden, Math Behind Financial Aspects of Partnership Distribution Waterfalls, 145 Tax Notes, Oct. 20, 2014 at 305 (describing the different time-value-of-money formulas and their uses).

Of course, Trumpet probably would not spend her time actually guessing and solving and guessing and solving for r until she causes the equation to be true. Instead, she would most likely use a financial calculator or spreadsheet function like Excel’s IRR function or XIRR function, which would do the guessing and calculating for her. As Microsoft provides, “Excel uses an iterative technique for calculating IRR [and XIRR]. Starting with guess, IRR [and XIRR] cycles through the calculation until the result is accurate within 0.00001 percent [0.000001 for XIRR].” See Microsoft Office Support, XIRR function. Because her cash flows do not occur at regular intervals and annual compounding is sufficient for analyzing projected future cash flows, Trumpet would most likely use the XIRR function to compute the IRR of those projected cash flows. In so doing, she would find that the result is 22.424240 percent. (She could use the IRR function to get the result, but she would have to enter zeros to represent no cash flows at the end of Years 1, 2, and 4.) Trumpet could use the result of her IRR calculation to compare the investment in question with other potential uses of her money and choose investments that exceed the opportunity cost of her capital. Trumpet probably would be happy about the ease with which she is able to compute IRR using Excel, but she probably would not rely solely upon IRR to make investment decisions.

Internal rate of return relates to projected future cash flows, which are estimates of what parties believe might happen in the future, and it applies similarly to all investment alternatives, so investors can tolerate some inaccuracy in the computation of IRR. To illustrate, investors use IRR to standardize the results of different series of cash flows. The cash flows of each series will most likely differ in amount and timing. By using IRR, an investor can obtain a common financial measurement of each series of cash flows. The investor can use that measurement to compare the attractiveness of the various series of cash flows, as compared to each other, and as compared to the investor’s opportunity cost of investing. Because IRR applies uniformly to all investment alternatives, compounding frequency is almost irrelevant (as long as it is the same for all series of cash flows) when computing the IRR of future cash flows. As long as an investor applies the same function to each series of cash flows, the returned IRR facilitates a general comparison of the alternative investments. Investors also realize that future cash flows are merely projections, and the IRR of those cash flows is based upon inputs that could end up varying significantly form the actual cash flows.

Informed investors also know that IRR has fundamental mathematical defects. First, the Fundamental Theorem of Algebra provides that a polynomial to the degree n has n possible roots. Some of those roots will be imaginary numbers, but a polynomial raised to the sixth degree, for instance, should have six possible roots under this theorem. Second, Descartes’s Rule of Signs provides that the number of possible real roots of a single-variable polynomial with real coefficients ordered by descending variable exponent can equal the number of sign differences between consecutive nonzero coefficients. The Rule of Signs would not be a concern for Trumpet in her scenario because the signs of her cash flows change just once from the negative contribution to the positive distributions. If she had made a contribution after receiving a distribution, however, she would have had at least two sign changes and should end up with at least two IRRs, which would diminish the utility of IRR. Thus, even as a tool for analyzing projected future cash flows, IRR is not without its flaws, which explains why investors do not use IRR as the sole tool for evaluating investment alternatives. Prudent investors therefore use other tools in addition to IRR, such as net present value, to evaluate investment alternatives.

The flaws of IRR that manifest themselves in comparisons of projected future cash flows are also present when parties improperly repurpose IRR to compute distribution waterfalls. Because IRR is a tool for analyzing projected future cash flows, it presents additional potential problems when parties to agreement repurpose it to determine the amount of an IRR hurdle based upon actual prior cash flows.

Use of XIRR to Compute IRR Hurdles

Despite the shortcomings of IRR and XIRR, parties to entity agreements often adopt them as shortcut tools for computing IRR hurdles. The XIRR function requires projected cash-flow and date inputs, and it returns a rate that represents the IRR of those projected cash flows. The typical user of the XIRR function knows the amounts and timing of projected cash flows and uses the XIRR function to compute the unknown rate of return of those cash flows. The use of the XIRR function to compute IRR hurdles requires repurposing and adapting the function in two ways. First, to make the XIRR function compute the amount of an IRR hurdle, the parties must reimagine the actual prior cash flows as being projected future cash flows. Second, the parties must modify the use of the function from one that finds an unknown rate to one that finds an unknown cash flow when the rate and other cash flows are given. To accomplish this second adaptation, parties must iteratively enter values for the unknown cash flows (i.e., guess what the cash flow should be) until the XIRR function returns a value that equals the rate provided for in the agreement. The XIRR function can return a very accurate rate (assuming it overcomes the theoretical math problems inherent in IRR) based upon projected cash flows, but when parties repurpose it to compute IRR hurdles the second level of guessing allows for a wide range of possible hurdle values. This iterative use of the XIRR function to compute an IRR hurdle is the “indirect method” of computing IRR hurdles.

The indirect method requires reimagining prior cash flows to be projected future cash flows, so a person applying the indirect method must assume that the first cash flow, which generally will be a contribution to the entity, occurs at the present time and that all other cash flows occur at some future time. Reimagined facts of the Trumpet example help illustrate this phenomenon. Assume that Trumpet decides to contribute \$1,000,000 to LLC on January 1, Year 1, in exchange for an interest in LLC. The LLC operating agreement provides that LLC will distribute its available cash to Trumpet until she receives an 18 percent IRR. After satisfying Trumpet’s IRR hurdle, LLC will distribute available cash 80 percent to Trumpet and 20 percent to the managing member. The LLC operating agreement provides that the LLC will determine Trumpet’s IRR using Excel’s XIRR function.

To compute Trumpet’s IRR hurdle on December 31,Year 6, LLC would use the following version of the net-present-value formula from above (LLC would set r equal to 18 percent, but this discussion represents r as a variable for illustrative purposes): In this equation, LLC is looking for the unknown X, which represents the amount that it must distribute on December 31, Year 6, to satisfy Trumpet’s IRR hurdle. Notice that the exponents (representing the time variable n) in the compounding expression ((1+r)n) are respectively 0 for the January 1, Year 1, contribution; 3 for the December 31, Year 3, distribution; 5 for the December, 31, Year 5, distribution; and 6 for the December 31, Year 6, distribution. Thus, even though LLC is considering the distribution that it has to make on December 31, Year 6, it is treating that distribution as occurring six years in the future, and it is treating the contribution as occurring at the present time (December 31, Year 6), even though it occurred six years ago. The two earlier distributions occurred one year and three years ago, respectively, but the equation treats them as occurring three years and five years, respectively, in the future. Thus, repurposing the XIRR function to compute IRR hurdles is a bit clunky.

One glaring problem that results from repurposing the XIRR function to compute IRR hurdles is the range of answers that rounding can cause. Consider how LLC would apply the function. At the end of Year 3, LLC has \$550,000 of available cash. The XIRR function (taking into account the prior contribution) provides that if it distributes all \$550,000 of available cash to Trumpet on December 21, Year 3, her IRR would be about −18 percent, which does not satisfy her 18 percent IRR hurdle, so LLC will distribute the entire amount to her. When LLC has \$700,000 of available cash at the end of Year 5, the XIRR function (taking into account the prior contribution and distribution) shows that Trumpet’s IRR would only be 6 percent, if LLC were to distribute all \$700,000 of available cash to Trumpet on December 31, Year 6, so it would distribute all \$700,000 to Trumpet at that time. At the end of Year 6, LLC has \$1,500,000 of available cash. LLC would again use the XIRR function (taking into account the prior contribution and distributions) to determine whether a distribution of all of the \$1,500,000 will satisfy Trumpet’s IRR hurdle. If LLC were to distribute all \$1,500,000 of the available cash to Trumpet on December 31, Year 6, Trumpet’s IRR would be 22 percent. Consequently, LLC would not distribute all \$1,500,000 of available cash to Trumpet on December 31, Year 6.

To determine the amount it should distribute to Trumpet, LLC would have to enter guess values into the array that the XIRR function uses to compute IRR. Those guesses would reveal that, due to rounding, Trumpet would obtain an 18 percent IRR if LLC distributed any amount from \$916,522 (17.500007749 percent IRR) to \$1,023,788 (18.499993682 percent IRR). Thus, a \$107,266 (\$1,023,788−\$916,522) guess-gap results from using the XIRR function if the parties round to the nearest whole percentage. That guess-gap is 5 percent of the total distributions (\$2,166,522) that Trumpet would receive based upon the low-end estimate and is 12 percent of the total low-end final distribution (\$916,522) she would receive, so the rounding error is significant. The XIRR function is accurate to the nearest millionth of one percent when it considers given projected cash flows and dates, but it does not accurately compute cash flows because that is not what it was built to do. Repurposing the function adds another level of guessing, and the two layers of guessing (inputting guess cash flows and the XIRR function’s rate guessing) allow the function to return a wide range of possible answers. Entity agreements that adopt the indirect method should address the rounding error that results from the use of the XIRR function (e.g., require the returned rate to be accurate to the nearest one millionth of one percent), but the author has yet to see an agreement that does.

Another potential shortcoming of the XIRR function is that it computes IRR using annual compounding. Adopting the XIRR function could therefore be a problem for entities that define IRR as a rate that compounds more frequently than annually. Entity agreements might address this problem by either requiring the result of the XIRR function to be converted to the nominal rate with the appropriate compounding using Excel’s “nominal” function, or by adding language to the IRR definition such as “IRR will be calculated with quarterly compounding using the following formula: =((1+XIRR)^(1/4)−1)*4.” This application of the nominal function adds an additional layer of complexity to the application of the indirect method and it does not solve the problem related to rounding. Thus, the indirect method of computing IRR hurdles using the XIRR function is flawed. Although parties may believe that they are doing themselves a favor by adopting the XIRR function as a simplification tool, they expose themselves to other problems and potential legal actions, the costs and complexity of which could dwarf any perceived complexity of using the direct method to compute IRR hurdles.

Direct Method of Computing IRR Hurdles

The direct method of computing IRR hurdles uses known values and an appropriate financial tool to directly compute IRR hurdles. In fact, the direct method adopts a formula that is something of a mirror image of the formula that Excel claims to use to compute IRR. Unlike the indirect method that incorporates the XIRR function, which uses guessing and is designed to compute an unknown rate, the direct method uses known values to directly compute the unknown IRR hurdle, so it does not require guessing.

The discussion above illustrates how LLC must reimagine prior cash flows as future cash flows to repurpose and apply the XIRR function to compute Trumpet’s IRR hurdle. LLC could, of course, avoid problems that result from repurposing the XIRR function by simply rewriting the equation to directly solve for X, the unknown amount of the distribution that will satisfy Trumpet’s IRR hurdle. LLC can rewrite the formula used in the indirect method by first subtracting from both sides of the equation, after which the equation would appear as follows: LLC could then multiply both sides of the equation by , after which the equation would read as follows: LLC could then substitute the 18 percent target IRR rate for r and solve for X. The equation would therefore become: , and X would equal \$969,887. Notice that the direct method returns a single exact result using the rate from the agreement, not a range of possible values. This would be the amount that LLC should distribute to Trumpet on December 31, Year 6, assuming annual compounding of 18 percent. This \$969,887 amount is, not surprisingly, somewhere between the \$916,522 low and the \$1,023,788 high end of the range that results from guessing with the indirect method.

Notice several phenomena about the direct method. First, LLC does not have to guess with respect to any aspect of the computation of the IRR hurdle. The direct-method formula has a single unknown variable, the IRR hurdle, and it directly solves for that variable. Second, LLC does not have to repurpose the direct-method formula and reimagine the times at which the cash flows occur. Notice that on December 31, Year 6, the direct method treats the January 1, Year 1, contribution as occurring six years ago, and the prior distributions as occurring three years and one year ago, respectively. That reflects the actual time periods that have passed since those cash flows occurred. Third, Trumpet’s cash contribution (i.e., her cash outflow) is represented as a positive number (the amount that she invested) and the distributions to her (i.e., her cash inflows) are represented as negative values (i.e., reductions of her investment).

This example assumes that all cash flows occur at the beginning or ending of a year (i.e., at regular intervals), but the direct method can also accommodate computations that include cash flows that do not occur at regular intervals (i.e., intraperiod cash flows). One of the reasons that parties choose the indirect method is because the XIRR function accounts for payments that may not occur at regular intervals. Microsoft’s definition of XIRR provides that it does this by taking into account projected cash flows and the dates on which they occur and then guesses the rate until: where:

• di = the ith, or last, payment date.
• d1 = the 0th payment date.
• Pi = the ith, or last, payment.

See Microsoft Office Support, XIRR function. The application of this formula to Trumpet’s contribution and distributions would appear as follows: As discussed above, to apply this formula to Trumpet, LLC would have to assume that the 0th payment date was January 1, Year 1, the ith date for the respective cash flows would be December 31, Year 3, for the first distribution; December 21 Year 5, for the second distribution; and December 31, Year 6, for the distribution in question. The time-variable exponents, using Excel to compute the number of days, for the respective cash flows would therefore be as follows: , , , and . Thus, the values of the time variables for the respective cash flows would be , , , . Because Trumpet’s contribution occurred on January 1, and the distributions occurred only at the end of the respective years, all of the time values are roughly whole numbers. (Excel’s day counting appears to not count both the first and last days given, so 12/31/03 minus 1/1/01 is not exactly 3, as one would otherwise expect. Nonetheless, it appears to treat leap year (Year 4) as having 366 days, which appears to be the reason that the time variables of the third and fourth payments equal whole numbers.) The time values would not be whole numbers if the cash flows occurred on dates other than the last day of a year, but the formula would still be able to account for those cash flows and accurately compute IRR.

The direct method can also account for cash flows that do not occur at regular intervals. As shown above, the time periods used for the direct method are the inverse of the time periods used for the indirect method. Thus, the general formula for the direct method would be: where:

• di = the ith, or first, cash-flow date.
• d1 = the 0th (or current) cash-flow date.
• Pi = the ith, or first, cash flow.
• D = the distribution required to satisfy the IRR hurdle.

The direct method does not require imagining that the 0th payment date as a date other than the date under consideration. Thus, LLC would treat December 31, Year 6 as the 0th payment date. The number of days since the other cash flows will equal 12/31/06 minus the respective dates of the other cash flows. Thus, the time variable for the January 1, Year 1, cash flow will be , for the December 31, Year 3, cash flow it will be , for the December 31, Year 5, cash flow it will be , and for the December 31, Year 6, cash flow, it will be .

This analysis uses Excel to count the days, so they may vary from the number that other counting techniques would return. The parties can adjust the manner in which they count days if they use the direct method. Thus, for instance, the parties can choose whether to include the contribution date in the calculation of the time variable.

Using the direct method, LLC could compute Trumpet’s IRR hurdle as follows: Based upon this equation, the payment required to satisfy Trumpet’s 18 percent IRR hurdle is \$969,477. The \$410 difference between this value and the \$969,887 amount obtained using whole numbers for the time variables, appears to be the result of Excel’s day-counting convention. To avoid confusion, parties should provide in their agreement how they will count days. Although such a provision may require additional drafting, the parties can control the variable, which they are unable to do if they adopt the XIRR function. Notice, further, that the \$410 difference caused by day-counting convention is only 0.04 percent of the low-end final distribution using whole time-variable values and is only 0.02 percent of the total low-end distributions. Thus, the difference caused by day-counting conventions is negligible, but the parties can easily eliminate the discrepancy by providing how they will compute the values of the time variables.

As stated above, the XIRR function does not account for compounding that is more frequent than annual, so parties that adopt XIRR should be prepared to convert the effective rate computed using XIRR to a nominal rate. The parties can easily adjust the direct-method formula to account for compounding that is more frequent than annual by dividing the rate variable and multiplying the time variable by the number of compounding periods per year. The direct-method formula that accounts for compounding and intraperiod payments would appear as follows: where:

• y = the number of compounding periods per year.

Using this formula for the Trumpet contribution and distributions and quarterly compounding (i.e., setting y equal to 4), Trumpet’s IRR hurdle on December 31, Year 6, would be \$1,108,066.

Finally, the Fundamental Theorem of Algebra and Descartes’s Rule of Signs do not apply to the direct-method formula because the rate and time variables have known values. Therefore, use of the direct method to compute IRR hurdles eliminates the mathematical flaws that are part of the indirect method.

The direct method therefore has many redeeming qualities and addresses the problems that the indirect method otherwise causes. Consequently, parties would be well served to define IRR in terms that adopt the direct method. The following is an example of language that parties could use to adopt the direct method for computing IRR hurdles:

Internal Rate of Return or IRR. A Member shall have received the specified Internal Rate of Return when the Member receives distributions sufficient to make the following equation equal zero: where:

• di = the ith, or first, cash-flow date.
• d1 = the current distribution date.
• Pi = the ith, or first, cash flow.
• y = the number of compounding periods per year.
• D = the distribution required to satisfy the IRR hurdle.

In applying the equation, contributions shall be treated as positive cash flows, and distributions shall be treated as negative cash flows. The Manager shall use Excel to compute d1di

Direct and Indirect Use of the Direct Method

Parties can choose to directly use the direct method by providing for its use in their agreements, or they may be forced to use it indirectly. For instance, regardless of what an entity agreement may provide for, an entity’s accountant may default to the direct method to compute the IRR hurdles, or, if a dispute arises with respect to the result obtained using the indirect method, the parties may settle the dispute using the direct method. In reality, parties to some entity agreements that explicitly adopt the indirect method actually use a form of the direct method by calculating IRR hurdles using spreadsheets that incorporate some version of the direct method. Parties to other agreements that adopt the indirect method may find that they have to use the direct method (either by agreement as part of a settlement or as directed by a judge or arbitrator) to resolve disputes that arise with respect to the problems associated with computing IRR hurdles using the indirect method. Parties may, however, use the indirect method and never dispute its results for any one of several possible reasons, including the following: (1) all members of an entity that adopts the indirect method may be satisfied with the returns they receive, (2) one or more of the parties may not understand the flaws of the indirect method and not appreciate that they are losing money that they would be entitled to under the direct method, (3) one or more of the parties may understand and exploit the flaws of the indirect method, (4) the entity agreement may address each of the indirect method’s flaws to the satisfaction of the entity’s members, or (5) the parties attach special significance to the description of the indirect method that differs from the technical finance definition of that description.

If a dispute does arise with respect to the results that derive from the indirect method, the objective way to resolve such a dispute is to use the direct method. For example, if the parties cannot agree upon which value to choose from the range of values that result from rounding when they apply the XIRR function, a judge could resolve that dispute by using the direct method. Consequently, unless a dispute does not arise, the parties should end up using the direct method, even if the agreement provides for the use of the indirect method. They can avoid the cost of contesting the results by including the direct method in their agreement from the beginning.

Why the Prevalence of XIRR?

Despite the flaws associated with the use of the XIRR function and the indirect method, numerous agreements adopt the indirect method. The use of the indirect method begs the question: Why are drafters defining IRR with reference to the XIRR function? Two types of drafters would most likely avoid the use of the XIRR function: (1) those who do not understand the XIRR function and refuse to draft language that they do not understand, and (2) those who thoroughly understand the XIRR function and its flaws and refuse to draft language that incorporates the flaws associated with the indirect method. Arguably, all attorneys should fall into one of those two categories, but some still incorporate the XIRR function into definitions of IRR.

Three explanations of the continued use of the XIRR function come to mind readily: (1) some drafters understand the XIRR function and appreciate its technical flaws, but they recognize that their clients attach a special meaning to the definition of IRR and make distributions according to their special meaning, disregarding the technical aspects of the XIRR function; (2) some drafters understand the XIRR function and use it to exploit its flaws to their client’s advantage; and (3) some drafters neither understand the XIRR function nor appreciate the flaws of the indirect method, but they erroneously believe they understand it, believe it simplifies the computation of IRR, and fail to recognize the flaws that they are incorporating into the entity agreement. The latter two reasons for including the XIRR function in the definition of IRR are troubling, and one would hope they are the rare exception.

Conclusion

The use of the XIRR function in distribution waterfalls appears to be in vogue. Its popularity is almost inexplicable from all but a cynical view. The people who use it appear to be blissfully ignorant of its flaws, to not appreciate its flaws, or worse, to appreciate its flaws and exploit them to their advantage. Without specific knowledge of a particular situation, determining which of those possible scenarios reflects reality is anyone’s guess. Parties can eliminate these and other guessing games by avoiding the XIRR function, jettisoning the indirect method, and explicitly adopting the direct method. Parties who are members of agreements that adopt the indirect method can verify the accuracy of distributions they receive by computing IRR hurdles using the direct method. Such a check will help ensure that distributions occur fairly, in accordance with sound financial principles.