Using Excel's Xirr and Xnpv Add-in Functions

Excel provides standard functions, IRR and NPV, for calculating the internal rate of return and net present value of a set of cash flows. While most often you’ll want to use these two functions, they may sometimes present a practical problem: Both the IRR and NPV functions assume you’ve first constructed a worksheet that arranges the cash flows into equal periods. In other words, to use the IRR or NPV function, you must first construct a worksheet that shows the investment’s monthly cash flows, or its annual cash flows, or the cash flows from some other consistent time period.

Some Background on the XIRR and XNPV Functions

Unlike the IRR and the NPV functions, the XIRR and XNPV functions don’t require you to first construct a worksheet schedule that arranges the investment cash flows into equal periods. With the XIRR and XNPV functions, you supply the date values that correspond to the cash flow values to the function as arguments.

NOTE: You might want to review the Microsoft Help file's discussion of the IRR and NPV functions if you have questions about how the XIRR and XNPV tools work.

The somewhat unique feature of both the XIRR and the XNPV function is that if you supply the actual date values or cash flow values inside the formula as arguments, they expect you to supply the values argument and the dates argument as arrays. (An array is just a set of numbers.)

For example, suppose that you want to calculate the internal rate of return and net present value for an investment that produces the following cash flows on the following dates:

1/1/2000 -1000

12/31/2000 -1000

4/15/2001 2000

12/31/2001 1000

If you include the actual array in the argument, you can designate the array by enclosing the values and dates arguments inside braces. To show the preceding date values in an array, for example, you would type the following:

{"1/1/2000","12/31/2000","4/15/2001","12/31/2001"}

To show the preceding cash flow values as an array, you would type the following:

{-1000,-1000,2000,1000}

If you enter the date values and cash flow values in worksheet ranges, you don’t need to worry about identifying the date values and cash flow values as arrays. For example, if you enter the preceding set of date values in the worksheet range A1:A4 and the preceding set of cash flow values in the worksheet range B1:B4, you can use these worksheet ranges as the function arguments.

The XIRR and XNPV functions, predictably, require you to use date values that are valid. You must also use the same number of date values as you use cash flow values. If you supply an invalid argument or set of arguments, Excel returns the #NUM error value.

NOTE: Excel considers the first date value to be the starting date of the investment. Accordingly, the first date value in your array or worksheet range must be the earliest. Subsequent date values don't have to be in chronological order, however.

Using the XIRR Function

The XIRR function calculates the internal rate of return for an investment given its cash flows, the dates of those cash flows, and, optionally, an initial guess as to the internal rate of return. The function uses the following syntax:

XIRR (values, dates, guess)

For example, suppose that you want to calculate the internal rate of return for an investment that produces the following cash flows on the following dates:

1/1/2000 -1000

12/31/2000 -1000

4/15/2001 2000

12/31/2001 1000

To calculate the internal rate of return for this set of cash flows using the XIRR function and using a starting guess of 20%, you would use the following formula:

=XIRR({-1000,-1000,2000,1000},{"1/1/2000","12/31/2000","4/15/2001","12/ 31/2001"},.2)

The formula returns the value .470251, which is equivalent to 47.0251% annually.If the date values were stored in the worksheet range A1:A4 and the cash flow values were stored in the worksheet range B1:B4, you could instead use the following formula:

=XIRR (B1:B4,A1:A4,.2)

Using the XNPV Function

The XNPV function calculates the net present value for an investment given its cash flows, the dates of those cash flows, and the annual discount rate. The function uses the following syntax:

XNPV (rate, values, dates)

For example, suppose that you want to calculate the net present value for an investment that produces the following cash flows on the following dates:

1/1/2000 -1000

12/31/2000 -1000

4/15/2001 2000

12/31/2001 1000

If the date values were stored in the worksheet range A1:A4, the cash flow values were stored in the worksheet range B1:B4, and you wanted to use a discount rate of 15%, you would use the following formula:

=XNPV (.15,B1:B4,A1:A4)

The formula returns the value 557.17.

Curiously, the XNPV function doesn’t accept date values supplied as text strings. For example, although the following formula is equivalent to the preceding one, it returns the

#VALUE error value:

=XNPV (.15,{-1000,-1000,2000,1000},{"1/1/2000","12/31/2000","4/15/

2001","12/31/2001"})

You could, however, rewrite this formula using equivalent serial date values (the serial date 36526 for 1/1/2000, the serial date value 36891 for 12/31/2000, and so on), and then Excel returns the correct net present value:

=XNPV (0.15,{-1000,-1000,2000,1000},{36526,36891,36996,37256})