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.
Excel shows 0.506820685
N
P
1
-1000
2024-01-01
2
300
2024-03-01
3
400
2024-10-30
4
500
2025-02-15
5
200
2025-04-01
Data
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.
| Formula | Excel shows | Case |
|---|---|---|
=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.
N
P
Q
1
-1000
2024-01-01
2024-01-01
2
300
2024-03-01
2023-03-01
3
400
2024-10-30
2024-10-30
4
500
2025-02-15
2025-02-15
5
200
2025-04-01
2025-04-01
Data
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 .