The formula
XIRR (annual, uses dates)ExcelGoogle Sheets
=XIRR(B2:B6,A2:A6)In D2 → 21.35%
IRR (per period: here, per half-year)ExcelGoogle Sheets
=IRR(B2:B6)In D3 → 10.14%
IRR annualizedExcelGoogle Sheets
=(1+IRR(B2:B6))^2-1In D4 → 21.32%
✓ Tested. All 3 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Cash flow | ||
| 2 | Jan 1, 2026 | -10,000 | 21.35% | |
| 3 | Jul 1, 2026 | 2000 | 10.14% | |
| 4 | Jan 1, 2027 | 3000 | 21.32% | |
| 5 | Jul 1, 2027 | 4000 | ||
| 6 | Jan 1, 2028 | 4000 |
How it works
- B2:B6
- Cash flows: at least one negative (investment) and one positive.
- A2:A6
- Dates for XIRR. The first date is the start.
- ^2
- Cash flows here are six months apart, so (1+IRR)^2−1 converts a half-year IRR to an annual rate.
Common errors and fixes
Why does the formula return #NUM!?
No sign change in the cash flows, or the solver didn't converge. Add a guess, e.g. XIRR(B2:B6,A2:A6,0.1).
IRR looks too small?
Your periods aren't years. Annualize, or use XIRR.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.