In this article:

NPV Formula in Google Sheets (Easiest Way to Use It in 2024)

May 8, 2024

NPV in Google Sheets

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.

NPV Formula in Google Sheets

The syntax for the NPV function in Google Sheets is:

NPV(discount_rate, cash_flow1, [cash_flow2, ...])

  • discount_rate is the rate used to discount future cash flows back to their present value.
  • cash_flow1, [cash_flow2, ...] represent the future cash flows for each period. The first cash flow occurs at the end of the first period, the second at the end of the second period, and so on. You can specify up to 254 cash flow periods.

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.

How to Use the NPV Formula in Google Sheets

Follow the steps below to use the NPV formula in Google Sheets and assess the profitability of your investment. 

1. Input Cash Flows and Set Discount Rate in Google Sheets

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.

npv google sheets

2. Select NPV Output Cell and Begin NPV Formula with Discount Rate

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.

google sheets npv

3. Add Future Cash Flows to NPV Formula, Complete, and Calculate

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.

npv formula google sheets

4. Incorporate Initial Investment into NPV Calculation

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.

npv in google sheets

5. Analyze NPV Result for Investment Decision

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.

npv formula in google sheets

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.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->