NPV function
NPV discounts a series of future cash flows back to today and returns their net present value. It is used to judge whether an investment is worth its cost.
Example
Read it as a sentence: find the present value of the values in N2:N5 at 10% per period, then add the N1 value, which gives $115.57.
Excel shows $115.57
N
1
-1000
2
300
3
400
4
500
5
200
Data
Syntax
NPV(rate, value1, [value2], ...)
Arguments in [square brackets] are optional.
-
rate(required) - Discount rate per period.
-
value1(required) - First cash flow, or a range of them.
-
value2(optional) - Further cash flows, up to 254 arguments.
Returns: Net present value of future cash flows.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=NPV(0.1,100,200) | $256.20 | Scalars |
=NPV(0,100,200) | $300.00 | Rate 0 |
=NPV(0.1,"100") | $90.91 | Text number arg counted |
=NPV(0.1,TRUE) | $0.91 | TRUE arg |
=NPV(0.1,Data!N2:N5)+Data!N1 | $115.57 | Range |
=NPV(0.1,Data!B1:B8) | $63.94 | Mixed range ignored text/bool/blank |
=NPV(-1,100,200) | #DIV/0! | Rate -1 |
=NPV(0.1,"a") | #VALUE! | Text arg |
The Data sheet these formulas read
A cell that starts with = holds that formula.
B
N
1
10
-1000
2
20
300
3
TRUE
400
4
500
5
abc
200
6
30
7
FALSE
8
40
Data
Watch out for
- The first value is treated as one period away, so add an upfront outflow outside the function.
- Assumes evenly spaced flows; use XNPV for dates.
Where it works
- Excel
- NPV is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .