# Excel XIRR Function

The Excel XIRR function is a financial function that returns the internal rate of return (IRR) for a series of cash flows that occur at irregular intervals. The XIRR function uses iteration to arrive at a result.

*values*- Array or reference to cells that contain cash flows.*dates*- Dates that correspond to cash flows, in any order.*guess*- [optional] An estimate for expected IRR. Default is 0.1 (10%).

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.

## Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.