The tutorial shows how to use XIRR in Excel to calculate the internal rate of return (IRR) for cash flows with irregular timing and how to make your own XIRR calculator.
When you are faced with a capital-intensive decision, calculating the internal rate of return is desirable because it lets you compare the projected returns for different investments and gives a quantitative basis for making the decision.
In our previous tutorial, we looked at how to calculate the internal rate of return with the Excel IRR function. That method is quick and straightforward, but it has an essential limitation – the IRR function assumes that all cash flows occur at equal time intervals such as monthly or annually. In real life situations, however, cash inflows and outflows often happen at irregular intervals. Thankfully, Microsoft Excel has another function to find IRR in such cases, and this tutorial will teach you how to use it.
The Excel XIRR function returns the internal rate of return for a series of cash flows that may or may not be periodic.
The function was introduced in Excel 2007 and is available in all later versions of Excel 2010, Excel 2013, Excel 2016, Excel 2019, and Excel for Office 365.
The syntax of the XIRR function is as follows:
For example, to calculate IRR for the series of cash flows in A2:A5 and dates in B2:B5, you'd use this formula:
Tip. For the result to display correctly, please make sure the Percentage format is set for the formula cell.
The following notes will help you better understand the inner mechanics of the XIRR function and use it in your worksheets most efficiently.
The XIRR function in Excel uses a trial and error approach to find the rate that satisfies this equation:
Starting with the guess if provided or with the default 10% if not, Excel goes through iterations to arrive at the result with 0.000001% accuracy. If after 100 attempts an accurate rate is not found, the #NUM! error is returned.
To check the validity of this equation, let's test it against the result of the XIRR formula. To simplify our calculation, we will be using the following array formula (please remember that any array formula must be completed by pressing Ctrl + Shift + Enter):
As shown in the screenshot below, the result is very close to zero. Q.E.D. :)
Below are a few examples that demonstrate the common uses of the XIRR function in Excel.
Suppose you invested $1,000 in 2017 and expect to receive some profit in the next 6 years. To find the internal rate of return for this investment, use this formula:
Where A2:A8 are cash flows and B2:B8 are the dates corresponding to the cash flows:
To judge the profitability of this investment, compare the XIRR output with your company's weighted average cost of capital or hurdle rate. If the returned rate is higher than the cost of capital, the project can be considered a good investment.
When comparing several investment options, please remember that a projected return rate is just one of the factors that you should estimate before making a decision. For more information, please see What is the internal rate of return (IRR)?
In case you know what kind of return you are expecting from this or that investment, you can use your expectation as a guess. It is especially helpful when an obviously correct XIRR formula throws a #NUM! error.
For the data input shown below, an XIRR formula without the guess returns an error:
The anticipated return rate (-20%) put in the guess argument helps Excel arrive at the result:
=XIRR(A2:A7, B2:B7, -20%)
For starters, please remember this – whatever cash flows you are calculating, the Excel XIRR function produces an annual rate of return.
To make sure of this, let's find IRR for the same series of cash flows (A2:A8) that occur monthly and yearly (the dates are in B2:B8):
As you can see in the below screenshot, the IRR goes from 7.68% in case of annual cash flows to about 145% for monthly cash flows! The difference seems too high to be justified by the time value of money factor alone:
To find an approximate monthly XIRR, you can use the below calculation, where E1 is the result of the regular XIRR formula:
Or you can embed XIRR directly in the equation:
As an additional check, let's use the IRR function on the same cash flows. Please keep in mind that IRR will also compute an approximate rate because it assumes all the time periods to be equal:
As the result of these calculations, we get a monthly XIRR of 7.77%, which is very close to 7.68% produced by the IRR formula:
The conclusion: if you are looking for an annualized IRR for monthly cash flows, use the XIRR function in its pure form; to get a monthly IRR, apply the adjustment described above.
To quickly get the internal rate of return for different projects, you can create a versatile XIRR calculator for Excel. Here's how:
Where Sheet1 is the name of your worksheet, A2 is the first cash flow, and B2 is the first date.
For the detailed step-by-step instructions, please see How to create a dynamic named range in Excel.
Done! You can now add or remove as many cash flows as you want, and your dynamic XIRR formula will recalculate accordingly:
The main difference between Excel XIRR and IRR functions is this:
Generally, if you know the exact dates of the payments, it is advisable to use XIRR because it provides better calculation accuracy.
As an example, let's compare the results of IRR and XIRR for the same cash flows:
If all the payments occur at regular intervals, the functions return very close results:
If the timing of cash flows is unequal, the difference between the results is quite significant:
XIRR is closely related to the XNPV function because the result of XIRR is the discount rate that leads to a zero net present value. In other words, XIRR is XNPV = 0. The following example demonstrates the relationship between XIRR and XNPV in Excel.
Let's say you are considering some investment opportunity and want to examine both the net present value and the internal rate of return on this investment.
With the cashflows in A2:A5, dates in B2:B5 and the discount rate in E1, the following XNPV formula will give you the net present value of the future cash flows:
=XNPV(E1, A2:A5, B2:B5)
A positive NPV indicates that the project is profitable:
Now, let's find what discount rate will make the net present value zero. For this, we use the XIRR function:
To check if the rate produced by XIRR really leads to a zero NPV, put it in the rate argument of your XNPV formula:
=XNPV(E4, A2:A5, B2:B5)
Or embed the entire XIRR function:
=XNPV(XIRR(A2:A5, B2:B5), A2:A5, B2:B5)
Yep, the XNPV rounded to 2 decimal places does equal zero:
To display the exact NPV value, choose to show more decimal places or apply the Scientific format to the XNPV cell. That will produce a result similar to this:
If you are not familiar with the scientific notation, perform the following calculation to convert it to a decimal number:
1.11E-05 = 1.11*10^-5 = 0.0000111
If you have run into a problem with the XNPV function in Excel, below are the main points to check.
A #NUM error may occur because of the following reasons:
A #VALUE error may be caused by the following:
That's how you calculate XIRR in Excel. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our sample workbook below. I thank you for reading and hope to see you on our blog next week!
XIRR Excel template (.xlsx file)
Table of contents