說明
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.
Note: 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
語法
NPV(rate, value1, […, value_254])
Inputs
This function accepts the following arguments:
| 名稱 | 必要 | 說明 | Valid Input |
|---|---|---|---|
評分 |
是 | 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] |
是 | 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. |
Examples
範例資料
| A | B |
|---|---|
| 年 | Cash Flow |
| 0 | -100000 |
| 1 | 30000 |
| 2 | 40000 |
| 3 | 50000 |
| 4 | 60000 |
Sample formulas
| 公式 | 說明 | 結果 |
|---|---|---|
=NPV(0.1, B3:B6) + B2 |
Calculates the NPV of the investment with a 10% discount rate, including the initial outlay. | $45,356.61 |
=NPV(0.12, B3:B6) + B2 |
Calculates the NPV with a 12% discount rate. | $37,712.55 |
=NPV(0.08, B3:B6) + B2 |
Calculates the NPV with an 8% discount rate. | $53,864.38 |
=NPV(0.1, 30000, 40000, 50000, 60000) + (-100000) |
Calculates the NPV using explicit values instead of cell references. | $45,356.61 |
附註
- 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 ReturnIRR 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.
提示
- 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.
Related functions
FV
IRR
NPV
PMT
PV
XIRR
XNPV