XIRR Function

Calculates the rate of return for cash flows on specific dates.

Category

Financial

Available in

All versions, Microsoft 365

Returns

An annual rate of return

Taught hands-on in Financial Modeling · Financial Functions Toolkit module

Overview

XIRR is IRR for real calendars: it finds the annual rate at which XNPV equals zero, so cash flows can fall on any dates.

It's the go-to measure for investments, private-equity deals and projects with uneven timing.

Syntax

=XIRR(values, dates, [guess])

Arguments

ArgumentRequired?What it does
valuesYesThe cash flows, with at least one negative and one positive.
datesYesThe matching dates.
guessNoA starting guess. Omitted = 10%.

Examples

Example 1 — Dated store returns

The store schedule with real dates.

B7fx=XIRR(B2:B6,A2:A6)
AB
1DateCash flow
24/1/2026-$150,000
39/15/2026$40,000
46/30/2027$50,000
53/31/2028$60,000
612/15/2028$55,000
7XIRR20.94%

Much higher than the 12.96% IRR because the cash arrives well before each anniversary.

Common mistakes

  • #NUM! error. Values need both signs, and no date can come before the first one.
  • Text dates. Dates typed as text cause #VALUE!. Convert them with DATEVALUE.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.