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. |
Read More: Excel XNPV vs NPV: Comparison with Examples
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).
Read More: How to Convert Percentage to Basis Points in Excel
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.
Read More: How to Calculate Profitability Index in Excel
Download Working File
Related Articles
- How to Calculate Tracking Error in Excel
- How to Calculate WACC in Excel
- How to Calculate Mileage Reimbursement in Excel
- How to Calculate Time Weighted Return in Excel
<< Go Back to Excel Formulas for Finance | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
