Documentation/Calc Functions/NPV

    From The Document Foundation Wiki
    Other languages:

    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:
    1. 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.
    2. 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.

    Data for example of NPV function
    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 =NPV(B1; B4:E4; B5:E5; B6:E6) and =NPV(B1; B4; C4; D4; E4; B5; C5; D5; E5; B6; C6; D6; E6) return the same result.

    $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%.

    Here the formula does not pass the startup cost as an argument to the NPV function but instead adds its value to the result as an undiscounted cash flow. The formula returns 443.21 currency units and this positive value indicates that the project should be profitable (assuming that the underlying assumptions are valid).

    $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.

    Here the formula passes the startup cost as an argument to the NPV function and so the value returned is multiplied by the factor 1.0875 (1+Rate) to correct the discounted values of all cash flows. The formula again returns 443.21 currency units.

    $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%.

    Here the formula does not pass the startup cost as an argument to the NPV function but instead adds its value to the result as an undiscounted cash flow. The formula returns −84.33 currency units and this negative value indicates that the project is unlikely to be profitable (assuming that the underlying assumptions are valid).

    −$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.

    Here the formula passes the startup cost as an argument to the NPV function and so the value returned is multiplied by the factor 1.0875 (1+Rate) to correct the discounted values of all cash flows. The formula again returns −84.33 currency units.

    −$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%.

    Here the formula passes all cash flows to the NPV function and the value returned is multiplied by the factor 1.1 (1+Rate) to correct the discounted values of all cash flows. The formula returns 30.28 currency units.

    $30.28

    Related LibreOffice functions:

    IRR

    PV

    XNPV

    ODF standard:

    Section 6.12.30, part 2

    Related (or similar) Excel functions:

    NPV