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.
Calculate internal rate of return for irregular cash flows
Calculated internal rate of return
=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%).
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.
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 Excel PV function is a financial function that returns the present value of an investment. You can use the PV function to get the value in today's dollars of a series of future payments, assuming periodic, constant payments and a constant...
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.