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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
