Related Functions:

The NPV function calculates the Net Present Value of an investment, based on a supplied discount rate, and a series of future payments and income.

Syntax

=NPV(rate,value1,[value2], … )

Note: Beginning with Excel 2007, you can enter up to 254 payment and income arguments to the function. Excel 2003 would only accept up to 29 value arguments.

Arguments

Argument Description
rate The discount rate over one period
value1,
[value2], …
Numeric values, representing payments and income, where:

  negative values are treated as payments
  positive values are treated as income

Note

  • If the values are supplied individually, numbers, blank cells, logical values and text representations of numbers are interpreted as numeric values; Other text values and error values are ignored;
  • If the values are supplied as an array, all non-numbers in the array are ignored.

Examples

  A B C
1 Data    
2 5% Annual Discount Rate  
3 -$10,000 Initial investment cost  
4 $2,000 Return – year 1  
5 $2,400 Return – year 2  
6 $2,900 Return – year 3  
7 $3,500 Return – year 4  
8 $4,100 Return – year 5  
9      
10 Formula Result Notes
11 =NPV(A2,A4:A8)+A3 $2,678.68 Initial investment (A3) not included – see below
12 =NPV(A2,A3:A8) $2,551.12 Initial investment (A3) included – see below

Note:

  • The initial investment of $10,000, shown in cell A3, is made at the start of the first period. Therefore, this value is not included in the arguments to the NPV function. Instead it is added on at the end.
  • If the investment was added one year, or period, into the investment, this would then be the first value1 argument in the NPV function.

The NPV Function uses the following equation to calculate the Net Present Value of an Investment:

    \[ NPV = \sum_{i=1}^n \frac{values_i}{(1+rate)^i}  \]