XIRR
Description:
This function is used to return the internal rate of return for a specified set_expression as per specified numeric_expression and guess.
Syntax:
XIRR(Numeric_Expression1, Set_Expression, Numeric_Expression2, Numeric_Expression3, Guess)
Return type:
SCALAR
Parameters:
Parameter | Description |
|---|---|
Numeric_Expression1 | It is a valid Numeric expression, typically an MDX expression of cell coordinates that returns a numeric value. It should be a series of cash flows that corresponds to a schedule of payments in dates. |
Set_Expression | It is a valid MDX expression that returns a set. |
Numeric_Expression2 | It is a valid Numeric expression, typically an MDX expression of cell coordinates that returns a numeric value. It should be a series of net asset value that corresponds to a schedule of payments in dates. |
Numeric_Expression3 | It is a valid Numeric expression, typically an MDX expression of cell coordinates that returns a numeric value. It is optional, if specified we consider first cash flow value as opening nav else default is taken from cash flow value. |
Guess | It is a valid Numeric expression that specifies a number that you guess is close to the result of XIRR. It is Optional, if specified then we consider provided guess value else default value of guess 0.1 is considered. |
Example:
WITH MEMBER [Measures].[Investment IRR] AS
XIRR([Date].[Calendar].[Month].Members, [Measures].[Cash Flow], 0.1)
MEMBER [Measures].[IRR Percent] AS
Format([Measures].[Investment IRR], 'Percent')
SELECT {[Measures].[Investment IRR], [Measures].[IRR Percent]} ON COLUMNS
FROM [AdventureWorksMF_ValueBased]