XIRR vs IRR in Excel: which one to use and why they differ

The short answer

=IRR(values) finds the rate at which the net present value of the values is zero, assuming they are exactly one period apart. The result is a rate per period: per year for yearly cash flows, per month for monthly ones.

=XIRR(values, dates) finds the rate at which the cash flows, discounted by their real timing, add up to zero. Each cash flow is discounted by (1 + rate)^((date − first date) ÷ 365). The result is always an effective annual rate, however far apart the payments are. Use IRR when the periods really are equal, and XIRR whenever they are not.

Step 1: put the dates next to the cash flows

XIRR needs two ranges of the same size: the cash flows in B2:B7 and the dates in A2:A7. The first row is the investment, a negative number on the start date. Every later date must be on or after the first one, and the range needs at least one negative and one positive value. Enter the dates as real Excel dates, not text, or the formula returns #VALUE!.

Column C shows what XIRR does with the dates: the years from the start, =(A3-$A$2)/365. The payments arrive after 0.542, 1.041, 1.751, 2.293 and 2.882 years. That is the timing IRR never sees.

Worked example: one investment, IRR vs XIRR

A $50,000 investment on 16 March 2026 returns five payments on irregular dates. Rows 10 to 17 compare the measures. The figures are fictional.

Worked example: one investment, IRR vs XIRR

	A	B	C	D
1	Date	Cash flow ($)	Years from start	PV at the XIRR
2	16 Mar 2026	-50,000	0.000	-50,000.00
3	30 Sep 2026	6,000	0.542	5,556.87
4	31 Mar 2027	8,000	1.041	6,904.63
5	15 Dec 2027	9,000	1.751	7,025.97
6	30 Jun 2028	10,000	2.293	7,230.07
7	31 Jan 2029	35,000	2.882	23,282.46
8	Total	18,000		0.00
9	Measure	Result
10	Years from first to last cash flow	2.88
11	Periods IRR assumes (rows − 1)	5
12	XIRR (actual dates)	15.19%
13	IRR (one year per row)	8.42%
14	IRR per row, annualized as half-years	17.55%
15	Required return	8.0%
16	XNPV at the required return	7,424
17	NPV at the required return, one year per row	729

This guide is educational, not investment advice. Excel's XIRR and XNPV count 365 days per year, also in leap years.

Step 2: IRR and XIRR on the same cash flows

=XIRR(B2:B7,A2:A7) returns 15.19%. =IRR(B2:B7) returns 8.42%. IRR has no dates, so it counts rows: six cash flows mean five periods, and it reports the rate as if the $68,000 had taken five years to come back. In reality the last payment arrives after 2.88 years, so the money worked much harder than IRR suggests.

You could try to rescue IRR by calling each row a half-year and annualizing: =(1+IRR(B2:B7))^2-1 gives 17.55%. That is closer but still wrong, because the gaps between the payments range from about six to eight and a half months. Only XIRR handles uneven gaps exactly.

Step 3: check XIRR by discounting

The definition gives a simple check. Discount each cash flow at the XIRR over its years from start, =B3/(1+$B$12)^C3, and the results in column D add up to 0.00. The $6,000 received after 0.542 years is worth $5,556.87 today at 15.19%, and the final $35,000 is worth $23,282.46. Together the five inflows are worth exactly the $50,000 invested. =XNPV(B12,B2:B7,A2:A7) gives the same zero in one formula.

XNPV vs NPV at your required return

The same timing issue affects present values. At a required return of 8%, =XNPV(B15,B2:B7,A2:A7) values the investment at $7,424 above its cost. =NPV(B15,B3:B7)+B2, which treats each row as a year, shows only $729. Both are positive here, but on a closer call the wrong function can flip the decision.

Note that NPV discounts its first value by a full period, so the investment made today stays outside the function. XNPV discounts to the first date in its range, so the investment goes inside. Our guide to IRR in Excel covers NPV, IRR and MIRR on a regular yearly project.

When IRR is fine and when you need XIRR

• IRR is fine for a model with one cash flow per year at the same point in each year, such as a DCF model or a project appraisal, and for evenly spaced monthly or quarterly flows. Annualize a monthly IRR with (1+IRR)^12-1.
• Use XIRR for real investments with dated cash flows: a portfolio with deposits and withdrawals, a private equity or real estate fund, a loan with irregular drawdowns, or any project whose payments do not fall on a regular grid.
• Report the same measure when you compare deals. An XIRR of 15% and an IRR of 15% computed on rows are not the same thing.

Common mistakes

• Using IRR on dated cash flows. IRR counts rows, not days. If the cash flows are not exactly one period apart, the result is wrong, often by several percentage points.
• Dates stored as text. Imported dates such as "2026-03-16" typed as text make XIRR return #VALUE!. Convert them with DATEVALUE or Text to Columns.
• A later date before the first date. XIRR discounts everything to the first date in the range. A cash flow dated earlier gives #NUM!, so put the earliest cash flow first.
• All cash flows with the same sign. Without at least one negative and one positive value there is no rate that makes the total zero, and both IRR and XIRR return #NUM!.
• Multiplying a monthly IRR by 12. That gives a nominal rate. To compare it with XIRR, which is an effective annual rate, compound it: (1+IRR)^12-1.
• Putting the investment inside NPV. =NPV(rate,B2:B7) discounts the money you pay today by a full year. Keep it outside, or use XNPV with dates.