Purpose
Return value
Syntax
=XIRR(values,dates,[guess])- 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%).
How to use
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.
Related functions
PV Function
NPV Function
The Excel NPV function is a financial function that calculates the net present value (NPV) of an investment using a discount rate and a series of future cash flows.
IRR Function
The Excel IRR function is a financial function that returns the internal rate of return (IRR) for a series of cash flows that occur at regular intervals.
MIRR Function
The Excel MIRR function is a financial function that returns the modified internal rate of return (MIRR) for a series of cash flows, taking into account both discount rate and reinvestment rate for future cash flows.
XNPV Function
The Excel XNPV function is a financial function that calculates the net present value (NPV) of an investment using a discount rate and a series of cash flows that occur at irregular intervals.
