Microsoft Excel provides two functions IRR and XIRR for calculating the internal rate of return. Both of them are fairly easy to use, but the results might differ. Even further, when one of the two functions might return an error or even wrong result, the other one might be the better choice. So, which function should you use? IRR or XIRR?
What do the IRR and XIRR functions do in Excel?
The IRR and XIRR functions return the internal rate of return for a schedule of cash flows.
The internal rate of return (IRR) is a metric used in financial analysis to estimate the profitability of potential investments. IRR is a discount rate that makes the net present value (NPV) of all cash flows equal to zero in a discounted cash flow analysis.
https://www.investopedia.com/terms/i/irr.asp
Maybe a little bit easier to understand from the Excel help text:
The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.
Microsoft Excel help of the IRR function.
Because you come to this page, I assume that you already know what the internal rate of return is. However, if you’d like to know more, please feel free to refer to this page.
Structure of the IRR function
The IRR function only has two arguments. And only one of them is mandatory – the second argument is optional.
The first argument contains the values, usually some kind of cash flows. Simply select a range of cells containing (periodical) cash flows.
The second argument is optional: You can enter a guess for the potential result of the IRR function. The reason for this argument is that the IRR calculation is an iterative calculation. Simply speaking, if the function starts the calculation too far off from the final result, it might return a wrong result or not find any result. In such case, you can enter a guess value. In most cases, you won’t need the guess, though. The default value – if you don’t enter anything here – is 10%.
Structure of the XIRR function
The structure of the XIRR function is very similar to the IRR function. It only has one more argument: A range of cells containing dates of the cash flows.
- Values refer to the range of cells usually containing the cash flows.
- The dates argument has the dates for each values cell in the first argument.
- Like in the IRR function, the guess can specify the starting value for the iterative calculation. This is a percentage value potentially close to your expected result of the XIRR function. The default value – if you don’t enter anything here – is 10%.
Ad
Have you ever built an app based on Excel?
What is the difference between IRR and XIRR?
So, let’s get more technical. Microsoft Excel provides two functions to calculate the internal rate of return. =IRR() and =XIRR().
Both functions are quite similar: They calculate the internal rate of return based on cash flows. The major difference is that XIRR can regard dates whereas the IRR function assumes all cash flows to come up periodically.
To demonstrate the differences between IRR and XIRR, we will take a look at four examples. Let’s start!
Example 1
The first example is very straigh-forward. We have a negative cash flow in January 2023 of -100 and every January afterward we have positive cash flow (20 in January 2024 and since then 30 every 12 months).
As you can see, both functions, IRR and XIRR return the same results.
For your reference, the IRR functions look like this:
Example 2
The second example is very similar to the first one above. The only difference is that the dates are different: Instead of January 2024, we have March 2024 and instead of January 2025, we have July 2024. The two different cells are highlighted in orange.
Both functions, IRR and XIRR, are exactly the same as in example 1 above. You can see, they return different results. The IRR function still returns the same results no matter at which point in time the cash flow occurs. The XIRR function on the other hand regards the exact date of the cash flow. In this case, the result of the XIRR function is more accurate.
Example 3
In our third example, we again have periodical values, each value dated to the first of January each year. The difference to the examples before is that the first years value is 0 or empty. The IRR and XIRR functions are again the same as before.
As you can see, XIRR returns 0% whereas IRR returns (the correct) 3.7%. So, the XIRR obviously has problems when the first value or values are empty or 0.
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Add more than 120 great features to Excel!
Example 4
Another example shows that the XIRR is comparatively “fragile” and returns 0, although the IRR can find a result.
Because the XIRR function returns 0%, it might make sense to enter a guess here. We would expect a value around -30% so that -30% is a good value for the guess argument. Like this, the XIRR function would return a better result as well.
When to use IRR and when XIRR?
Usually, my advice is to go with the newer or more advanced Excel function when you have two similar functions. The reason is that newer functions are in most cases more powerful. For example, I generally recommend using SUMIFS instead of SUMIF or XLOOKUP instead of VLOOKUP, e.g.
In this case, it’s a little more tricky.
The first question: Do you have irregular cash flows – cash flows that don’t come periodically? In such case, you have to use XIRR.
If your cash flows occur periodically, you could choose between IRR and XIRR. IRR has one advantage: It accepts 0 cash flows in the first periods. XIRR on the other hand would return 0% (not even indicating that this might be an error…).
As a rule of thumb, if your cash flows come periodically, I would therefore recommend using IRR instead of XIRR.
Why does XIRR return 0% (zero)?
As we have seen before, the XIRR function tends to return (wrongly) 0% much more often than the IRR function does. In such case, you should check the following:
- Is the first value 0 or empty? Make sure that the values argument starts with a value that is not 0.
- Is the result potentially “far” away from the default guess of 10%? In this case, enter a guess for the third argument. For example, -10%.
Why does XIRR return the #NUM! error?
- Check, if the first value is positive. It should be negative for the XIRR function. The IRR function, however, often seems to work with non-negative first values.
From the XIRR help text: XIRR expects at least one positive cash flow and one negative cash flow; otherwise, XIRR returns the #NUM! error value. - For XIRR: If any number in dates precedes the starting date, XIRR returns the #NUM! error value.
- And also for XIRR: If values and dates contain a different number of values, XIRR returns the #NUM! error value.
- If after 20 iterative calculation, IRR can’t find a result, it also returns the #NUM! error. For XIRR, Excel calculates 100 iterations. In this case, please try to enter a guess in the last argument.
Download IRR and XIRR examples
Please feel free to download all the examples from this article in one Excel workbook. Click here and the download starts right away.
Image source: Image by Nattanan Kanchanaprat from Pixabay
You’re pretty cute… I also like Excel 🙂