Creating expressions using XIRR function
The XIRR function in Kyvos calculates the Internal Rate of Return (IRR) for a series of cash flows occurring at irregular intervals (non-periodic).
Note
For the net asset value in the Kyvos semantic model, the column must be a semi-additive measure with the last non-empty child. The use of semi-additive measures is supported only in index-based semantic models.
Syntax of XIRR Function
XIRR[Numeric_Expression1, Set_Expression, Numeric_Expression2, Guess]
The formula uses the following arguments:
Argument | Description |
---|---|
Numeric_Expression1 | It is a valid numeric expression, typically an MDX expression, representing cell coordinates that return a numeric value. It should consist of a series of cash flows corresponding to a schedule of payments on specific dates. |
Set_Expression | It is a valid MDX expression that returns a set. |
Numeric_Expression2 | It represents a valid numeric expression, typically an MDX expression of cell coordinates that returns a numeric value. It should be a series of net asset values corresponding to a schedule of payment dates. |
Guess (Optional) | A valid numeric expression that specifies a number you believe is close to the result of XIRR. |
Return value: SCALAR
For example,
WITH MEMBER [Measures].[X] AS XIRR([Measures].[CashFlows], [XIRR].[H_XIRR].[Date], [Measures].[NetAssetValue])Â SELECT [Measures].[X] ON 0 FROM [AdventureWorksMF]
To use the XIRR expression when creating calculated measures in semantic models, perform the following steps:Â
Create or edit an existing semantic model.
Choose to create a new measure.
In Measure Properties, select Expression.
To write an XIRR expression, start typing an expression in the editor section of the dialog box. As you enter text, suggested functions, semantic models, dimensions, members, and so on, are displayed. Click a name, then press Enter to use that item.
You can also see a description of the selected function.
To view a list of functions along with the detailed description, navigate to the Functions tab and select a function from the list.
Tips
Double-click on the dialog header to access the full-screen mode.
Use the Undo and Redo icons as needed as you create your calculated expression.Â
To find or replace content, click the Find/Replace icon, and enter what to find.
Select Match Case icon (Aa) if needed.
If you want to replace the found value, click the arrow to the left of Find, enter the text to replace, and specify whether to replace all.
To delete the entry for that item, click the X at the end of the line.
Click Validate to validate your criteria.Â
If you have made changes and don’t want to keep them, click Cancel.Click Save.
For further details about formula syntax and usage of the XIRRÂ function, see Microsoft documentation.
XIRR expressions used in worksheet
Â
Copyright Kyvos, Inc. All rights reserved.