XL: Algorithm Used by the XIRR() Function

• Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

SUMMARY

The following information describes the algorithm used by the XIRR() function of Microsoft Excel to compute the internal rate of return on a schedule of cash flows that are not necessarily periodic, that is, payments may be made at different time intervals.

MORE INFORMATION

Microsoft Excel includes a function called XIRR() which returns the internal rate of return for a schedule of cash flows that are not necessarily periodic. This function is similar to the IRR() function which returns the internal rate of return for a series of periodic cash flows.

NOTE: If the XIRR() function is not available, you must install the Analysis ToolPak add-in.

With IRR(), all cash flows are discounted using an integer number of compounding periods. For example, the first payment is discounted one period, the second payment two periods, and so on.

With XIRR(), we want to permit payments to occur at unequal time periods. Our approach is to associate a date with each payment and thereby permit fractional periods (raising or discounting with a fractional power).

The next step is to arrive at the correct discounting rate. Basically, the larger the rate, the more the values are reduced.

We set bounds on the discount rate above and below the correct rate by doubling guesses in each direction. With known upper and lower bounds, we use Newton's method to find the appropriate guess to any level of accuracy.

At each stage we perform the discounting calculation.

NOTE: Newton's method is a way to zoom in on a root of an equation (y=f(x)) by using the tangent line to the equation's curve at successive x-values. The new x-value keeps getting closer and closer to the root of the equation until you reach some preset precision.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, pages 466-467