How to use NPV Google Sheets formula?

The syntax for the NPV formula is =NPV(discount, cashflow1, [cashflow2, ...]), it has 2 required parameters and 1 optional parameter.

NPV

The NPV function calculates the net present value of a series of cash flows, discounted by a specified rate. It is commonly used to determine the present value of an investment, where the cash flows represent incoming and outgoing payments. The function takes a discount rate and one or more cash flow values as input.

How to use NPV formula?
Examples of using NPV formula
NPV formula not working?
Similar formulas to NPV

Usage

Use the NPV formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:

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

Parameters:

discount (required):
The discount rate to be applied to the cash flows. This represents the rate of return that could be earned on an investment with similar risk.
cashflow1 (required):
The first cash flow value to be included in the calculation. This could be a positive or negative value representing incoming or outgoing payments.
cashflow2, ... (optional):
Additional cash flow values to be included in the calculation. These could be positive or negative values representing incoming or outgoing payments.

Examples

Here are a few example use cases that explain how to use the NPV formula in Google Sheets.

Calculating the present value of an investment

Suppose you are considering an investment that will require an initial outlay of $10,000 and will generate cash flows of $5,000 per year for the next 5 years. You believe the appropriate discount rate is 8%. You can use the NPV function to calculate the present value of the investment, which will help you decide whether to proceed.

Comparing two investments

Suppose you are considering two different investments, each of which requires an initial outlay of $5,000 and generates cash flows of $2,000 per year for the next 5 years. However, the two investments have different cash flows in later years. You can use the NPV function to calculate the present value of each investment and compare them to see which one is more attractive.

Determining the value of a project

Suppose you are evaluating a proposed project that will require an initial outlay of $50,000 and generate cash flows of $10,000 per year for the next 10 years. You can use the NPV function to determine whether the project is worth pursuing, based on the current value of its future cash flows.

Common Mistakes

NPV not working? Here are some common mistakes people make when using the NPV Google Sheets Formula:

Incorrectly calculating the discount rate

One common mistake when using the NPV formula is incorrectly calculating the discount rate. This can be fixed by double-checking the calculations and making sure the right rate is used.

Not including all cash flows

Another common mistake is not including all cash flows in the formula. Make sure to include all cash flows in the formula to get an accurate result.

Incorrectly ordering the cash flows

It is important to order the cash flows correctly in the formula to get an accurate result. Make sure to order the cash flows from earliest to latest.

Using inconsistent time periods

Another mistake is using inconsistent time periods for the cash flows. Make sure all cash flows are in the same time period (e.g. years) before using the formula.

Not adjusting for inflation

If you are not adjusting for inflation, your NPV calculation may be inaccurate. Make sure to account for inflation when calculating the cash flows.

The following functions are similar to NPV or are often used with it in a formula:

IRR
The IRR function is used to calculate the Internal Rate of Return for a series of cash flows. The IRR is the interest rate at which the Net Present Value of the cash flows equals zero. It is commonly used to evaluate potential investments or projects.
XNPV
The XNPV function calculates the net present value of a series of cash flows that occur at irregular intervals. It discounts each cash flow by a discount rate that is supplied by the user. It is commonly used in financial analysis to determine the value of investments or to evaluate the profitability of projects.
PV
The PV function in Google Sheets calculates the present value of a regular payment stream or a lump sum amount, based on a constant interest rate. It is commonly used in financial analysis to determine the value of investments or loans. This function returns a negative value, as it represents money flowing out from the user.
FV
The FV function calculates the future value of an investment based on periodic constant payments and a constant interest rate. It takes into account the present value of the investment, the number of periods in which the payments are made, and the compounding frequency. This formula is commonly used in financial planning and investment analysis.

Learn More

You can learn more about the NPV Google Sheets function on Google Support.

NPV