ExcelFunctions.net

Search Site:

NPV Function Equation

The Excel NPV Function uses the following equation :

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

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 format 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 payments and income, where : - negative values are treated as payments - positive values are treated as income |

Note that :

- 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

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

The spreadsheet on the right shows an example of the NPV function. The data used is 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:

- In this example, the initial investment of $10,000 (shown in cell A2), 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 afterwards.
- If the investment was added one year (or period) into the investment, this would then be the first value1 argument in the NPV function.

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