Value of three yearly receipts
| A | |
|---|---|
| 1 | Receipts |
| 2 | 100 |
| 3 | 200 |
| 4 | 300 |
Formula: C2: =ROUND(NPV(0.1,A2:A4),2)
Result: 481.59
The three amounts are worth about 481.59 today at a 10% discount rate.
Google Sheets function
NPV in Google Sheets
NPV 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 NPV Excel guide; check the function help in Google Sheets for its own argument list.
Worked examples in Google Sheets
Subtract the up-front cost
| A | |
|---|---|
| 1 | Cash flow |
| 2 | -500 |
| 3 | 100 |
| 4 | 200 |
| 5 | 300 |
Formula: C2: =ROUND(NPV(0.1,A3:A5)+A2,2)
Result: -18.41
The outlay at time zero is added outside NPV; the result of -18.41 says the project loses value at 10%.
Receipts at the start of each period
| A | |
|---|---|
| 1 | Receipts |
| 2 | 100 |
| 3 | 200 |
| 4 | 300 |
Formula: C2: =ROUND(NPV(0.1,A2:A4)*(1+0.1),2)
Result: 529.75
Multiplying by 1 plus the rate moves every flow one period earlier.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in D2: =NPV(10%,B3:B6)+B2
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Cash flow | Result | |
| 2 | 0 | -10000 | 1121.50809371 | |
| 3 | 1 | 3000 | ||
| 4 | 2 | 4000 | ||
| 5 | 3 | 4500 | ||
| 6 | 4 | 2500 |
Result in D2: 1121.50809371
Google Sheets is used here only as a descriptive product name. FormulaCamp is not affiliated with Google.