If you are working on a project and need to determine the internal rate of return you may stumble across the IRR and the XIRR functions. Although both the functions work with the same dataset and will return the IRR (Internal Rate of Return), they will give you very different results. You may ask why you would choose one over the other. And we have the answer.
This guide will go into the depths of why this is and where you can use the two functions. But first, let’s take a look at what IRR (Internal Rate of Return) is.
Let’s get going!
Contents
What is the Internal Rate of Return (IRR)?
To simply describe the Internal Rate of return, it is the discount rate that equates your Net Present Value of your Future cash flow to zero (0). Confused? Don’t worry. If the Net Present value of your investment is zero, it doesn’t necessarily mean that it is a bad investment. It simply means that you will get the discount rate (the IRR) as your rate of return for your investments.
Looking at the IRR formula may help you understand the concept better. The IRR formula is:
0 = NPV = t∑t=1 Ct/(1+IRR)t − C0
The formula may seem hard to grasp at first, but when you know what each part denotes, it’s fairly simple. Let’s take a look at the breakdown of the formula:
NPV is the Net present value
t is a particular time
Ct is the Net Cash Inflow during this time t.
C0 is the initial Cash or the Initial Investment Cost.
So, looking back at the IRR formula, what IRR does is equate the Net Present value to 0. That means the difference between your NPV and your original investment will be equal to zero. As we said before, this does not mean that it is a bad investment. But mostly, the higher your IRR is the more likely that your investments will be profitable. The basic idea is when you compare your IRR output with a weighted average cost of capital for your project, If the IRR is higher than the cost of capital, then you can consider the investment potentially profitable.
IRR essentially is a potential annual growth that your initial investment would return. So, among different investment options, you are most likely to choose the one with the highest IRR. Although you can calculate your IRR using this formula, you will be better served by using Excel to do the job for you much more efficiently.
The IRR Function in Excel
The IRR function in Excel can help you easily calculate the IRR of your investments in a few clicks. This function allows you to calculate the rate of return on a project or an investment. But the IRR function does this without considering external factors. So, the IRR formula will not consider the corresponding dates.
The IRR function assumes that your project’s cash flow interval is equal. And so the function just chalks it up to a yearly estimation and assumes you have only one cash inflow every year and the inflow is occurring at exactly the same date.
But often this is not the case and the cash flow may not come on a set yearly basis. For example, you may have an initial investment set up front. And that may not be on the last day of the year.
The formula for the IRR function in Excel is
=IRR(values, [guess])
Formula Breakdown
Here, values denote your range of cash inflows and it is a required parameter of the function. The guess is an optional parameter of the function and usually, you won’t need it. But the guess usually is 10% if you don’t enter it. Although you don’t need the guess, it does have a part to play in your IRR calculation. Sometimes there may be multiple correct IRR outputs and to find the most correct IRR Excel uses the default guess if not entered.
The first investment is always written in negative as it is an outflow for you. Otherwise, the cash inflows are written as positive numbers. You can see this similarly in our sample dataset in the image below:
How To Calculate The IRR
As the IRR function assumes that you have equal time between your cash inflows you only need to select the series of cash flows while using IRR. To calculate the IRR in Excel, you have to perform the following steps:
1. Choose any cell in the worksheet other than your dataset and the formula bar type in
=IRR()
2. Click inside the parenthesis and then using your mouse drag and select your cash flow range as shown in the picture.
3. And done. As seen in the output picture, you will get your IRR. In our case, the IRR equates to 13.50%.
But what if get a decimal number as shown in the picture below?
Well, don’t worry. You can just simply change the data type to percentage in the home tab as we have shown in the picture below.
And now you have your IRR In a percentage format.
The XIRR Function
Like the IRR function, the XIRR function stands for the Extended Internal Rate of Return. As we have said before, the IRR function calculates the Internal Rate of Return without considering external factors like Cash Flows and Discount rates. But the XIRR function calculates the Internal Rate of Return with these external factors in mind.
With the XIRR function, you can assign dates to your cash flow making it much superior when dealing with investments made throughout the year. So, in contrast to the IRR function, the XIRR function calculates ROI more accurately by taking in the Cash flow and their corresponding dates. Although, you have to remember that XIRR only takes valid Excel dates.
The formula for the XIRR function in Excel is:
=XIRR(values, dates, [guess])
Formula Breakdown
Here, similar to the IRR function, values denote your range of cash inflows and it is a required parameter. Dates is another required parameter of the function and the XIRR function requires it to provide a better Internal Return Rate in terms of the investment period. For XIRR to work correctly, the date format needs to be one of the Excel date formats.
The guess is an optional parameter of the function similar to the IRR it is usually 0.1 or 10%. The guess is your anticipated return rate. Although you don’t need the guess, it does have a part to play in your XIRR calculation. If Excel can’t find the return rate, you can put in your anticipated return rate to help Excel do the calculation.
Sometimes there may be multiple correct IRR outputs and to find the most correct IRR, Excel uses the default guess if not entered. The XIRR function will calculate 100 iterations to find the rate of return.
How to Calculate The XIRR
To calculate the XIRR you have to put in both the dates of the cash inflow and the cash inflow amounts. To demonstrate the process we will use the same datasheet that we used for using the IRR function. Just follow along the process:
1. Choose any cell in the worksheet other than your dataset and the formula bar type in
=XIRR()
2. Click inside the parenthesis and then using your mouse drag and select your cash flow range as shown in the picture.
3. Then add a comma and similarly using your mouse, drag and select the dates of the cash inflows.
4. And done. As seen in the output picture, you will get your XIRR. In our case, the IRR equates to 13.49%.
Now we have the IRR for the annual cash inflow.
But what if we wanted to calculate the internal rate of return on a monthly basis. Then we can calculate the monthly XIRR. To do this we will use the following formula:
=(1+XIRR(Cashflow_cell_range, Cashflow_date_Range))^(1/12)-1
So to do this, follow the following process.
1. Similar to what we did before, choose any cell, and in the formula bar type in:
=(1+XIRR())^(1/12)-1
2. Then similarly in the parenthesis after the XIRR click with your mouse and drag and select the Cell range containing the cash inflow amounts.
3. Add a comma, and drag and select the cash inflow date range similar to the picture.
4. And you are done. You get the monthly XIRR for your data set.
5. You can still get the annual XIRR using the previously mentioned XIRR formula as shown here in the picture.
=XIRR()
In the picture, you can see that if we calculate the monthly XIRR it is a huge deviation from the annual XIRR.
But for one final check, we did an IRR function calculation on monthly data to show you the difference.
Here we can see that the IRR produces a similar result to the annual XIRR calculation. This shows that if you do have the dates for your cash inflows, you should use XIRR to calculate a timed rate of returns.
4 Things to Know About The IRR And The XIRR Function
You should know the following things to use the IRR and the XIRR function more effectively.
1. Your cash inflow range must have one negative and one positive value. The negative values represent the cash outflows while the positive values represent the cash inflows.
2. The first value in your cash flow column should be negative. This is because your first cash inflow is almost always an investment Excel does not discount your initial investment. It brings the subsequent payments back to your initial investment date and discounts them based on a 365-day basis.
3. The dates entered in the cash inflow column must be valid Excel dates. Without a valid Excel date, the functions will return an error. So if you have put in the date in a different format, just change the format to a known Excel date format. Or, if Excel is intelligently changing your date format just check our guide on how to stop Excel from automatically doing that.
4. The XIRR function is designed especially to calculate the IRR with uneven cash flow dates. But the IRR function works with dates where the cashflow dates are unknown.
Difference Between IRR and XIRR
To simplify things, the primary difference between IRR and XIRR is that XIRR considers external factors while the IRR function does not. IRR does not consider when the Cash flow took place. But the XIRR function takes the times of the cash flow into account. This enables the XIRR function to return a more accurate view of your return on investment. But the IRR function just provides you with the rate of return and sums it up to annual periods.
This difference makes the XIRR function more effective at calculating your return on investments. But in case you specifically need to only calculate your rate of return for a yearly period, you can use the IRR function.
So the basic difference between XIRR and IRR functions comes down to accuracy. When you have the information on how frequently the cash inflow takes place in a project you can use XIRR to gain a more accurate calculation of your rate of return. But if you don’t have the dates for those cashflows, you can just choose to use IRR.
We can see this in the following examples. Here we used IRR and XIRR on the same dataset but with varying cash inflows.
If you have cash inflows that come in regularly, then using the XIRR and IRR function gives you a close result. You can see this in the following picture.
But if you have unequal intervals between the cashflows then the different functions will give you results with a noticeable gap. As seen in this image.
So, if you do have cash inflow dates, then we would suggest you use the XIRR function for a more accurate calculation.
IRR vs XIRR Face to Face
Here is a simple comparison table for you to quickly glance at the differences between IRR and XIRR. We have put XIRR and IRR face to face so that you can choose the right function for you.
| IRR | Category | XIRR |
| Internal Rate of Return. | Full form | Extended Internal Rate of return. |
| Less accurate. | Accuracy | More Accurate. |
| Two parameters: Values and Guess | Parameters | Three parameters: Values, Dates and Guess |
| Does not consider external factors. | External factors | Considers external factors. |
| Calculates Internal rate of return estimating yearly intervals between inflows. | Consideration of Cash inflow dates. | Uses dates of cash inflows in the calculation. |
| When there are equal intervals between cash flows | Better use case | When there are unequal intervals between cash inflows. |
As you can see there are some basic differences between the two functions. But we would suggest you use the XIRR function when you can. This will give you more accuracy in your calculation and more flexibility to calculate the IRR of a particular timeframe. .
Troubleshooting Problems
When working with the IRR and the XIRR function you may come across two errors. These errors will not let you calculate your rate of return. But fear not. We will tell you why these problems may occur and how you can resolve them.
#VALUE! Error
#VALUE! Error is a very common error that you may face while working with IRR or XIRR. You will often get this error because of silly typing mistakes. You may get the error due to these reasons:
1. As we discussed before, XIRR works with valid Excel date formats. So, if any of the date formats you used are not valid date formats, just change them to a date format like dd-mm-yy, or mm-dd-yy, and you’re set. You will not get the #VALUE! Error.
2. Again, if any of your supplied values are not numbers then you will get the same error. So just change any silly typos, or letters that you may have put in place of the numbers and the error will go away.
#NUM! Error
The #NUM! Error is another common error that you may come to see in your Excel file when you are working with XIRR. Here’s how you can remove this error.
1. If you have mistakenly put a subsequent date earlier than the first date of your cash inflow, you will get this error. To fix this, just check if the first date in the dates column is the earliest date of your cash inflow.
2. You may face this error if you have different lengths of columns for your cash inflow dates and your cash inflow. Add the missing date or cash amount and it will fix the error.
3. You may also face this error if your cash inflow column does not have at least one positive and one negative value. Just check, if you have put in a negative sign before cash outflows and if all the inflows are positive. And you’re set.
4. Finally, we discussed previously that the XIRR function calculates 100 iterations to estimate the IRR. Sometimes, you may get a #NUM! Error if Excel is not able to calculate the IRR even after 100 iterations. You can fix this by changing the guess. This will trigger Excel to use your guess input instead of the default 0.1.
Concluding Words
If you want to calculate the potential of your investments or get an estimation of your internal rate of return, your go-to function to use in Excel would be IRR or XIRR. But, although both functions should return the internal rate of return, the IRR will only chalk your rate or return to an annual estimation.
So if you have cash inflows that did happen yearly, you can estimate your rate of return with the IRR function. But if you do have cash inflows with irregular intervals, you would be better served with the XIRR function which will help you calculate a timed and accurate internal rate of return estimate.
Similar Post: