Financial analysis
NPV function: keep the day-zero investment outside NPV
NPV discounts its first cash-flow argument by one full period. Add the initial day-zero outlay outside NPV(rate, Year1:YearN) so present value is never misstated.
An immediate outlay followed by three annual inflows
A project requires -1,000 today (Year 0 in B2) and returns 400 at the end of Years 1, 2, and 3 (B3:B5). The required annual discount rate in E2 is 10% (0.10).
| Period | Cell | Cash flow |
|---|---|---|
| Year 0 (today) | B2 | -1000 |
| Year 1 | B3 | 400 |
| Year 2 | B4 | 400 |
| Year 3 | B5 | 400 |
Add B2 outside NPV(E2, B3:B5)
NPV assumes its first value occurs at the end of Period 1. Discounting Years 1–3 gives 400/1.1 + 400/1.21 + 400/1.331 = 994.74, so =ROUND(B2 + NPV(E2, B3:B5), 2) equals -5.26.
Including B2 inside NPV—NPV(E2, B2:B5)—wrongly discounts today’s -1,000 by 1.10 and reports -4.78.
=ROUND(B2+NPV(E2,B3:B5),2)Cross-check with IRR and test a higher Year 3 inflow
IRR(B2:B5) includes Year 0 directly and returns 0.0970 (9.70%), confirming why a 10% hurdle rate produces a negative NPV.
Increase B5 from 400 to 420 in the practice grid: the present value rises by 20 / 1.331 = 15.03, flipping NPV from -5.26 to +9.77.
=ROUND(IRR(B2:B5),4)Try the example
Use the original values or change them to test your understanding.