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.
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,
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.
Using these investment details, we will calculate the ROI.
First, we will calculate the IRR percentage.
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.
[Guess] is an inflation rateInflation RateThe rate of inflation formula helps understand how much the price of goods and services in an economy has increased in a year. It is calculated by dividing the difference between two Consumer Price Indexes(CPI) by previous CPI and multiplying it by 100., so automatically, it takes 10%. Let’s leave it.
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,
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.
For values, the argument chooses cells from C2 to C6.
The XIRR function is different from the IRR function, and the second argument dates, i.e., the dates for cash flowsCash FlowsCash Flow is the amount of cash or cash equivalent generated & consumed by a Company over a given period. It proves to be a prerequisite for analyzing the business’s strength, profitability, & scope for betterment. . So choose the date range of cells from B2 to B6.
As usual, leave the last argument.
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 Key Differences
- Cash Flows: It is one of the primary differences between these two functions. IRRIRRInternal rate of return (IRR) is the discount rate that sets the net present value of all future cash flow from a project to zero. It compares and selects the best project, wherein a project with an IRR over and above the minimum acceptable return (hurdle rate) is selected. 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.
- Accurate Result: Unless there is a standard cash flow XIRRXIRRThe XIRR function, also known as the Extended Internal Rate of Return function in Excel, is used to calculate returns based on multiple investments made for a series of non-periodic cash flows. 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 functionIRR FunctionThe internal rate of return, or IRR, calculates the profit generated by a financial investment. IRR is a built-in function in Excel that calculates the IRR using a range of values as an input and an estimate value as the second input. or else we need to track dates and use XIRR function.
IRR vs XIRR Comparative Table
|IRR 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.
|IRR 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.
|IRR 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.
|With 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.
|Since 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.
This article is a guide to IRR vs XIRR. Here we discuss internal rate of return & extended IRR, key differences, comparison, examples & downloadable template. You may also have a look at the following articles –