XIRR Function - CALC Filter Value

[jamessun1000]

Hi 

I have a data table with raw information. This table lists all project infomration required to calculate XIRR. 

This includes cash flows, date information and is listed in one consolidated table (i.e. there are multiple Projects referred to as Cashflow ID's within the one table). 

How do I calculate the XIRR on the NetCashFlowBeforeInterest cash flows specific to the one particular CashFlowID?

CashFlowID-Raw	Period End-Raw	Period-Raw	NetCashFlowBeforeInterest- Raw	EquityCashFlow- Raw	XIRR-Raw
1	31-07-20 0:00	1	-20000	-20000	14.0%
1	31-08-20 0:00	2	400000	500000	14.0%
2	30-09-20 0:00	1	-30000	-30000	10.0%
2	31-10-20 0:00	2	200000	250000	10.0%
CashFlowID-Sum	XIRR-Sum
1	14.0%
2	10.0%

 (Raw Data table and summary table presented above). 

Ideally I would like the XIRR value returned within the table (as above) or calculated in a seperate table linking to the cash flows. 

At the moment I am using the following:

XIRR - Raw = CALCULATE(XIRR(Raw Data Table,Raw Table[NetCashFlowBeforeInterest],Raw Table'[PeriodDate]),FILTER(Raw Table,Sum Table[CasfhlowID-Sum]=Raw Table[CashFlowID]))

Please help.

[JackSelman]

Hi @jamessun1000 - I've just encountered this very same issue myself. Did you ever manage to resolve this?

[phteve]

I have the same question. Thanks in advance!

[v-lili6-msft]

Hi@jamessun1000

Can you show me the formula that you get the result (14.0%,14.0%,10.0%,10.0%) in the raw data table.

I do a measure but the result is different from your "XIRR - Raw" . So please show me more details for me.

thank you .

Best Regards,

Lin

[jamessun1000]

Hi @v-lili6-msft

The 14% and 10% is there for illustration only to differentiate betwen detailed / raw data and the summary table. i.e. the 14% and 10% represent two unique projects IRR with the cash flows, dates and project identifier all in a single table (Rather than a pivot column).  

I cannot get the XIRR formula to work when there are multiple project cash flows in the one column and I attempt to add in a filter. If i remove the filter and just do XIRR (TABLE,Values,Dates) it is one IRR value for every single row (as it thinks its one continuos cash flow)

[jamessun1000]

@v-lili6-msft

An example of the data I am looking at is as follows: 

NetCashFlowBeforeInterest	PeriodDate	CashFlowID	CorporateTax	Period End	Period	XIRR
(250,057)	15-05-17	145	0	31-05-17	0
-	15-07-17	146	0	31-07-17	0
(348,306)	15-06-17	145	0	30-06-17	1
(250,010)	15-08-17	146	0	31-08-17	1
(7,759,974)	15-07-17	145	0	31-07-17	2
(3,001,440)	15-09-17	146	0	30-09-17	2
(151,709)	15-08-17	145	0	31-08-17	3
(3,850)	15-10-17	146	0	31-10-17	3
(142,823)	15-09-17	145	0	30-09-17	4
(180,968)	15-11-17	146	0	30-11-17	4
(157,490)	15-10-17	145	0	31-10-17	5
(50,553)	15-12-17	146	0	31-12-17	5
(155,955)	15-11-17	145	0	30-11-17	6
(18,794)	15-01-18	146	0	31-01-18	6
(621,471)	15-12-17	145	0	31-12-17	7
(23,427)	15-02-18	146	0	28-02-18	7
(235,838)	15-01-18	145	0	31-01-18	8
(43,211)	15-03-18	146	0	31-03-18	8
(138,249)	15-02-18	145	0	28-02-18	9
(35,640)	15-04-18	146	0	30-04-18	9
36,216	15-03-18	145	0	31-03-18	10
51,713	15-05-18	146	0	31-05-18	10
(56,211)	15-04-18	145	0	30-04-18	11
-	15-06-18	146	0	30-06-18	11
(605,218)	15-05-18	145	0	31-05-18	12
(54,664)	15-07-18	146	0	31-07-18	12
(456,878)	15-06-18	145	0	30-06-18	13
(94,088)	15-08-18	146	0	31-08-18	13
(701,458)	15-07-18	145	0	31-07-18	14
(52,726)	15-09-18	146	0	30-09-18	14
(941,692)	15-08-18	145	0	31-08-18	15
(8,138,447)	15-10-18	146	0	31-10-18	15
(1,153,876)	15-09-18	145	0	30-09-18	16
(624,164)	15-11-18	146	0	30-11-18	16
(5,862,441)	15-10-18	145	0	31-10-18	17
(600,640)	15-12-18	146	0	31-12-18	17
(5,425,137)	15-11-18	145	0	30-11-18	18
(499,039)	15-01-19	146	0	31-01-19	18
(39,483,915)	15-12-18	145	0	31-12-18	19
(612,957)	15-02-19	146	0	28-02-19	19
(729,184)	15-01-19	145	0	31-01-19	20
(469,591)	15-03-19	146	0	31-03-19	20
(708,178)	15-02-19	145	0	28-02-19	21
(1,966,873)	15-04-19	146	0	30-04-19	21
(669,823)	15-03-19	145	0	31-03-19	22
(119,278)	15-05-19	146	0	31-05-19	22
(79,249,307)	15-04-19	145	0	30-04-19	23
(1,833)	15-06-19	146	0	30-06-19	23
(2,011,479)	15-05-19	145	0	31-05-19	24
38,316	15-07-19	146	0	31-07-19	24
(1,477,223)	15-06-19	145	0	30-06-19	25
19,431,443	15-08-19	146	0	31-08-19	25
(1,496,638)	15-07-19	145	0	31-07-19	26
-	15-09-19	146	0	30-09-19	26
(1,851,760)	15-08-19	145	0	31-08-19	27
30,284	15-10-19	146	0	31-10-19	27
(2,045,514)	15-09-19	145	0	30-09-19	28
(1,190)	15-11-19	146	0	30-11-19	28
(2,052,810)	15-10-19	145	0	31-10-19	29
-	15-12-19	146	0	31-12-19	29
(2,552,881)	15-11-19	145	0	30-11-19	30
(170)	15-01-20	146	0	31-01-20	30
(2,534,006)	15-12-19	145	0	31-12-19	31
(1,190)	15-02-20	146	0	29-02-20	31
(2,502,405)	15-01-20	145	0	31-01-20	32
-	15-03-20	146	0	31-03-20	32
(2,377,231)	15-02-20	145	0	29-02-20	33
(170)	15-04-20	146	0	30-04-20	33
(1,980,918)	15-03-20	145	0	31-03-20	34
(1,190)	15-05-20	146	0	31-05-20	34
(7,662,700)	15-04-20	145	0	30-04-20	35
-	15-06-20	146	0	30-06-20	35
116,230,935	15-05-20	145	0	31-05-20	36
(170)	15-07-20	146	0	31-07-20	36
(1,587,657)	15-06-20	145	0	30-06-20	37
(2,891)	15-08-20	146	0	31-08-20	37
(1,475,437)	15-07-20	145	0	31-07-20	38
-	15-09-20	146	0	30-09-20	38
(1,967,288)	15-08-20	145	0	31-08-20	39
(1,232)	15-10-20	146	0	31-10-20	39
(2,233,785)	15-09-20	145	0	30-09-20	40
(1,190)	15-11-20	146	0	30-11-20	40
(2,562,115)	15-10-20	145	0	31-10-20	41
-	15-12-20	146	0	31-12-20	41
(2,716,351)	15-11-20	145	0	30-11-20	42
(170)	15-01-21	146	0	31-01-21	42
(1,892,247)	15-12-20	145	0	31-12-20	43
(1,190)	15-02-21	146	0	28-02-21	43
(13,432,056)	15-01-21	145	0	31-01-21	44
1,807,504	15-03-21	146	0	31-03-21	44
131,964,060	15-02-21	145	0	28-02-21	45

This represents two unique projects (Project ID 145 and 146). How do I calculate the IRR of each Project to be presented in the above table or calculated in another table on a project by project basis. 

I do not want a rolling XIRR as I can see this in other sections of the forum.