A simple project
| A | |
|---|---|
| 1 | Cash flow |
| 2 | -1000 |
| 3 | 400 |
| 4 | 400 |
| 5 | 400 |
Formula: C2: =ROUND(IRR(A2:A5),4)
Result: 0.097
Paying 1,000 and receiving 400 three times earns 9.7% a period.
Google Sheets function
IRR in Google Sheets
IRR 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 IRR Excel guide; check the function help in Google Sheets for its own argument list.
Worked examples in Google Sheets
Starting the search elsewhere
| A | |
|---|---|
| 1 | Cash flow |
| 2 | -1000 |
| 3 | 300 |
| 4 | 400 |
| 5 | 500 |
Formula: C2: =ROUND(IRR(A2:A5,0.2),4)
Result: 0.089
The guess of 20% only sets where the iteration starts, and the answer is the same rate it would find from 10%.
No sign change
| A | |
|---|---|
| 1 | Cash flow |
| 2 | 100 |
| 3 | 200 |
| 4 | 300 |
Formula: C2: =IRR(A2:A4)
Result: #NUM!
All flows are positive, so no discount rate can bring them to zero.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in D2: =IRR(B2:B6)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Cash flow | Result | |
| 2 | 0 | -10000 | 0.151064041897 | |
| 3 | 1 | 3000 | ||
| 4 | 2 | 4000 | ||
| 5 | 3 | 4500 | ||
| 6 | 4 | 2500 |
Result in D2: 0.151064041897
Google Sheets is used here only as a descriptive product name. FormulaCamp is not affiliated with Google.