Skip to main content Skip to complementary content

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

Information noteBy default, the result will be formatted as currency.

The formula to calculate XNPV is shown below:

XNPV aggregation formula

Image of formula for XNPV script function.

where:

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

  • d1= the first payment date

  • di = the ith 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.

Arguments
Argument Description
discount_rate

discount_rate is the yearly rate that the payments should be discounted by.

A value of 0.1 would indicate a 10% discount rate.

pmt Payments. The expression or field containing the cash flows corresponding to the payment schedule given in date. Positive values are assumed to be inflows, and negative values are assumed to be outflows.
Information noteXNPV() does not discount the initial cash flow since it will always happen on the start date. Subsequent payments are discounted based on a 365-day year. This is different from NPV(), where also the first payment is discounted.
date The expression or field containing the schedule of dates corresponding to the cash flow payments given in pmt. The first value is used as the start date for calculating the time offsets for future cash flows.
TOTAL

If the word TOTAL occurs before the function arguments, the calculation is made over all possible values given the current selections, and not just those that pertain to the current dimensional value, that is, it disregards the chart dimensions.

By using TOTAL [<fld {.fld}>], where the TOTAL qualifier is followed by a list of one or more field names as a subset of the chart dimension variables, you create a subset of the total possible values.

Defining the aggregation scope

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.

Example

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!