XIRR

Calculates the rate of return (within .000001%) of investments that have irregular payment schedules.

Note

XIRR is an AtScale beta feature.

Syntax

XIRR(Payment_Measure, Date_Attribute[, Initial_Guess][, Default_Value])

Input parameters

Payment_Measure

Required. The measure that represents the payment. This must have the same size and order as the Date_Attribute argument.

Date_Attribute

Required. The attribute that represents the payment schedule. This should be either a secondary attribute or an attribute of a single-level degenerate dimension, with values of type date, datetime, or timestamp. Additionally, Date_Attribute must have the same size and order as the Payment_Measure argument.

Initial_Guess

Optional. The starting value of the rate. This must be a static floating point value. If this argument is not specified, it defaults to 0.1.

Default_Value

Optional. The value that is returned if the rate cannot be accurately calculated. If this argument is not specified, it defaults to NULL.

Examples

The following example calculates the rate of return for [Measures].[Reseller Sales Amount Local] based on the schedule [DateCustom].[Retail445].[Reporting Day], using the default default values for the initial guess and default return value:

XIRR([Measures].[Reseller Sales Amount Local], [DateCustom].[Retail445].[Reporting Day])

The following example calculates the rate of return for [Measures].[Internet Sales Amount Local] based on the schedule [DateCustom].[Retail445].[Reporting Day], using 0.1 as the initial guess and 0 as the default return value:

XIRR([Measures].[Internet Sales Amount Local], [DateCustom].[Retail445].[Reporting Day], 0.1, 0)