XIRR for multi-period

Question

Hi everyone,&nbsp;&nbsp;I'm new here and thank you for your help in advance.&nbsp;&nbsp;I am trying to calculate XIRR for commercial real estate returns. The differences compared to stocks XIRR are, I have cash flows from operations and closing value for the net asset value of the property at each quarter end.&nbsp;&nbsp;&nbsp;My issue is, if I select Q1 and Q2 of 2020, XIRR will include the March 31 and April 1 closing and opening balances when it should only consider January 1 and June 30 for a YTD XIRR. Does anyone know a dynamic way that these values can be excluded?&nbsp;&nbsp;My sample data below.&nbsp;Date$Type1/1/2020-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,000Opening2/1/2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10Cash flow2/15/2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10Cash flow3/31/2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,010Closing4/1/2020-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,010Opening4/7/2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20Cash flow5/3/2020-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5Cash flow6/30/2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,050Closing7/1/2020-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,050Opening7/10/2020-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10Cash flow8/10/2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20Cash flow9/10/2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20Cash flow9/30/2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,060Closing&nbsp;Thank you!&nbsp;

anonymous · Accepted Answer

Hi&nbsp;user1164&nbsp;
You can refer to the following suggestion.
First, you need to create a data table

Then create a measure
Measure = var a=FILTER(ALLSELECTED('Table'),[Type]="Opening"&amp;&amp;QUARTER([Date])=MINX(VALUES('Table 2'[Date]),QUARTER([Date])))
var b=FILTER(ALLSELECTED('Table'),[Type]="Closing"&amp;&amp;QUARTER([Date]) =MAXX(VALUES('Table 2'[Date].[QuarterNo]),[Date].[QuarterNo]))
return IF(ISFILTERED('Table 2'[Date].[Quarter]),IF(OR(SELECTEDVALUE('Table'[Date])=MINX(a,[Date]),SELECTEDVALUE('Table'[Date])=MAXX(b,[Date])),1,0),1)
Then put the measure to the visual filter

Output

&nbsp;
Best Regards!
Yolo Zhu
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

&nbsp;

Forum Discussion

XIRR for multi-period

2 Replies

Related content

Calculate XIRR

XIRR calculation using DAX

XIRR Calculation Error

Aggregate XIRR Calculation Help

xIRR calculation discrepancy

Date	$	Type
1/1/2020	- 1,000	Opening
2/1/2020	10	Cash flow
2/15/2020	10	Cash flow
3/31/2020	1,010	Closing
4/1/2020	- 1,010	Opening
4/7/2020	20	Cash flow
5/3/2020	- 5	Cash flow
6/30/2020	1,050	Closing
7/1/2020	- 1,050	Opening
7/10/2020	- 10	Cash flow
8/10/2020	20	Cash flow
9/10/2020	20	Cash flow
9/30/2020	1,060	Closing