Documentation/Calc Functions/XNPV

    From The Document Foundation Wiki
    Other languages:

    Function name:

    XNPV

    Category:

    Financial Analysis

    Summary:

    Calculates the net present value (sometimes known as net present worth) of a series of future cash flows that do not need to be periodic. Net present value analysis is a financial technique that is often used to assess the likely profitability of a project or investment.

    XNPV allows cash flows to occur on any date, with values that may vary and represent either income (positive) or expenditure (negative). Each cash flow is discounted to determine its present value on the first date given. A constant discount rate is used and the sum of those present values is returned.

    XNPV assumes that all years (including leap years) comprise 365 days.

    Syntax:

    XNPV(Rate; Values; Dates)

    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.

    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 annual discount rate. Rate must be greater than -1 (-100%).

    Values is a list of real numbers, which are the values of the cash flows. Values may be a specific cell range (for example, "$SourceData.A3:B12"), the name of a named or database range, or an inline array. The following points should be noted with regards to the supplied cash flows:

    • Values should be the same length as the Dates argument and each should comprise at least two cells.
    • For each value in Values, there should be a corresponding date in Dates.
    • Each cash flow should be expressed in the same currency unit.
    • Each cash flow may be positive (income) or negative (expenditure).

    Dates is a list of dates, which are the dates when the cash flows occur. Dates may be a specific cell range (for example, "$SourceData.A3:B12"), the name of a named or database range, or an inline array. The following points should be noted with regards to the supplied dates:

    • Dates should be the same length as Values and each should comprise at least two cells.
    • For each date in Dates, there should be a corresponding value in Values.
    • The first entry in Dates is taken as the start of the cash flows, with subsequent entries discounted to that date.
    • The second and subsequent cash flows defined in the Dates and Values lists need not be in chronological order.

    The following conditions (including errors) may be encountered:

    • If the Rate argument is non-numeric, then XNPV reports a #VALUE! error.
    • If the Rate argument is not greater than -100% or -1, then XNPV reports an invalid argument error (Err:502).
    • If the number of cells in Values is not equal to the number of cells in Dates, then XNPV reports an invalid argument error (Err:502).
    • If the number of cells in Values is less than two, then XNPV reports an invalid argument error (Err:502).

    Additional details:

    • The formula for XNPV is:
    [math]\displaystyle{ \Large \text{XNPV}~=~\sum_{i=1}^{N}~\frac{Values_{i}}{(1+Rate)^{\frac{Dates_{i}-Dates_{1}}{365}}} }[/math]
    where N is the number of cash flows defined by the Values and Dates arguments.
    • 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.
    • To calculate the net present value for a series of periodic cash flows, use the NPV function.
    • XNPV is related to the XIRR function – given a common set of cash flows, the internal rate of return (XIRR) is the discount rate for which the XNPV function would return 0.
    • If you suspect incorrect results for XNPV, look in the data ranges for empty cells or cells containing text. To highlight text contents in a data range, use Calc’s View ▸ Value Highlighting facility.
    • Difficulties have been experienced when attempting to define the dates of cash flows in an inline array, such as {"2022-01-01", "2022-07-01", "2023-01-01"}, when XNPV does not give the results expected. Either place the dates in cells or use equivalent date-time serial numbers within the inline array (for example, {44562, 44743, 44927}).
    • For more background information about net present value, visit Wikipedia's Net present value page.

    Examples:

    Dates in the following examples are formatted in accordance with the ISO 8601 standard (YYYY-MM-DD). Other locale-dependent date formats will be available for your Calc installation. Note that it is often good practice to utilize the DATE function instead of typing formatted date values.

    The following table shows the data used in the first example below.

    Data for example of XNPV function
    A B
    1 Discount Rate 10%
    2
    3 Value Date
    4 100 2021-03-31
    5 150 2021-06-30
    6 200 2021-09-30
    7 250 2021-12-31
    8 300 2022-03-31
    9 350 2022-06-30
    10 400 2022-09-30
    11 450 2022-12-31
    12 500 2023-03-31
    13 550 2023-06-30
    14 600 2023-09-30
    15 650 2023-12-31


    Formula Description Returns
    =XNPV(B1; A4:A15; B4:B15) where cells B1 and A4:B15 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 10% per year. The function returns the value 3,810.46 currency units (to two decimal places). 3,810.46
    =XNPV(0.0875; C1:C4; D1:D4) where cells C1:C4 contain the values -4500, 1000, 2000, and 3000 respectively; cells D1:D4 contain the dates 2022-01-01, 2022-12-31, 2023-12-31, and 2024-12-31 respectively. A proposed project has an initial startup cost of 4,500 currency units but provides cash flows of 1,000, 2,000, and 3,000 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%. The function returns 443.81 currency units (to two decimal places) and this positive value indicates that the project should be profitable (assuming that the underlying assumptions are valid). 443.81
    =XNPV(0.0875; {−4500, 1000, 2000, 3000}; {44562, 44926, 45291, 45657}) This example calculates the net present value for exactly the same project and cash flows as in the previous example. The values 44562, 44926, 45291, and 45657 are the date-time serial numbers for 2022-01-01, 2022-12-31, 2023-12-31, and 2024-12-31 respectively. The formula again returns 443.81 currency units. 443.81
    =XNPV(5%; E1:E3; F1:F3) where cells E1:E3 contain the values -2000, 1000, and 3000 respectively; cells F1:F3 contain the dates 2022-01-01, 2022-07-01, and 2023-01-01 respectively. Here the function returns the value 1,833.24 currency units (to two decimal places). If the data for the second and third cash flows is swapped (that is, exchange E2↔E3 and F2↔F3), then exactly the same value is returned. Thus the ordering of data for the second and subsequent cash flows is not significant. 1,833.24

    Related LibreOffice functions:

    NPV

    XIRR

    ODF standard:

    Section 6.12.52, part 2

    Related (or similar) Excel functions:

    XNPV