Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More...Less
This article describes the formula syntax and usage of the NPVfunction in Microsoft Excel.
Description
Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).
Syntax
NPV(rate,value1,[value2],...)
The NPV function syntax has the following arguments:
-
RateRequired. The rate of discount over the length of one period.
-
Value1, value2, ...Value1 is required, subsequent values are optional. 1 to 254 arguments representing the payments and income.
-
Value1, value2, ... must be equally spaced in time and occur at the end of each period.
-
NPV uses the order of value1, value2, ... to interpret the order of cash flows. Be sure to enter your payment and income values in the correct sequence.
-
Arguments that are empty cells, logical values, or text representations of numbers, error values, or text that cannot be translated into numbers are ignored.
-
If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.
-
Remarks
-
The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation is based on future cash flows. If your first cash flow occurs at the beginning of the first period, the first value must be added to the NPV result, not included in the values arguments. For more information, see the examples below.
-
If n is the number of cash flows in the list of values, the formula for NPV is:
-
NPV is similar to the PV function (present value). The primary difference between PV and NPV is that PV allows cash flows to begin either at the end or at the beginning of the period. Unlike the variable NPV cash flow values, PV cash flows must be constant throughout the investment. For information about annuities and financial functions, see PV.
-
NPV is also related to the IRR function (internal rate of return). IRR is the rate for which NPV equals zero: NPV(IRR(...), ...) = 0.
Example
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
| Data | Description | |
|---|---|---|
| 0.1 | Annual discount rate | |
| -10000 | Initial cost of investment one year from today | |
| 3000 | Return from first year | |
| 4200 | Return from second year | |
| 6800 | Return from third year | |
| Formula | Description | Result |
| =NPV(A2, A3, A4, A5, A6) | Net present value of this investment | $1,188.44 |
Example 2
| Data | Description | |
|---|---|---|
| 0.08 | Annual discount rate. This might represent the rate of inflation or the interest rate of a competing investment. | |
| -40000 | Initial cost of investment | |
| 8000 | Return from first year | |
| 9200 | Return from second year | |
| 10000 | Return from third year | |
| 12000 | Return from fourth year | |
| 14500 | Return from fifth year | |
| Formula | Description | Result |
| =NPV(A2, A4:A8)+A3 | Net present value of this investment | $1,922.06 |
| =NPV(A2, A4:A8, -9000)+A3 | Net present value of this investment, with a loss in the sixth year of 9000 | ($3,749.47) |
Top of Page
Need more help?
Want more options?
Discover Community
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
I'm quite familiar with Excel across various platforms and versions, from Excel for Microsoft 365 to earlier iterations like Excel 2007. Let's dive into the NPV function in Excel, which is used to calculate the net present value of an investment based on future cash flows and a discount rate.
In Excel, the NPV function takes the rate of discount and a series of future payments or income as arguments. It's crucial that these values are equally spaced in time and occur at the end of each period. The function interprets the order of cash flows based on the sequence you provide.
The NPV calculation involves considering the rate of discount over the period and the order of future cash flows. Notably, it's essential to add the first cash flow separately if it occurs at the beginning of the first period.
It's worth mentioning the relationship between NPV, PV (present value), and IRR (internal rate of return). NPV is similar to PV but differs in that PV allows cash flows to begin either at the end or at the beginning of the period, while NPV focuses on future cash flows. Furthermore, IRR is the rate at which NPV equals zero.
For instance, in Excel, you can input the data provided in the article to calculate the net present value of investments using the NPV function. In the examples provided, the function is used with various discount rates and cash flow scenarios, demonstrating how to compute the net present value in Excel.
If you're interested in exploring further, Excel's NPV function can be a powerful tool for analyzing investments and making informed financial decisions, especially when coupled with other related functions like PV and IRR.
This function's flexibility and utility make it a valuable asset for financial analysis and planning within Excel across all the mentioned versions and platforms.
