XNPV - chart function

XNPV() returns the aggregated net present value for a schedule of cash flows (not necessarily periodic) represented by paired numbers in the expressions given by pmt and date, iterated over the chart dimensions. All payments are discounted based on a 365-day year.

Syntax:  

XNPV([TOTAL [<fld{,fld}>]] discount_rate, pmt, date)

Return data type: numeric

The formula to calculate XNPV is shown below:

where:

• P_i = Net cash inflow-outflows during a single period i

• d₁= the first payment date

• d_i = the i^th payment date

• rate = discount rate

Net present value, or NPV, is used to calculate the current total value of a future stream of cash flows given a discount rate. To calculate XNPV, we need to estimate future cash flows with corresponding dates. After this, for each payment, we apply the compounded discount rate based on the date of the payment.

Performing the XNPV aggregation over a series of payments is similar to performing a Sum aggregation over those payments. The difference is that each amount is modified (or “discounted”) according to the chosen discount rate (similar to interest rate) and how far into the future the payment is. Performing XNPV with the discount_rate parameter set to zero will make XNPV equivalent to a Sum operation (the payments will not be modified before being summed). In general, the closer the discount_rate is set to zero, the more similar the XNPV result will be to that of a Sum aggregation.

When working with this function, the following limitations apply:

• discount_rate, pmt and date must not contain aggregation functions, unless these inner aggregations contain the TOTAL or ALL qualifiers. For more advanced nested aggregations, use the advanced function Aggr, in combination with a specified dimension.

• Text values, NULL values and missing values in any or both pieces of a data-pair result in the entire data-pair being disregarded.

When to use it

• XNPV() is used in financial modeling for calculating the net present value (NPV) of an investment opportunity.

• Due to its higher precision, XNPV is preferred over NPV, for all types of financial models.

Regional settings

Unless otherwise specified, the examples in this topic use the following date format: MM/DD/YYYY. The date format is specified in the SET DateFormat statement in your data load script. The default date formatting may be different in your system, due to your regional settings and other factors. You can change the formats in the examples below to suit your requirements. Or you can change the formats in your load script to match these examples.

Default regional settings in apps are based on the regional system settings of the computer or server where Qlik Sense is installed. If the Qlik Sense server you are accessing is set to Sweden, the Data load editor will use Swedish regional settings for dates, time, and currency. These regional format settings are not related to the language displayed in the Qlik Sense user interface. Qlik Sense will be displayed in the same language as the browser you are using.

Cashflow:
LOAD 2013 as Year, * inline [
Date|Payments
2013-01-01|-10000
2013-03-01|3000
2013-10-30|4200
2014-02-01|6800
] (delimiter is '|');