NPVfunction 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
- Examples of using
NPVformula not working?
- Similar formulas to
NPV formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:
- 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.
ExamplesHere are a few example use cases that explain how to use the
NPVformula 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.
NPVnot working? Here are some common mistakes people make when using the
NPVGoogle 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:
IRRfunction 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.
XNPVfunction 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.
PVfunction 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.
FVfunction 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.
You can learn more about the
NPV Google Sheets function on Google Support.