The return from investments, whether from stocks, properties, and other assets, is the most crucial information any investor, entrepreneur, CEO and management need to know. In many cases, investors will measure their return via the Internal Rate of Return (IRR) Formula. In Excel, you will find two different kinds of formulas to calculate the IRR, the IRR vs XIRR formulas. For obtaining a general understanding of how IRR analysis works, we refer to this article [link]. In this article, we will explain the main differences from a financial modeling point of view when using the IRR vs XIRR Formula in Excel.

IRR Formula in Excel

The below example illustrates how you can use the IRR formula in Excel to calculate the Internal Rate of Return. Using Excel’s IRR function, the IRR for the investment below is 12%.

Important to note here is that the IRR formula assumes each cash flow event to occur annually, meaning the cash flow events lie 1 year apart. This also means the above calculation will only be correct if that assumption is true.

Now the problem in practice is that many times cash flows do not occur only once a year or annually but at different times and dates during the year. The question then is how can you calculate the IRR with Excel in those situations?

The answer is, if you need to calculate IRR in Excel whereas cash flows occur at different times during the year, then you need to use the XIRR formula to obtain more precisions in your calculations.

How does Excel’s XIRR function work?

XIRR is the abbreviation for the Extended Internal Rate of Return. The Microsoft Excel XIRR function is a built-in financial function that calculates the IRR of a series of cash flows that occur at IRREGULAR intervals defined by specific dates.

The XIRR Formula in Excel has the following components:

• Values – This is a required parameter that refers to a series of cash flows that correspond to a scheduled payment date. This represents the series of cash flows from which the XIRR is calculated.
• Dates – This is a required parameter that refers to the schedule of payment dates that corresponds to the cash flow event. Dates must be entered in a valid date format in excel.
•  [Guess] – Similar to IRR, this is an optional parameter that represents a number that you believe is close to the XIRR as a starting point in the iterative calculations by Excel.

Let us continue with our prior example but instead of assuming an annual cash flow, the cash flows will be paid out at more precise dates using the XIRR formula.

As you can see above, putting exact payment dates and using the XIRR formula affects the IRR. The IRR now is 11% instead of 12% when using more precision as can be reflected in the XIRR formula. This proofs the point that timing matters in IRR calculations.

Important to note also the following when using the XIRR formula:

• The cash flows must contain at least one negative cash flow and at least one positive cash flow representing the initial investment cost and the return. If all entered cash flows are positive, the XIRR formula (similar to the IRR formula) will return a #NUM error.
• In case the dates of later cash flows precede the starting date, Excel will show a #NUM error as well.
• In case entered dates are not valid dates, Excel will show a #VALUE error.
• A word of caution when using XIRR function, do take note of leap years (2020, 2024, 2028) as this will affect the number of days; from 365 to 366 days.

IRR vs XIRR: Which is better?

Clearly, XIRR offers a more precise way of calculating the IRR than the XIRR formula. The main difference between IRR vs XIRR is that the result can be different when the timing of the cash flows is too far apart from an annual cash flow pattern.

In analyzing and assessing investment and capital project proposals, it is extremely crucial to understand the difference between IRR vs XIRR. The IRR formula will hold when you calculate business cases where you have reasons to believe that the annual cash flow assumption will hold. It’s, therefore, quicker and easier to calculate than when using XIRR.

XIRR is especially relevant to use for short-term investment horizons with high IRRs where using the exact dates makes a big difference in the IRR calculation. XIRR requires precise date estimations which sometimes are difficult to obtain or portrays a level of precision which by itself might not be available at the time of calculation.

The bottom line is when deciding which one to use, IRR vs XIRR, you will have to think when the cash flows occur and if by using the exact dates will make a big difference in your IRR calculation. If the answer is yes, then you are better off using the XIRR formula.