IRR vs XIRR in Excel: Which One Should You Use?

IRR vs XIRR comes down to dates. IRR assumes your cash flows arrive at equal intervals, like once a year, and returns the rate per interval. XIRR reads the actual date of each cash flow and returns a yearly rate. If your payments come on irregular dates, use XIRR; if they are evenly spaced, both work. This guide is for analysts, finance students and job seekers who need to pick the right function and explain the result.

This article explains how the functions calculate. It is not advice on any investment.

IRR vs XIRR at a glance#

Both functions find an internal rate of return: the discount rate at which the cash flows are worth exactly zero today. They differ in how they measure time.

The optional guess is a starting point for Excel's search. It defaults to 10%, and you rarely need it.

How IRR works#

IRR treats the list of values as a timeline with equal steps. The first value is at period 0, the next at period 1, and so on. It then finds the rate where the discounted values add up to zero.

Take a project that costs 10,000 today and returns 3,000, 4,000 and 5,000 at the end of each of the next three years. With the values in B2:B5:

=IRR(B2:B5)

The result is 8.90% per year. You can check it by hand: 3,000 / 1.089 + 4,000 / 1.089² + 5,000 / 1.089³ is very close to 10,000, so the total with the -10,000 is about zero.

If the cash flows are monthly, IRR returns a monthly rate. For -1,000 followed by twelve monthly payments of 90, IRR gives 1.20% a month. To turn that into a yearly figure, use =(1+B15)^12-1 (where B15 holds the IRR), which gives 15.45%. Multiplying by 12 gives 14.45%, a nominal rate that ignores compounding. The percent change lesson is a good refresher on working with rates like these.

How XIRR works#

XIRR adds a second list: the date of each cash flow. Each value is discounted by the number of days since the first date, divided by 365. The result is always an annual rate.

=XIRR(B2:B5, A2:A5)

Dates in Excel are numbers (days since 1 January 1900), which is why XIRR can subtract them. If that is new, the date basics lesson shows how Excel stores dates.

The formula XIRR solves is:

Sum of  value / (1 + rate) ^ ((date - first_date) / 365)  =  0

The left side is called XNPV, the dated version of net present value. XIRR is the rate that makes XNPV zero, in the same way that IRR is the rate that makes NPV zero. The guide to the NPV function and the comparison of NPV vs PV explain discounting in more detail.

Side-by-side example: the same cash flows, two answers#

The difference shows up as soon as the dates are uneven. Here is one investment in A1:B5:

The money comes back over about two years: 166, 425 and 745 days after the start.

IRR spreads the payments over three years, so it thinks you waited longer for your money and reports a lower return. XIRR sees that everything came back within about two years. The gap is almost five percentage points, and IRR gives no warning.

You can confirm the XIRR answer with the dated discounting formula. At 8%, which is below the XIRR, the discounted cash flows add up to 656.49:

=SUMPRODUCT(B2:B5/(1+E1)^((A2:A5-A2)/365))

with 8% in E1. A positive result means the true rate is higher than 8%. Put 12.61% in E1 and the result is very close to 0. That is exactly what XIRR finds for you.

Now compare the evenly spaced project from earlier. With dates of 1 January 2024, 2025, 2026 and 2027, XIRR returns 8.88% against IRR's 8.90%. The tiny gap is because 2024 is a leap year with 366 days, while XIRR always divides by 365. For truly even flows, the two agree closely.

When to use IRR#

IRR is the simpler choice when the timing is regular:

• Yearly project models where each column is one year and cash flows are assumed at year end.
• Monthly schedules, such as a loan or lease, as long as you convert the monthly rate to an annual one.
• Teaching and interviews, where the question says "assume annual cash flows".
• Quick checks when you don't have dates, only a sequence of amounts.

A special case: with just a start value and an end value, IRR equals the compound annual growth rate. The CAGR formula guide shows that calculation with POWER.

When to use XIRR#

XIRR is the right choice whenever real dates matter:

• Investments with irregular deposits and withdrawals, such as adding money at different times of year.
• Private deals and projects where payments land on specific dates.
• Actual results, as opposed to a plan: real invoices and receipts rarely fall exactly 12 months apart.
• Comparing returns over different time spans, because XIRR always gives a yearly rate.

The same idea applies to other dated functions: XNPV is the dated version of NPV. For growth without a series of cash flows, the FV function works forward from a known rate instead.

Common IRR and XIRR mistakes#

Most wrong answers come from the inputs, not the functions:

1. Using IRR on irregular dates. As the example shows, the result can be far off. Use XIRR.
2. No sign change. The first cash flow (money out) must be negative and at least one later flow positive. All positive or all negative gives #NUM!. The guide to the #NUM! error lists other causes.
3. Reading a monthly IRR as yearly. 1.20% a month is not 1.20% a year. Convert it with (1+rate)^12-1.
4. Dates stored as text. XIRR needs real dates. If dates are left-aligned, convert them with DATEVALUE first, or XIRR returns #VALUE!.
5. First date not the earliest. XIRR returns #NUM! if any date comes before the first one.
6. Missing zero periods in IRR. If a year has no cash flow, type 0 in that cell. Leaving it out shifts every later flow one period earlier.

Our recommendation#

Default to XIRR whenever you know the dates of your cash flows. It handles uneven timing, always returns an annual rate and matches how money really moves. Use IRR for clean models with equal periods, such as a five-year plan with one column per year, or when a test question asks for it by name. If the two give very different answers on your data, trust XIRR and check the dates.

Whichever you use, sense-check the result with a discounting formula like the exercise on this page. A rate is only as reliable as the cash flows and dates behind it.

Practise it#

IRR and XIRR depend on two everyday skills: working with dates as numbers, and reading rates of change. Practise date basics in a live spreadsheet to see how Excel subtracts dates, then practise percent change so rates and growth feel natural. Then try the XNPV exercise on this page and change the rate in E1 until the result reaches zero.