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.
=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.
|rate||The discount rate over one period|
|Numeric values, representing payments and income, where:
- 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.
|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|
|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|
- 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: