Document toolboxDocument toolbox

Creating expressions using XIRR function

Owned by Sarabjeet Kaur
Last updated: Oct 25, 2024
3 min read

The XIRR function in Excel 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

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.
NOTE: It is optional. If specified, the provided guess value is considered. Otherwise, the default guess value of 0.1 will be used.

  • 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: 

  1. Create or edit an existing semantic model.

  2. Choose to create a new measure.

  3. In Measure Properties, select Expression.

  4. 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.

    image-20241024-105311.png

  5. 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.

    image-20241025-071808.png

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. 

  1. To find or replace content, click the Find/Replace icon, and enter what to find.

    1. Select Match Case icon (Aa) if needed.

    2. 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.

    3. To delete the entry for that item, click the X at the end of the line.

  2. Click Validate to validate your criteria. 
    If you have made changes and don’t want to keep them, click Cancel.

  3. 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.