ExcelInsider Team
IRR and XIRR are both financial metrics that people use to decide the best project for them to invest in. IRR is a tedious calculation, and XIRR is even more so. Fortunately, Microsoft Excel provides dedicated functions for calculating XIRR and IRR, which you can use without needing to know how to perform these calculations manually. In this article, we will learn how to use XIRR vs IRR in Excel so that you can calculate these metrics and take the best financial decision for your project.
➤ Use the following formula to calculate IRR:
=IRR(B2:B13)
➤Replace B2:B13 with your cash flow. Remember to keep your investment in the mix.
➤To calculate XIRR, use the formula from below:
=XIRR(B2:B13,A2:A13)
➤ Replace A2:A13 with the dates of the returns, and B2:B13 with the cash flow.
In order to make a proper investment decision, calculating IRR and XIRR is important. Microsoft Excel makes it easy to calculate these metrics if you know about the returns before investing. We will learn those metrics and how to use the functions provided by Excel to calculate those values.
What is XIRR in Excel?
XIRR is a function that can be used in Excel to calculate XIRR (the financial metric). XIRR returns the internal rate of return while considering the time it takes for the cash flow to return from the project. The function requires 2 to 3 parameters in Excel. Here is the syntax of XIRR:
=XIRR(values, dates, [guess])
The first parameter is the series of cash flows we get from the project over a span of time. We will insert the cell reference of those values in this cell. In the second parameter, the cell references of the dates when the cash was received need to be inserted. Finally, the third parameter is an optional parameter that requires a guess of the XIRR output. If you don’t put a guess, Excel will assume it is 0.1 (10%).
The cash flow must contain a negative cash flow and a positive cash flow at least, otherwise Excel will throw the #NUM! Error. For the dates, they must be stored in Excel date formats in those cells and not in text; otherwise, we will see the #VALUE! Error. No date should precede the starting date as well. The number of dates should be the same as the number of cash flow values.
What is IRR in Excel?
IRR, or Internal Rate of Return, is the discount rate that, when applied to the net present value, returns 0. The IRR function in Excel is a simpler version of XIRR, and it considers the cash flow occurs at a regular interval. The function requires only one parameter to work, but you can insert two if you need to. Here is the syntax of IRR:
=IRR(values, [guess])
Here, the first parameter is the series of cash flows from the project you want to calculate the IRR of. The second parameter is a guess of IRR, which is not usually required. But, you might want to insert something if it’s not close to 10%.
Here is a comparison between XIRR and IRR functions in Excel:
| Difference | XIRR | IRR |
|---|---|---|
| Full Form | Extended Internal Rate of Return | Internal Rate of Return |
| Syntax | =XIRR(values, dates, [guess]) | =IRR(values, [guess]) |
| Explanation | Considers irregular time intervals between cash flows | Assumes equal time intervals for every return |
| Usefulness | More useful with risky investments like real estate or loans | Usable for simple projects like FDRs |
Calculating IRR in Excel
To demonstrate the calculation, we have a table with 2 columns. The first column contains the dates of the cash flow, and the second column contains the actual cash flow. The first cash flow is negative because it’s the investment amount. The dates will not help calculate IRR, but they will be useful in XIRR. In the E2 cell, we will calculate the IRR.
➤ In the E3 cell, write the following formula and press Enter:
=IRR(B2:B13)
Measuring XIRR in Excel
This time, we will require both the dates and the cash flows. Follow the steps below:
➤ Go to the E3 cell.
➤ Insert the following formula:
=XIRR(B2:B13,A2:A13)
➤ Here, the number is shown in decimal instead of percentage. We need to change it to a percentage. We didn’t need to change the number type for IRR because Excel does that automatically. It does not work the same way for XIRR.
➤ In the Home tab of the ribbon, go to the Number group, and press on the percentage (%) icon.
➤ You can also press Ctrl + Shift + % instead of clicking on that icon.
Frequently Asked Questions
What does 20% XIRR mean?
That means that the investment will provide a 20% return on investment per year. As it was calculated using XIRR, the calculation has considered the time of the cash flow and the amount of money that it will return.
Is Excel’s IRR accurate?
It is more accurate than doing it manually. When you calculate IRR manually, you do multiple calculations for NPV and stop when you feel like it’s okay. But Excel keeps calculating until the calculation is accurate within 0.00001%. Therefore, it is more accurate to calculate IRR in Excel than in real life.
Is XIRR more accurate than IRR?
Yes, XIRR considers the time it takes for each return to come, unlike regular IRR. However, XIRR is usually harder to calculate, and in a lot of cases, IRR is enough to understand the state of the potential investment.
Is XIRR better than CAGR?
Sometimes XIRR does provide a clearer picture than CAGR. CAGR, or Compound Annual Growth Rate, is used for investments that provide a constant growth rate. XIRR assumes that the returns will fluctuate and provide outcomes at different times. CAGR bets on the investment providing a steady rate and a smooth return. You can use XIRR for calculations that need CAGR, but not the opposite.
Can XIRR go negative?
Absolutely. If the XIRR of a project is negative, it means that the investment will be a loss, and you should not consider that investment by any means. However, governments sometimes might select a project with a negative XIRR because government projects are usually not executed for profit.
Wrapping Up
In this article, we have learned the difference between XIRR and IRR and how to calculate them in Excel. If you have properly followed the steps provided in this article, you will be able to calculate IRR and XIRR before taking your investment decision. If you have any questions, feel free to leave them below. The Excel file with example data and formulas is available for download. We wish you good luck with your investment.
