IRR vs XIRR

Differences between IRR vs XIRR

IRR determines the internal rate of return of by only considering the discount rate and ignoring the cost of capital, risk-free rate, etc. In other words, it makes the net present value (NPV) of all cash flows equal to zero in a discounted cash flow analysis. Whereas, the XIRR function determines the value of an investment or project feasibility when the cash flows are not periodic. It helps one accurately model cash flows over variable time intervals while considering the discount rates and corresponding dates.

Both IRR and XIRR are used in financial modeling to evaluate investments.

What is IRR?

The IRR function is used to calculate cash flows at regular intervals. It provides an annualized rate of return. Since it can be used to compare the profitability of investments at standard time intervals with regular cash flow, which may not always be the case, it is considered less accurate.

IRR Formula with Example

By now, we all know that the IRR is used to measure the profitability of an investment. So, by the name itself, we know that IRR considers internal factors and is used to compare and choose between capital projects.

The IRR Formula in Excel is as follows:

The IRR function uses the following arguments:

• Values (mandatory) – This is an array of values representing the cash flows, which include investment and net income values. These values can be provided in a range of cells which can be given here.
• [Guess] (optional) – A number the user guesses is close to the expected internal rate of return. The default value is 0.1 (=10%).

Let us look at an example to understand IRR. Suppose a company is thinking of investing an amount of $2,00,000 on a project. Let us term it as Project A. This project can generate $1,00,00 cash flows in the first year after tax. It can grow by $30,000 over the next three years. Let us calculate the IRR. We also have a discount rate of 15%. It is the rate of return of an investment Project B considered as an alternative. 

Step 1: The NPV function in Excel calculates the present value of an investment using a series of future cash flows and the discount rate. It subtracts the initial investment from it. So here, let us apply the NPV function in cell C8.

=NPV(D2,C3:C6)+B2

Here, D2 is the discount rate, C3 to C6 represents the cash flows, and the initial investment B2 should be subtracted from it. We use the + sign since it is already entered as a negative number in red in B2.

Step 2: The NPV is a positive value which shows that Project A’s rate of return exceeds the discount rate of Project B, thereby indicating that Project A is a better investment.

Step 3: Now, let us calculate the IRR of project A in cell C9 using Excel’s IRR formula.

=NPV(D2,C3:C6)+B2

Thus, this shows that both Project A and Project B, if offered a discount rate of 17%, would yield equal returns.

What is XIRR?

On the other hand, XIRR can measure the profitability of investments with varying cash flows, x and specific dates can be assigned to the cash flow. This scenario is realistic and makes it more accurate. It considers the initial investment and rental income or capital gains.

XIRR Formula with Example

The Excel formula for XIRR is:

=XIRR(values, dates,[guess])

Arguments

• Values (mandatory) – This is an array of values representing the series of cash flows. Usually, a range of cells is given here. 
• Dates (mandatory) – These dates correspond to the cash flows. The initial investment’s date must be the first entry here. Rest can be in any order. 
• [guess] (optional) –This is a guess or estimate of the IRR. If not provided, Excel takes the default value of 10%.

Example

Let us look at an example for XIRR. Suppose you are investing an initial amount $1500 in an investment plan. You expect to get cash flows at the end of 6 months, one and half years, two years, three years, and five years.

Step 1: Now, let us calculate the XIRR or Internal Rate of Return for this non-periodic investment. We use the formula below in cell B9:

=XIRR(B2:B7, A2:A7)

Here,

• B2:B7 are the cash flows.
• A2:A7 are their corresponding dates.

Step 2: You get the XIRR value as follows.

IRR vs XIRR – Key Differences

Let us look at some of the key differences in IRR vs XIRR.

The main difference in IRR vs XIRR is that IRR deals with cash flows at regular intervals, while the XIRR deals with irregular cash flows.

Both give an overview of the profitability of an investment.

However, in IRR, we do not enter the dates for calculation. Hence, it rolls out the cash flows in annual periods. For XIRR, we can enter specific dates for irregular cash flow. Therefore, if you know the exact date of payments, it is better to use the XIRR.

Let us look at an example where we compare the IRR and XIRR for the same cash flows. Given below is the investment made by a company with regular cash flows at fixed periods. Now, let us apply the formula for XIRR and IRR and check the internal rate of return to find the difference between IRR vs XIRR.

Step 1: Let us apply the IRR formula in cell B8.

=XIRR(B2:B6)

Where B2:B7 are the cell references corresponding to the cashflows.

We get the following result.

Step 2: Now, apply the formulain cell B9.

=XIRR(B2:B6, A2:A6)

Let us look at the result.

As you can see, the values are very close if the payments are at regular time intervals. Let us look at what happens when the payment periods are different. Let us alter the dates in the table and apply the formula.

Thus, we notice a significant difference for irregular time intervals. Therefore, the XIRR formula is more accurate since it calculates for specific periods.

IRR vs XIRR Comparative Table

IRR	XIRR
With IRR, it is assumed that all the time periods in a series of cash flows are equal.	With XIRR, you can assign a date to each individual cash flow.
This function finds the internal rate of return (IRR) for monthly, quarterly or annual periodic cash flows.	use this function to calculate IRR for cash flows that are not necessarily periodic.
The formula for IRR in Excel is: =IRR (values, [guess]) Here, we do not have to mention the date of the cash flows.	The formula for XIRR in Excel is: =XIRR (values, dates, [guess]) Here, XIRR requires two inputs: the cash flows and their corresponding dates.
Best used for generic calculations involving annual cash flows	Used for irregular cash flows, for example, an outlier-sized deal.
Easy to calculate with fewer details required	Requires more parameters like the exact date of cash-flow
Not very useful in a practical setting as cash flows can be irregular.	More preferred formula for all financial modeling.

Important Points

• Note that do not leave a blank cell for periods without cash flows. It may cause errors. Fill up the cell with zero.
• Both IRR and XIRR can cause #NUM errors if the table does not contain at least one positive and one negative number.
• You can use the NPV formula in Excel to verify the accuracy of your IRR.
• IRR and XIRR take the guess argument as 10% by default unless specified.

Download Template

This article must help understand the IRR vs XIRR, with its formula and examples. We can download the template here to use it instantly.

Recommended Articles

Guide to IRR vs XIRR. Here we explain their meaning, formulas, top differences with comparative table and differences. You may learn more from the following articles –

• Accounting Number Format in Excel
• TODAY Excel Function
• COUNT Excel Function