Skip to main content Skip to complementary content

XNPV - script function

XNPV() returns the aggregated net present value for a schedule of cashflows (not necessarily periodic) represented by paired numbers in pmt and date. All payments are discounted based on a 365-day year.

Syntax:  

XNPV(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 cashflows.

When working with this function, the following limitations apply:

  • 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 1 – Single payment (script)

Example 2 – Multiple payments (script)

Example 3 – Multiple payments and irregular cashflows (script)

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!