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

The Net Present Value is described in detail on the Wikipedia Net Present Value page

Related Functions:
XNPV Function
PV Function

Basic 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:

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

Also note that in the latest versions of Excel, you can provide up to 254 payment and income value arguments to the Npv function, but in Excel 2003, you can only provide up to 29 values to the function.


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 an example of the NPV function.

The rate and value arguments that are supplied to the function are shown in cells A1 - A7 of the spreadsheet and the NPV function is shown in 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 period 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 an 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 shown in cells A1 - A7 of the spreadsheet and the NPV function is shown in 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.


Further examples of the Excel NPV function can be found on the Microsoft Office website