Google Sheets NPV Function | Calculate Net Present Value of Investment | Google Sheets Functions

The Google Sheets NPV function returns the net present value of an
investment for a discount rate and a series of cash inflows and outflows over a timeframe. To get the NPV, if the first cash flow is at the beginning of the first period, do not include the cash flow while specifying the range for the cashflow1 attribute in the NPV function formula. Instead, add the first cash flow to the NPV output.

Here is an example that uses the above approach:

Say there is a cash outflow of $500 in period 0 and are cash inflows of $150, $375, and $600 in periods 1, 2, and 3 respectively. Use the NPV function and calculate the present value of the cash inflows. Then add the cash outflow from the NPV function result. The NPV will be $397.07.

Note that the a cash outflow is input as a negative number and a cash inflow as a positive numbers.

--------------------------------------------------------
Use the PMT function to calculate the payment on a loan. Here is the link to the step-by-step video tutorial on PMT:

   • Google Sheets PMT Function | Calculat...  

--------------------------------------------------------
Use the IPMT function to calculate the interest component of a loan. Here is the link to the step-by-step video tutorial on IPMT:

   • Google Sheets IPMT Function | Calcula...  

--------------------------------------------------------
Use the NPER function to calculate the number of repayment periods for a loan. Here is the link to the step-by-step video tutorial on NPER:

   • Google Sheets NPER Function | Calcula...  
--------------------------------------------------------
Use the PPMT function to calculate the principal component of a loan. Here is the link to the step-by-step video tutorial on PPMT:

   • Google Sheets PPMT Function | Calcula...  
--------------------------------------------------------

Let's look at the format of the Google Sheets NPV function formula:

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

Begin the formula with an equal-to symbol.

NPV is the name of the function, and stands for Net Present Value.

discount is the discount rate over one period.

cashflow1 is either a cash outflow or inflow.

cashflow2 and subsequent cash flows are optional.

Here is an example of the NPV function formula:

We will use the same example above again. The discount rate is 7%.
The cash outflow is -$500 is input in cell A1. The cash inflows of $150, $375, and $600 in the periods 1, 2, and 3 respectively are input in cells B2 to B4 respectively.

Let's input the numbers in the formula.

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

=NPV(7%, B2:B4)

=$957.51

Now, let's add the cash outflow to the above formula

=NPV(7%, B2:B4)+(-500)

The above formula returns $457.51.

Do take a look at this video tutorial, which gives the steps to use the Google Sheets NPV function formula with an example.