The Excel NPV Function

NPV Function Equation

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

Excel NPV Equation

For a detailed description of the Net Present Value, see the Wikipedia Net Present Value page.

Related Functions:
XNPV Function
PV Function

Function Description

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

The syntax of the function is:

NPV( rate, value1, [value2], [value3], ... )

where the arguments are as follows:

rate-The discount rate over one period.
value1, [value2], ...-

Numeric values, representing a series of regular payments and income, where:

  • Negative values are treated as payments;
  • Positive values are treated as income.

Note that:



NPV Function Example

Example 1

 AB
12% - Annual discount rate
2-5000 - Initial investment cost after 1 year
3800 - Return from year 1
4950 - Return from year 2
51080 - Return from year 3
61220 - Return from year 4
71500 - Return from year 5
8  
9 Net Present Value:
10 =NPV( A1, A2:A7 )

The above spreadsheet on the right shows a simple example of the NPV function.

The rate and value arguments that are supplied to the function are stored in cells A1-A7 of the spreadsheet and the NPV function is entered into cell B10.

This function gives the result 196.88.

Note that, in this example, the initial investment of $5,000 (shown in cell A2), is made at the end of the first period. Therefore, this value is included as the first value1 argument to the NPV function.



NPV Function Example 2

 AB
15% - Annual discount rate
2-10000 - Initial investment cost at start of year 1
32000 - Return from year 1
42400 - Return from year 2
52900 - Return from year 3
63500 - Return from year 4
74100 - Return from year 5
8  
9 Net Present Value:
10 =NPV( A1, A3:A7 ) + A2

The above spreadsheet on the right shows a further example of the NPV function in which the first payment is made at the start of the first period.

Again, the rate and value arguments of the investment are stored in cells A1-A7 of the spreadsheet and the NPV function is entered into cell B10.

This function gives the result 2,678.68.

Note that, as the initial investment of $10,000 (shown in cell A2), is made at the start of the first period, this value is not included in the arguments to the NPV function. Instead it is added on afterwards.


For further examples of the Excel NPV function, see the Microsoft Office website