XNPV (Net Present Value of an Investment for Payments or Incomes at Irregular Intervals)

Returns the net present value of an investment based on a discount rate and a set of future payments (negative values) and income (positive values).

These payments or incomes do not need to occur at regular intervals. To calculate the net present value of an investment for payments and incomes that occur at regular intervals, see NPV (net present value of an investment).

Syntax

XNPV <FactID> (Payments, Dates, Rate)

Where:

FactID is a parameter that forces a calculation to take place on a fact table that contains the selected fact.

Payments is a fact or metric representing a list of values containing either payment or income figures.

Dates is a fact or metric representing a list of dates for the payments or incomes that are included. Ensure that these values use a date/time format.

Rate is the discount rate applied to the cash flows. This is commonly included in the expression as a constant value.

Expression

Where:

n is the number of entries in payments.

di is the ith payment date and d1 is the first payment date.

Usage Notes

The order of the entries in Payments is assumed to be the order in which funds flow; payment and income entries must, therefore, be provided in the correct sequence.

Example

You have an investment where payments are made at irregular intervals. This payment and payment date information is included in MicroStrategy and displayed on the simple report shown below:

Since the dates are not at regular intervals such as monthly or quarterly, you cannot use the NPV function to determine the net present value. Instead, you can use the XNPV function, which also evaluates the date of each payment to determine a net present value. A new metric is added to the report, which uses the following definition that includes a rate of 20%:

XNPV(Payment, Date, 0.2){}

The new report now includes this information, as shown below:

The net present value considering the payments, the dates of those payments, and a rate of 20% is $20,379. Notice that while there is a single value for XNPV, it is displayed on every row. One way to simplify the display of this information is to include this data on a dashboard. You can include the XNPV result in a text field along with additional visualizations of the data, as shown below.