Creating expressions using XIRR function
- Sarabjeet Kaur
The XIRR function in Kyvos calculates the Internal Rate of Return (IRR) for a series of cash flows occurring at irregular intervals (non-periodic).
Enhanced XIRR Calculation Support in Kyvos 2025.3.1
The Kyvos 2025.3.1 supports enhanced syntax for XIRR calculations, featuring the following enhancements:
Ability to visualize XIRR using time series data.
Support for date dimensions at the year, month, week, or day level.
Filtering by specific date ranges is supported.
Support for scenarios where the initial net cash flow value is zero.
Performance optimizations ensure that XIRR calculations remain accurate and efficient, even with large datasets.
Points to know:
The solution is certified for Spark architecture only.
You can filter visualizations by year, month, week, day, date, model portfolio, and other attributes.
XIRR results generally match those from Excel. In rare cases where they don’t, the results will align with LibreOffice.
The Opening_CF column must be a semi-additive measure using the first non-empty child function.
The Closing_NAV column must be a semi-additive measure using the last non-empty child function.
Supported XIRR Syntax
Case 1:
XIRR[Numeric_Expression1, Set_Expression, Numeric_Expression2, Guess]Case 2:
XIRR (Numeric Expression 1,Start Date: End Date, Numeric Expression2,Numeric Expression3, Guess)
The formula uses the following arguments:
Case1: XIRR[Numeric_Expression1, Set_Expression, Numeric_Expression2, Guess]
Argument | Description |
|---|---|
Numeric_Expression1 | It is a valid numeric 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 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
The formula uses the following arguments:
Case2: XIRR (Numeric Expression 1,Start Date: End Date, Numeric Expression2,Numeric Expression3, Guess)
Argument | Description |
|---|---|
Numeric_Expression1 | It is a valid numeric 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. |
Numeric_Expression3 (Optional) | It is a valid numeric expression representing cell coordinates that return a numeric value. It should consist of a series of opening cash flows corresponding to a schedule of payments on specific dates. Note: If numeric expression3 is used in XIRR expression, while evaluating the expression, the first value of the Opening Cash Flow, the middle value of the Net Cash Flow, and the last value of the Closing_NAV will be used. |
Start Date | This is the date when the initial investment is made, the first cash outflow. |
End Date | This is the date when the investment is redeemed, or the final cash inflow occurs. |
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,
XIRR([Measures].[net_cashflow],IncludeViewFilter([Date].[H_Date].[Date]),[Measures].[closing_nav],[Measures].[opening_cf])The XIRR expression created for semantic models
XIRR expression
XIRR expression in Workbook
XIRR expression in Microsoft Power BI
Copyright Kyvos, Inc. All rights reserved.