XIRR vs IRR in Excel (Differences with Examples)

Basics of Excel XIRR vs IRR Functions

The IRR function (Internal Rate of Return) calculates the internal rate of return for a series of given periodic cash flows. These cash flows do not have to be even, though they should include the initial input investment and the subsequent values that denote the net income. The internal rate of return is utilized, in order to give an indication of the profitability of a potential investment.

The higher the internal rate of return for a particular investment, the more likely it is that the investment will be profitable. The IRR function is similar to the NPV function. The IRR function, works on the premise that the NPV is 0. The IRR input values are spaced at regular, equal intervals. The XIRR function is used to calculate the internal rate of return for cash flows that are not periodic. Consequently, The XIRR value is closely related to the XNPV function and is the internal rate of return calculated for a corresponding XNPV of 0.

XIRR vs IRR Function: Comparisons in Tabular Form

Points of Comparison	The XIRR function	The IRR function
Context	*The XIRR function* (Internal Rate of Return) is used to calculate the internal rate of return for a series of given periodic cash flows.	*The XRR function* is used to calculate the internal rate of return for cash flows that are not periodic
Modus Operandi	*The XIRR function* requires three parameters for working; they are values, dates, and guesses. The values must contain at least one positive value and one negative value. The values must be entered in the correct sequence as well. All subsequent dates must be later than the starting date.	*The IRR function* requires two parameters to work; they are: values and guess. The values must contain at least one positive value and one negative value. The values must be entered in the correct sequence as well.
Expansion Rate	The users don’t have to input the expansion or inflation rate in the argument in general. By default, the function guesses it as 10%(0.1). But if the calculation requires it, the user can manually input the rate.	This function is the same as the XIRR function for *the IRR function*.
Specific Time	To work with this function, users need to mention time or periods to get the return on investment.	To work with this function, users do not need to mention specific times or periods.
Superior Analyzer	*The XIRR function* reviews the time flow or periods for getting the final result. So, it is more dependable than *the IRR function*.	*The IRR function* does not review the time flow or periods for getting the final result. So, the users might not get an accurate result.

Excel XIRR Function: Syntax and Arguments

Summary:

The XIRR function shows the internal rate of return for a sequence of cash flows that are not periodic.
The values entered into the argument must contain at least one positive value and one negative value. Otherwise, it will result in an error.
Available from Excel 2007.

Syntax:

The syntax of the XIRR function is:

=XIRR(values, dates, [guess])

Arguments:

Argument	Required or Optional	Value
Values	Required	Refers to a series of numbers that represent cash flows with a corresponding date schedule.
Dates	Required	Is a schedule of payment dates that corresponds to the cash flow payments.
Guess	Optional	Is a guess at the internal rate of return, if left out Excel assumes a guess value of *0.1* or *10%*.

Return:

The XIRR function shows the internal rate of return for a series of cash flows that are not periodic all the time.

Excel IRR Function: Syntax and Arguments

Summary:

The IRR function shows the internal rate of return for a sequence of cash flows enacted by given numbers as cell values.
The values entered into the argument must contain at least one positive value and one negative value. Furthermore, the values must be entered in the correct sequence as well.
Available from Excel 2007.

Syntax:

The syntax of the IRR function is:

=IRR(values, [guess])

Arguments:

Argument	Required or Optional	Value
Values	Required	Refers to a series of numbers that represent cash flows.
Guess	Optional	Is a guess at the internal rate of return, if left out Excel assumes a guess value of *0.1* or *10%*.

Return:

Returns the internal rate of return for a sequence of given periodic cash flows.

XIRR vs IRR Function: Applicational Differences

We will use the following dataset to illustrate the difference between XIRR and IRR in Excel.

Example 1 – Using the XIRR Function in Excel

Step 1:

Enter the following formula of the XIRR function in cell D10.

=XIRR(D5:D9,C5:C9)

Step 2:

Press Enter to get the Return on Investment (ROI).

Example 2 – Using IRR Function in Excel

Step 1:

In cell D10, enter the following formula of the IRR function.

=IRR(D5:D9)

Step 2:

To see the Return on Investment (ROI), press Enter.

From the two examples, you can see that, by using the XIRR function, you will be able to get more Return on Investment (RO)) percentage (23.7%) compared to the IRR function (16.2%). This is because we have considered a particular date scheduled cash flow in the formula of the XIRR function, which gives you a more accurate result.

Download Working File

IRR Versus XIRR Function.xlsx

<< Go Back to Excel Formulas for Finance | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!