Excel for Microsoft 365Excel for Microsoft 365 for MacExcel for the webExcel 2021Excel 2021 for MacExcel 2019Excel 2019 for MacExcel 2016Excel 2013 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.
The biggest mistake we make with the NPV formula is including Year 0 in the array. Excel recognizes the first value as a period one inflow/outflow. Accordingly, we need to exclude the Year 0 value but add that number at the end of the function.
The other common error with NPV is forgetting to divide the discount rate to match the cash flow periods. If you have annual cash flows, then you don't need to divide the discount rate. But if you have monthly cash flows, you need to divide the discount rate by 12, and quarterly would be divided by 4.
The reason is simple. Excel NPV formula assumes that the first time period is 1 and not 0. So, if your first cash flow occurs at the beginning of the first period (i.e. 0 period), the first value must be added to the NPV result, not included in the values arguments (as we did in the above calculation).
Because NPV calculations require the selection of a discount rate, they can be unreliable if the wrong rate is selected. Making matters even more complex is the possibility that the investment will not have the same level of risk throughout its entire time horizon.
NPV Formula. To calculate net present value, you need to determine the cash flows for each period of the investment or project, discount them to present value, and subtract the initial investment from the sum of the project's discounted cash flows.
Here's the NPV formula for a one-year project with a single cash flow:NPV = [cash flow / (1+i)^t] - initial investmentIn this formula, "i" is the discount rate, and "t" is the number of time periods.
Instead, it calculates the present value of a series of cash flows, even or uneven, but it does NOT net out the original cash outflow at time period zero. This original cash outflow actually needs to be manually subtracted out when using the NPV formula in Excel.
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.
Sensitivity to Inputs: NPV calculations are sensitive to changes in inputs such as cash flow estimates, discount rates, and project timelines. Small variations in these inputs can lead to significant changes in the NPV result, making the analysis somewhat subjective.
Net Present Value is the sum of the investment's expected cash inflows and outflows discounted back to their present value at a risk adjusted rate. If the NPV is greater than $0, the project is accepted. Otherwise the project is rejected.
Address: 2033 Gerhold Pine, Port Jocelyn, VA 12101-5654
Phone: +8524399971620
Job: Central Manufacturing Supervisor
Hobby: Jogging, Metalworking, Tai chi, Shopping, Puzzles, Rock climbing, Crocheting
Introduction: My name is Edmund Hettinger DC, I am a adventurous, colorful, gifted, determined, precious, open, colorful person who loves writing and wants to share my knowledge and understanding with you.
We notice you're using an ad blocker
Without advertising income, we can't keep making this site awesome for you.
