The NPV function in Google Sheets is categorized under the financial functions (Insert > Function > Financial).

The purpose of the NPV function is to calculate the net present value (NPV) of an investment. For the calculation, the function requires or we can say we should input the discount rate and a series of periodic (future) cash flows.

As you may already know, money earned today is worthier than the money earned in the future because the money now can earn interest. The discount rate (interest rate) in the NPV calculation addresses this difference.

We need to use the NPV function in a tricky way in Google Sheets (Spreadsheets) to get the correct net present value of future cash flows.

The reason, NPV function is similar to the PV function. The only notable difference is the former allows variable-value cash flows in contrary to the constant-amount periodic payments in PV. I’ll explain it with an example at a later part of this tutorial.

Let’s see how to use the NPV function in Google Sheets in the correct way.

Syntax and Arguments of the NPV Function in Google Sheets

Syntax:

NPV(discount, cashflow1, [cashflow2, ...])

Arguments:

• discount – The rate of discount of the investment over the length of one period.
• cashflow1 – The first (future) cash flow.
• cashflow2, … – Additional (future) cash flows.

Please note the below points while entering each cash flow values (‘cashflow’ arguments).

Points to be Noted:

• cahshflow1, cashflow2, … must be equally spaced in time and occur at the end of each period.
• The ‘cashflow’ should be +ve if it represents income or -ve if it represents payments that in the investor’s perspective.
• The NPV function in Google Sheets uses the order of cashflow1, cashflow2, … to interpret the order of cash flows. Please make sure to enter the payment and income values in the correct sequence.

Examples to Net Present Value Calculation in Google Sheets

Actually, NPV is one of the easy to learn financial functions in Google Sheets.

For example, let’s consider investing in an upcoming project. Using NPV formula in Google Sheets we can make a decision about whether it’s worth investing in the project.

This is just a basic example to make you understand how to use the NPV function in Google Sheets.

Example 1 – Investment Decision

Assume the said project (it’s a two-year project) requires an initial investment of $1,000 and we are expecting 0 income in the first period (first year) and $1,500 in the second period (second year).

If the annual discount rate is 8%, the NPV would be 286.01.

Actually NPV function returns the present value (PV) of variable-value cash flows.

=npv(A3,D4:D5)

Result: 1,286.01

To get net present value (NPV), we must subtract (add because of negative value) the initial investment value from the PV.

=npv(A3,D4:D5)+D3

Result: 286.01

Using IF logical statement, we can return YES / NO, I mean whether to invest in the project or not.

=if(npv(A3,D4:D5)+D3>0,"YES","NO")

See one more example. In that, I’m comparing PV with NPV so that you can better understand the NPV.

Example 2 – NPV is Similar to PV (Fact Check)

To make a comparison we can feed fixed-value cash flows instead of variable-value cash flows to the NPV function in Google Sheets.

Input Values for NPV:

discount – 8%

There are 5 number of future cash flows and the amount is $1,500 in each 5 periods.

The following NPV formula would return 5,989.07 as the net present value.

=npv(A2,C3:C7)

Note: Actually the above value is PV since we haven’t considered the initial investment value in cell C2 which is -4,500.

Let’s use the above elements in the PV function.

The below PV formula would return -5,989.07 and that tells everything!

=pv(B10,B11,B12)

That’s all about how to use the NPV function in Google Sheets. Thanks for the stay, Enjoy!