Documentation/Calc Functions/XNPV

From The Document Foundation Wiki
Jump to navigation Jump to search


Function name:

XNPV

Category:

Financial Analysis

Summary:

Calculates the capital value (net present value) for a list of payments that take place on different dates. The calculation is based on a 365 days per year basis, ignoring leap years.

Syntax:

XNPV(Rate; Values; Dates)

Returns:

Returns a real number (in currency units) which is the net present value of a series of cash flows.

Arguments:

Rate is a real number (expressed as a percentage or fraction) or a reference to the cell containing that number which is the internal rate of return for the payments.

Values is an array of real numbers or a reference to a range of cells containing those numbers which are a series of cash flows. The first cash-flow amount is a negative number that represents the investment. The later cash flows are discounted based on the annual discount rate and the timing of the flow. The series of cash flows should contain at least one positive and one negative value.

Dates is a reference to a range of cells containing those dates which are a series of dates that correspond to Values. The first date indicates the start of the cash flows. All other date values must be later, but need not be in any order. The range of Values and Dates shall be the same size. The series of values must contain at least one negative and one positive value (receipts and deposits).

  • If dimensions of Dates and Values are not the same then the function returns an error value.

Additional details:

  • The formula for XNPV is:
Xnpv new.png

where N is the count of Values/Dates.

  • If the payments take place at regular intervals, use the NPV function.

Examples:

A B C
1 2001-01-01 -10000 Received
2 2001-01-02 2000 Deposited
3 2001-03-15 2500
4 2001-05-12 5000
5 2001-08-10 1000
Formula Description Returns
=XNPV(0.06;B1:B5;A1:A5) or =XNPV(6%;B1:B5;A1:A5) or =XNPV(0.06;{-10000;2000;2500;5000;1000};A1:A5) The function calculates the net present value for the above-mentioned five payments for a national internal rate of return of 6%. 323.016893587303
=XNPV(0.06;B1:B5;A1:A4) The function returns an error since the dimensions of Values and Dates are not same. Err:502

Related LibreOffice functions:

NPV

ODF standard:

Section 6.12.52, part 2

Equivalent Excel functions:

XNPV