About this lesson
NPV in Google Sheets: valuing a project, and the year 0 trap
Google Sheets' NPV assumes the first cash flow is a year away, so keep today's cash flow out of it and add it separately: =B4+NPV(B1,B5:B9). Putting the year 0 cost inside, =NPV(B1,B4:B9), discounts everything by an extra year. A positive NPV means the project earns more than the discount rate.
Using Excel? Do this lesson in Excel.
=NPV(B1,B4:B9)What you'll practise
Appraise a warehouse automation project: fall into the NPV trap, fix it by adding year 0 outside the function, then prove the answer with a present value for each year.
4 steps · 40 points · the cells outlined in gold below
- NPV
- discount rate
- project appraisal
- year 0 cash flows
The data
| A | B | C | |
|---|---|---|---|
| 1 | Discount rate | 10% | |
| 2 | |||
| 3 | Year | Cash flow ($k) | PV ($k) |
| 4 | 0 | -4,800 | |
| 5 | 1 | 1,100 | |
| 6 | 2 | 1,400 | |
| 7 | 3 | 1,600 | |
| 8 | 4 | 1,700 | |
| 9 | 5 | 1,300 | |
| 10 | |||
| 11 | NPV, the trap | ||
| 12 | NPV, done right | ||
| 13 | NPV, by hand |