XIRR (Internal Rate of Return for Payments at Irregular Intervals)

Returns the internal rate of return on a set of payments that do not occur at regular intervals. The internal rate of return is the interest rate received for an investment consisting of payments. These payments do not need to be equal in value, and do not need to occur at regular intervals.

To calculate the internal rate of return for payments that occur at regular intervals, see:

• IRR (internal rate of return): Internal rate of return for periodic payments.
• MIRR (modified internal rate of return): Modified internal rate of return for periodic payments, which takes into consideration both the cost of investment and the interest received on investment.

Syntax

XIRR <FactID, Guess, SortBy> (Payment, Dates)

Where:

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

Guess is a parameter that indicates an estimate assumed to be close to the result sought. The default value is 0.1, which can be thought of as representing an expected internal rate of return of 10 percent.

Payment is a fact or metric representing a list of numbers for which the internal rate of return is sought.

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

Usage Notes

The list of values must contain at least one positive entry and one negative entry.

The XIRR function uses the secant method to determine the internal rate of return, which adjusts the initial guess as required to return a result. The final result must be within 0.000001 percent to be considered valid.

Payments, whether positive or negative, must be entered in the desired sequence, as this is the order in which entries are interpreted for calculation.

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 IRR function to determine the internal rate of return. Instead, you can use the XIRR function, which also evaluates the date of each payment to determine an internal rate of return. A new metric is added to the report which uses the following definition:

XIRR(Payment, Date){}

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

The internal rate of return considering the payments and the dates of those payments is 21.93. Notice that while there is a single value for XIRR, 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 XIRR result in a text field along with additional visualizations of the data, as shown below.