The NPV function in Google Sheets calculates the net present value of an investment based on a series of periodic cash flows and a discount rate. This powerful financial tool helps in evaluating the profitability of investments.
Parameters
discount: The discount rate used to calculate the present value of cash flows.cashflow1: The first cash flow to be considered.cashflow2, ...: [Optional] Additional cash flows to be considered.
Step-by-Step Tutorial
-
Calculating NPV with individual cash flows:
- Example:
=NPV(0.1, 1000, 2000, 3000) - Result: The result will be the net present value of the cash flows using a discount rate of 10%.
- Example:
-
Calculating NPV with a range of cells containing cash flows:
- Example: If
A1:A5has the cash flows1000, -500, 2000, 3000, -1500, then=NPV(0.08, A1:A5)will return the net present value using a discount rate of 8%.
- Example: If
Working with a Single Value, a List, and a Range
The discount argument is always a single rate, but the cash flows that follow it accept a single value, a comma-separated list, or a range — mix and match freely.
- Single cash flow:
=NPV(0.1, C1)— discounts just the one cash flow inC1. - List of cash flows:
=NPV(0.1, C1, C2, C3)— treats each cell as a separate period, in order. - Range:
=NPV(0.1, C1:C5)— treats every cell in the range as a period, in order. - Mixed:
=NPV(0.1, C1, C3:C5)— you can combine single cells and ranges, and they're still treated as consecutive periods in the order listed.
Use Cases and Scenarios
- Investment Evaluation: Determine the current value of future cash flows.
- Business Decision Making: Assess the profitability of a project or investment.
- Capital Budgeting: Analyze the financial viability of potential investments.
Related Functions
IRR: Calculate the internal rate of return for a series of cash flows.XIRR: Calculate the internal rate of return for cash flows that may occur at irregular intervals.PV: Calculate the present value of a future stream of cash flows.