IRR vs XIRR

Publication Date :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

arrow

Difference Between IRR vs XIRR

The IRR function calculates the internal rate of return on the cash flows after considering the discount rate, and helps evaluate the return on investment over some time.

The XIRR function is the extended internal rate of return that considers the cash flows, discount rates, and the corresponding dates to measure the return accurately.

Suppose you have cash flows in cells A1 to A5 (-1000, 200, 300, 400, 500). Use "=IRR(A1:A5)" in A6 to find the investment's internal rate of return.

Similarly, if you have irregular cash flows and specific dates in cells A1 to A5 and B1 to B5, use "=XIRR(A1:A5, B1:B5)" in C1 to calculate the investment's annualized internal rate of return.

IRR vs XIRR
Table of contents
  • The IRR function calculates the internal rate of return on the cash flows, and the XIRR function is the extended internal rate of return that considers the cash flows and discount rates and the corresponding dates.
  • To know the internal rate of return, we need the dates for each of our cash flows because this is where our period will be considered, and an accurate ROI percentage can arrive.
  • Both the IRR and XIRR functions have the mandatory argument “values”. However, the XIRR has another mandatory argument, “dates”, which makes it different from IRR.

What Is IRR?

IRR calculates the “Interest Rate of Return” on cash flow based on the discount rate and estimates the return on investments that arise periodically.

IRR Formula With Example

The syntax of the Excel IRR formula is,

IRR Formula

The arguments of the Excel IRR formula are,

  • values: It is a mandatory argument. It is an array or reference of a table for which IRR calculation is done. It must have one positive and one negative value.
  • guess: It is an optional argument. It is a number that is guessed, which is nearest to the result of IRR.

Let us understand its working with an example.

Mr. A has invested some money in one of the projects, and below are the details we have for his investment.

IRR vs XIRR Example 1

Using these investment details, we will calculate the ROI.

First, we will calculate the IRR percentage.

IRR vs XIRR Example 1-1

The first argument of the IRR function is to select the values, i.e., what are the cash inflows and outflows, so choose the range of cells from C2 to C6.

Example 1-2

is an inflation rate, so automatically, it takes 10%. Let’s leave it.

IRR vs XIRR Example 1-3

So, the ROI for Mr. X is 17.13%.

What Is XIRR?

The Extended Internal Rate of Return, or the XIRR function, calculates the internal rate of return for an irregular series of cash flows or instalments. This cash flow is not periodic, unlike other payments.

XIRR Formula With Example

The syntax of the Excel XIRR Formula is,

XIRR Formula

The arguments of the Excel XIRR Formula are,

  • values: It is a mandatory argument. It is the array of values that represent the series of cash flows.
  • dates: It is a mandatory argument. It is the irregular, periodic series of dates that correspond to the given array values.
  • guess: It is an optional argument. It is an initial guess of what the IRR will be.

Now, let us take the above example to use the XIRR function.

IRR vs XIRR Example 1-4

For values, the argument chooses cells from C2 to C6.

Example 1-5

The XIRR function is different from the IRR function, and the second argument dates, i.e., the dates for cash flows. So choose the date range of cells from B2 to B6.

Example 1-6

As usual, leave the last argument.

IRR vs XIRR Example 1-7

With the XIRR function, we have got more ROI percentage because, with this function, we have taken into consideration specific date scheduled cash flows.

IRR vs XIRR Infographics

IRR vs XIRR Infographics

IRR vs XIRR Key Differences

  1. Cash Flows: It is one of the primary differences between these two functions. IRR does not understand when the actual cash flow happens, so it assumes it as a year, but with the XIRR function, it considers dates when cash flow happens.
  2. Accurate Result: Unless there is a standard cash flow XIRR is always the best option to evaluate an investment, so if our cash flows are happening at any specific date, we can rely on the IRR function or else we need to track dates and use XIRR function.

IRR vs XIRR Comparative Table

ParticularsIRRXIRR
MeaningIRR is the basic version of calculating ROI for an investment for a series of cash flows.RR is the improved version of IRR, where XIRR considers a scheduled series of cash flows.
Formula SyntaxIRR has only two parameters: values and guess. Of these two parameters, values are a mandatory argument, and guess is an optional argument.XIRR has three parameters: values, dates, and guess. Values and dates are mandatory arguments for these three parameters. In comparison, guess is an optional argument.
Inflation RateIRR considers the inflation rate with its guess argument. It takes 10% (0.1) as the inflation rate by default. However, the user can give their rate of inflation.XIRR, too, takes into consideration the inflation rate with its Guess argument. It takes 10% (0.1) as the inflation rate by default. However, the user can give their rate of inflation.
Cash FlowsWith IRR, we do not mention the specific date of cash inflow or outflows.With XIRR, we provide the dates for cash outflow and inflow, so this is where XIRR is better than the IRR function because it considers scheduled cash flows.
Better EvaluatorSince IRR does not consider dates of cash flows; we may not get accurate details unless cash flows are at the same periods.XIRR considers dates of cash flows, so the period of cash flows is treated accurately to get the accurate internal rate of return.