NPV, or Net Present Value calculates an investment's net present value using future cash flows and a discount rate. It assesses an investment's profitability by discounting future cash flows to their present value which aids in capital budgeting decisions to gauge an investment's attractiveness.
The syntax for the NPV function in Google Sheets is:
NPV(discount_rate, cash_flow1, [cash_flow2, ...])
It's important to note that the NPV function in Google Sheets does not include the initial investment at time 0 in its calculation. Therefore, to get the total NPV of an investment, you should add the initial investment (typically a negative number, since it's an outflow) to the NPV function's result.
This is because the initial investment usually occurs at the start of the first period (time 0), and the NPV function assumes that cash flows start at the end of the first period.
Follow the steps below to use the NPV formula in Google Sheets and assess the profitability of your investment.
Enter the following cash flows in cells B3 through B7: $2,000 for Year 1, $3,000 for Year 2, $3,500 for Year 3, $4,000 for Year 4, and $4,500 for Year 5. Then, input the discount rate of 8% in cell B1.
Click on an empty cell, such as B9, for the NPV calculation. Type the NPV formula =NPV(, and immediately reference the discount rate by clicking on cell B1 or typing B1.
After the discount rate in the formula, add a comma. Then, select cells B3 to B7 for the cash flows from Year 1 to Year 5. Close the formula with a parenthesis ) and press Enter. Google Sheets will calculate the NPV with your inputs.
The initial investment is not included in the NPV function. To adjust, add the initial investment amount from cell B2 to the NPV result. Update cell B9 to =NPV(B1, B3:B7)+B2.
Evaluate the NPV result in cell B9. A positive NPV indicates a potentially profitable investment, while a negative NPV suggests the investment may not be beneficial.
We hope that you now have a better understanding of what the NPV formula in Google Sheets is and how to use it. If you enjoyed this article, you might also like our article on how to use Goal Seek in Google Sheets or our article on how to change Google Sheets from radians to degrees.
