What is XIRR?
XIRR stands for Extended Internal Rate of Return, which is a financial algorithm used to measure the rate of return on investments made into an investment at irregular intervals.
The XIRR function, which is common to most spreadsheet software programs, uses an iterative process to keep trying slightly higher and lower rates until it finds a rate that would yield the final value of the investment.
Since the number of iterations the XIRR function can use to find the rate of return is finite, the function displays an error if it reaches its maximum number of tries before finding a rate match. This is why the function also allows you to enter a guess, increasing the chance the function will find a match before it reaches its iteration limit.
XIRR vs IRR
The difference between IRR and XIRR is IRR measures rate of return on uneven cash flows made at regular intervals, whereas XIRR measures the return on even or uneven cash flows made at irregular intervals.
For example, if you are adding a varying amount to an investment on an every-month basis, you would use IRR to calculate the rate of return.
On the other hand, if you are adding a fixed or varying amount to an investment at odd intervals (May of this year, June of next year, etc.), you would use XIRR to calculate the rate of return.
How to calculate XIRR in Excel
To use the XIRR Function in Excel, you enter the dates of the cash flows in one column (ordered from oldest to latest), and the amounts in a second column ("-" indicates you deposited funds into the investment). Next, you insert the XIRR function (XIRR(values, dates, guess)) and provide the function with the value and date ranges, along with your best guess, like this:
|Formula in cell B5 =XIRR(B1:B4,A1:A4,0.08)|
Note that it is best to use the date function to enter dates, rather than entering dates as text.