Documentation/Calc Functions/NPV
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
NPV
Category:
Financial Analysis
Summary:
Calculates the net present value (sometimes known as net present worth) of a series of future periodic cash flows. Net present value analysis is a financial technique that is often used to assess the likely profitability of a project or investment.
NPV assumes that cash flows occur regularly, at the end of equal length time periods. Cash flow values may vary between periods and represent either income (positive) or expenditure (negative). Each cash flow is discounted to determine its present value using a constant discount rate and the sum of those present values is returned.
Syntax:
NPV(Rate; Value 1 [; Value 2 [; … [; Value 254]]])
Returns:
Returns a real number, which is the net present value of the specified sequence of cash flows based on the given discount rate. The number returned is in the same currency unit as the specified cash flow values, and by default is formatted in a locale-dependent currency format.
Arguments:
Rate is a real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to a cell containing that number, which is the periodic discount rate. Typically, periods may be monthly, quarterly, semiannually, or annually, but the period length is not passed to NPV. Although it is common for discount rates to be quoted annually, it is important that the value of the Rate argument is adjusted to match the true period length between cash flows.
Value 1, Value 2, … , Value 254 give the set of real numbers, which are the periodic cash flows. Each argument may take one of the following forms:
- A real number, or an expression that evaluates to a real number.
- A reference to a single cell containing a real number.
- A simple reference to a cell range containing real numbers (for example, A1:B9).
- The name of a named range, comprising cells containing real numbers.
- The name of a database range, comprising cells containing real numbers.
- An inline array of real numbers (for example, {1.1; 2.2; 3.3; 4.4}).
The following points should be noted with regards to the supplied cash flows:
- Each cash flow should be expressed in the same currency unit.
- Each cash flow may be positive (income), zero, or negative (expenditure).
- One cash flow should be supplied for each period during the predicted life of the project or investment. The value 0 should be included for any period with a zero cash flow – if this is omitted, then later cash flows will be discounted incorrectly.
- NPV expects cash flows to be supplied in chronological order and evaluates the arguments in the order that they appear - Value 1, Value 2, and so on. Range reference and array arguments are evaluated row-wise starting from the top-left.
- The order of the cash flows passed to NPV is important. For example, the formula
=NPV(10%; 100; 200; 300)
returns 481.59 currency units, while the formula=NPV(10%; 300; 200; 100)
returns 513.15 currency units. - Although NPV can accept up to 254 Value n arguments, each could specify a range of cells and this means that the total number of cash flows processed could (in theory) be greater than 254.
The following conditions (including errors) may be encountered:
- If the Rate argument is non-numeric, then NPV reports a #VALUE! error.
- If the Rate argument is set to -100% or -1, then NPV reports a #NUM! error.
- Cells within the Value n arguments that are empty or contain text are ignored.
- If a Value n argument does not match one of the expected forms listed above, then NPV reports a parameter list error (Err:504).
Additional details:
- The discount rate is assumed to compound each period and so the formula for the NPV function is:
- [math]\displaystyle{ \text{NPV}~=~\sum_{i=1}^{n}\frac{FV_{i}}{(1+r)^{i}} }[/math]
- where:
- n = total number of cash flows specified
- FVi = value of ith cash flow
- r = periodic discount rate
- If the resulting value is positive (and the input data estimates are based on valid assumptions), then the projected earnings generated by the project or investment should exceed the anticipated costs.
- Care should be taken if the project or investment not only has cash flows at the end of each period, but also requires a cash flow at the beginning of the first period to cover startup costs. This can be addressed in one of two ways:
- A simple solution is to omit details of any startup payment from the list of cash flows passed to NPV. Then simply add the startup cash flow (which needs no discounting) to the value returned by NPV to give the overall net present value.
- An alternative method is to pass the startup cash flow to NPV, as the first value in the list of cash flows. The value returned should then be multiplied by a factor (1+Rate) to account for the over-discounting of all cash flows.
- Examples of these two methods are provided in the #Examples: section.
- It is also possible to use NPV when the cash flows occur at the beginning of each period. In this case, the value returned should again be multiplied by a factor (1+Rate) to account for the over-discounting of all cash flows. An example of this method is provided in the #Examples: section.
- To calculate the net present value for a series of non-periodic cash flows, use the XNPV function.
- NPV is related to the IRR function – given a common set of cash flows, the internal rate of return (IRR) is the discount rate for which the NPV function would return 0.
- For more background information about net present value, visit Wikipedia's Net present value page.
Examples:
The following table shows the data used in the first example below.
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Discount Rate | 2.5% | |||
2 | |||||
3 | Q1 | Q2 | Q3 | Q4 | |
4 | Year 1 | 100 | 150 | 200 | 250 |
5 | Year 2 | 300 | 350 | 400 | 450 |
6 | Year 3 | 500 | 550 | 600 | 650 |
Note that the formatting of the return values given in the following examples might assume the English (USA) locale.
Formula | Description | Returns |
---|---|---|
=NPV(B1; B4:E6)
where cells B1 and B4:E6 contain the values shown in the table above. |
Here the function calculates the net present value of twelve quarterly cash flows, based on a constant discount rate of 2.5% per quarter.
The function returns the value 3,695.96 currency units.
Note that the formulas |
$3,695.96 |
=NPV(0.0875; {1000; 2000; 3000}) - 4500 |
A proposed project has an initial startup cost of 4500 currency units but provides cash flows of 1000, 2000, and 3000 currency units at the end of each of the three years of the project.
The net present value of these cash flows is to be calculated using a discount rate of 8.75%.
|
$443.21 |
=1.0875*NPV(0.0875; {−4500; 1000; 2000; 3000}) |
This example calculates the net present value for exactly the same project and cash flows as in the previous example.
|
$443.21 |
=NPV(8.75%; −1000; 2500; 3500) - 4000 |
A proposed project has an initial startup cost of 4000 currency units but provides cash flows of −1000, 2500, and 3500 currency units at the end of each of the three years of the project.
The net present value of these cash flows is to be calculated using a using a discount rate of 8.75%.
|
−$84.33 |
=1.0875*NPV(8.75%; −4000; −1000; 2500; 3500) |
This example calculates the net present value for exactly the same project and cash flows as in the previous example.
|
−$84.33 |
=1.1*NPV(10%; −700; 200; 300; 400) |
A proposed project is expected to provide cash flows of −700, 200, 300, and 400 currency units at the beginning of each of the four years of the project.
The net present value of these cash flows is to be calculated using a using a discount rate of 10%.
|
$30.28 |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
NPV