Practice free

XIRR function

XIRR returns the annualized internal rate of return for cash flows on specific dates. It suits irregular investments, such as deposits and withdrawals on arbitrary days, where IRR does not fit.

Example

Read it as a sentence: find the annual return rate for the cash flows in N1:N5 on the dates in P1:P5, which gives 0.506820685.

Practice this in the app

Syntax

XIRR(values, dates, [guess])

Arguments in [square brackets] are optional.

values (required)
Cash flows, at least one negative and one positive.
dates (required)
Date for each cash flow; the first is the start.
guess (optional)
Starting estimate; 0.1 if omitted.

Returns: Annualised internal rate of return for dated cash flows.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=XIRR({-100,110},{45292,45658}) 0.099713582 Array constants
=XIRR({-100,110},{"2024-01-01","2025-01-01"}) 0.099713582 Text dates
=XIRR(Data!N1:N5,Data!P1:P5) 0.506820685 Dated flows
=XIRR(Data!N1:N5,Data!P1:P5,0.2) 0.506820685 With guess
=XIRR({100,200},{45292,45658}) #NUM! All positive
=XIRR({-100,"a"},{45292,45658}) #VALUE! Text values
=XIRR(Data!N1:N5,Data!Q1:Q5) #NUM! First date not earliest
=XIRR(Data!N1:N5,Data!P1:P4) #NUM! Size mismatch

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • No date may come before the first date.
  • Use real dates or serials, not text.
  • May return #NUM! if it fails to converge; supply a guess.

Where it works

Excel
XIRR is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .