Using the Excel XIRR Function
SummaryThe XIRR function is one of Excel's financial functions and produces the internal rate of return for a series of cash flows that may not be periodic and have irregular intervals.
This differs from the IRR function that requires cash flows to be regular and periodic.
Syntax=XIRR (values, dates, [guess])
Required. A series of cash flows that correlates to a schedule of payments given as dates. The first payment value is optional and represents a cost or initial payment at the beginning of an investment. If the first value is a cost or payment, it must be entered as a negative number.
The value argument must contain at least one positive and one negative number or else the function returns the #NUM! error.
Required. A schedule of dates that corresponds to the cash flow payments. Dates should be entered in chronological order, and should be entered using the DATE function to avoid errors resulting from dates entered as text.
Excel recognizes all dates between January 1, 1900 and December 31, 9999.
Optional. A rate you believe is close to the XIRR. If guess is omitted, it is assumed to be 10%.
Usage NotesXIRR returns the modified internal rate of return for a series of cash flows occurring at irregular intervals while considering the discount rate and reinvestment rate.
XIRR is related to the XNPV function as the rate of return calculated by XIRR is the interest rate that corresponds to XNPV = 0.
Similar to IRR and RATE, XIRR uses iterations to produce a result. The function starts with the guess value, if provided, or 10%, if guess isn't provided, and iterates until the result is accurate within 0.000001 percent. The function returns the #NUM! error if a result isn't found within 100 attempts.
The XIRR function will return the following errors:
- #NUM! error if the function doesn't contain at least one positive and one negative value;
- #VALUE! error if any value in the date argument isn't a valid date;
- #NUM! error if any value in the date argument precedes the starting date;
- #NUM! error if the values and dates argument contain a different number of values