Three dated flows
| A | B | |
|---|---|---|
| 1 | Date | Cash flow |
| 2 | 1/1/2024 | -1000 |
| 3 | 7/1/2024 | 600 |
| 4 | 1/1/2025 | 600 |
Formula: D2: =ROUND(XIRR(B2:B4,A2:A4),4)
Result: 0.2782
The payments of 600 after six and twelve months produce an annual return of about 27.8%.
Google Sheets function
XIRR in Google Sheets
XIRR with worked examples you can test in the live grid. The argument details are in the Excel guide.
In Google Sheets
The arguments and common errors are covered in the XIRR Excel guide; check the function help in Google Sheets for its own argument list.
Worked examples in Google Sheets
One year in, one year out
| A | B | |
|---|---|---|
| 1 | Date | Cash flow |
| 2 | 1/1/2024 | -1000 |
| 3 | 1/1/2025 | 1100 |
Formula: D2: =ROUND(XIRR(B2:B3,A2:A3),4)
Result: 0.0997
A 10% gain over 366 days is slightly under 10% on a 365-day year.
Uneven calendar
| A | B | |
|---|---|---|
| 1 | Date | Cash flow |
| 2 | 1/15/2024 | -1000 |
| 3 | 6/1/2024 | 400 |
| 4 | 2/1/2025 | 800 |
Formula: D2: =ROUND(XIRR(B2:B4,A2:A4),4)
Result: 0.251
The three flows are spaced unevenly, which IRR could not handle.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in D2: =XIRR(B2:B5,A2:A5)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Cash flow | Result | |
| 2 | 1/1/2026 | -10000 | 0.247820893036 | |
| 3 | 6/1/2026 | 3000 | ||
| 4 | 1/1/2027 | 4500 | ||
| 5 | 6/1/2027 | 5000 |
Result in D2: 0.247820893036
Google Sheets is used here only as a descriptive product name. FormulaCamp is not affiliated with Google.