IRR vs XIRR

Updated on April 17, 2024
Article byWallstreetmojo Team
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

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.

For eg:
Source: IRR vs XIRR (wallstreetmojo.com)

Key Takeaways

• 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.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

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.
• guessIt 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.

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 . 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.