The Excel NPV Function
NPV Function Equation
The Excel NPV Function uses the following equation to calculate the Net Present Value of an Investment:
For a detailed description of the Net Present Value, see the Wikipedia Net Present Value page.
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:
|
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.
- In the latest versions of Excel, you can provide up to 254 value arguments to the NPV function, but in Excel 2003, the function can only accept up to 29 values.
NPV Function Example
Example 1
| A | B | |
|---|---|---|
| 1 | 2% | - Annual discount rate |
| 2 | -5000 | - Initial investment cost after 1 year |
| 3 | 800 | - Return from year 1 |
| 4 | 950 | - Return from year 2 |
| 5 | 1080 | - Return from year 3 |
| 6 | 1220 | - Return from year 4 |
| 7 | 1500 | - 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
| A | B | |
|---|---|---|
| 1 | 5% | - Annual discount rate |
| 2 | -10000 | - Initial investment cost at start of year 1 |
| 3 | 2000 | - Return from year 1 |
| 4 | 2400 | - Return from year 2 |
| 5 | 2900 | - Return from year 3 |
| 6 | 3500 | - Return from year 4 |
| 7 | 4100 | - 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