Skip to main content Skip to complementary content

XNPV - script function

The XNPV() script function takes specific dates corresponding to each cashflow being discounted apart from the discount rate. It is different from the NPV() function, since NPV() assumes all time periods to be equal. For this reason, XNPV() is more precise than NPV().


XNPV(discount_rate, pmt, date)

Return data type: numeric. By default, the result will be formatted as currency.

The formula to calculate XNPV is:

Image of formula for XNPV script function.


  • 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. To calculate NPV, we need to estimate future cash flows for each period and determine the correct discount rate.

XNPV() takes a discount rate and multiple values ordered by period. Inflows (incomes) are positive, and outflows (future payments) are assumed to be negative values. These occur at the end of each period.

Argument Description

discount_rate is the percentage rate of discount applied.

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


This field holds cashflow values. The first value is assumed to be the cashflow at the start, and corresponding date is used as reference for calculating present value for all future cash flows.

Information noteXNPV() does not discount the initial cash flow. Subsequent payments are discounted based on a 365-day year. This is different from NPV(), where every payment is discounted.

This field holds the date on which the cashflow (value, the second parameter) happened. The first value is used as the start date for calculating the offsets for future cashflows.


If there are text values, NULL values, and missing values in any or both pieces of a data-pair, the data-pair will be 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 1 – Single payment (script)

Example 2 – Multiple payments (script)

Example 3 – Multiple payments and irregular cashflows (script)

See also:

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!