Excel XIRR Function

The XIRR function calculates the internal rate of return for a series of cash flows that occur at irregular intervals. Payments are expressed as negative values and income as positive values.  If the first value is a cost or payment, it must be a negative value. Subsequent payments are discounted based on a 365-day year. To calculate the internal rate of return for a series of regular, periodic cash flows, use the IRR function.

XIRR is related to the XNPV function. The rate returned by XIRR is the interest rate when XNPV = 0. The XIRR function uses iteration to arrive at a result. Starting with guess (which defaults to 0.1 if not provided) XIRR iterates through a calculation until the result is accurate to 0.000001 percent. If no result is found after 100 tries, XIRR returns the #NUM! error.

The XIRR function takes three arguments: values, dates, and guess. Values represent a series of cash flows. The first value is optional and corresponds to a cost at the beginning of the investment. If the first value is a cost or payment, it must be a entered as a negative number. Values must include at least one positive and one negative value, or XIRR will return a #NUM! error. If values contains any non-numeric values, XIRR returns a #VALUE! error.

The dates argument represents a schedule of dates that correspond to values. The values supplied for dates must be valid Excel dates. Dates do not need to be entered in chronological order. Typically, dates is supplied as a range. If any date is not recognized as a date, XIRR returns a #VALUE! error.

The guess argument is optional and represents the seed value to start with for the iterative calculation used by XIRR. If not provided, guess defaults to 10% (0.10). Typically, you can safely omit guess. If XIRR returns #NUM!, and values contains at least one positive and one negative value, try different percentages for guess between 0 and 1.

Example

In the example shown, dates are in the values are in the range B5:B10, and dates are in the range C5:C10. The formula in cell F4 is:

=XIRR(B5:B10,C5:C10)  // returns .0788

The result returned by XIRR is .0788, displayed as 8% when the percentage number format is applied.

Notes

• The values array must contain at least one positive value and one negative value.
• Dates must be valid Excel dates that correspond to values
• Dates do not need to be in chronological order.
• XIRR is related to the XNPV function.