Description
Use this function to calculate the net present value of an investment based on a series of future cash flows and a discount rate. Supported in Chains. Can be used with CHILDREFS.
NPV is widely used in finance and accounting to determine the profitability of an investment or project. It takes into account the time value of money, recognizing that money received in the future is worth less than money received today.
The NPV function assumes:
- A constant discount rate over the investment period
- Cash flows occur at regular intervals (usually annually)
- The first cash flow occurs one period after the initial investment
Syntax
NPV(rate, value1, […, value_254])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
rate |
Yes | The discount rate over one period. |
A number greater than -1 (e.g., 10% is entered as 0.1), a reference to a cell containing such a number, or a formula which results in either of these. |
value_1, […,value_n] |
Yes | A series of cash flows that correspond to a schedule of payments in a schedule of payments. | A number, a reference to a cell containing a number, a cell range, or a formula which results in any of these. Up to 254 additional values can be provided. |
Example
Sample data
| A | B | |
|---|---|---|
| 1 | Year | Cash Flow |
| 2 | 0 | -100000 |
| 3 | 1 | 30000 |
| 4 | 2 | 40000 |
| 5 | 3 | 50000 |
| 6 | 4 | 60000 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Calculates the NPV of the specified investment with a specified discount rate, including the initial outlay. | =NPV(0.1, B3:B6) + B2 |
Calculates the NPV of B3:B5 with a 0.1 (10%) discount rate, including the initial outlay ( For this data set this formula returns $45,356.61. |
| Calculate the NPV of the specified investment with a specified discount rate, including the initial outlay. | =NPV(0.12, B3:B6) + B2 |
Calculates the NPV of For this data set this formula returns $37,712.55. |
| Calculate the NPV of the specified investment with a specified discount rate, including the initial outlay. | =NPV(0.08, B3:B6) + B2 |
Calculates the NPV of For this data set this formula returns $53,864.38. |
| Calculate the NPV using explicit values instead of cell references. | =NPV(0.15,30000,60000,90000,100000)+(-100000) |
Calculates the NPV using the supplied values with a For this data set this formula returns $87,807.36. |
Notes
- NPV does not include the initial investment. You must add or subtract that separately.
- NPV uses the order of the values to interpret the order of cash flows. Be sure to enter your payments and income in the correct sequence.
- If the value of an argument is not numeric, NPV treats it as 0 (zero).
- NPV is closely related to the internal rate of return (IRR) function and can be used to analyze the profitability of an investment or project.
- The discount rate used in NPV calculations is typically the cost of capital, opportunity cost, or inflation rate.
Tips
- Always include the initial investment cost (usually a negative number) in your NPV calculation, but not as part of the NPV function itself.
- Use NPV in conjunction with other financial functions such as IRR and XNPV for comprehensive investment analysis.
- When comparing projects, the one with the higher NPV is generally considered more favorable.
- For very long series of cash flows, it's usually more efficient and manageable to reference a range of cells containing the cash flow values rather than inputting them as individual arguments.