IRR error

[Anonymous]

Dear All,

I want to calculate the IRR but when I try to I get an error. What do I do wrong?

I used the following code:

ROLLING IRR =

VAR minIndex =

CALCULATE ( MIN ( 'TEST - Working Capital'[Index] ), ALL ( 'TEST - Working Capital' ) )

RETURN

IF (

'TEST - Working Capital'[Index] > minIndex && NOT(ISBLANK('TEST - Working Capital'[Data])),

CALCULATE (

XIRR (

'TEST - Working Capital',

'TEST - Working Capital'[Cash flow amount],

'TEST - Working Capital'[Transaction Date]

),

FILTER (

'TEST - Working Capital',

EARLIER ( 'TEST - Working Capital'[Index] ) >= 'TEST - Working Capital'[Index]

)

)

)

I filtered out one loan as an example.

Transaction Date	Cash flow amount	Transaction Type	Index	ROLLING IRR
1-10-2015	1.000.000, -€	Facility - Purchase	128	#ERROR
31-10-2015	€ 5.416,67	Loan - Interest Payment	136	#ERROR
30-11-2015	€ 5.416,67	Loan - Interest Payment	145	#ERROR
31-12-2015	€ 5.416,67	Loan - Interest Payment	168	#ERROR
31-12-2015	6.000, -€	Facility - Commitment Increase	170	#ERROR
31-1-2016	€ 5.449,17	Loan - Interest Payment	190	#ERROR
29-2-2016	€ 5.449,17	Loan - Interest Payment	207	#ERROR
31-3-2016	€ 5.449,17	Loan - Interest Payment	228	#ERROR
30-4-2016	€ 5.449,17	Loan - Interest Payment	258	#ERROR
31-5-2016	€ 5.449,17	Loan - Interest Payment	274	#ERROR
30-6-2016	€ 5.449,17	Loan - Interest Payment	303	#ERROR
31-7-2016	€ 5.449,17	Loan - Interest Payment	326	#ERROR
31-8-2016	€ 5.449,17	Loan - Interest Payment	340	#ERROR
30-9-2016	€ 5.449,17	Loan - Interest Payment	373	#ERROR
31-10-2016	€ 5.449,17	Loan - Interest Payment	402	#ERROR
30-11-2016	€ 5.449,17	Loan - Interest Payment	428	#ERROR
31-12-2016	€ 5.449,17	Loan - Interest Payment	454	#ERROR
31-12-2016	1.006, -€	Facility - Commitment Increase	465	#ERROR
31-1-2017	€ 5.454,62	Loan - Interest Payment	486	#ERROR
28-2-2017	€ 5.454,62	Loan - Interest Payment	512	#ERROR
31-3-2017	€ 5.454,62	Loan - Interest Payment	559	#ERROR
30-4-2017	€ 5.454,62	Loan - Interest Payment	574	#ERROR
31-5-2017	€ 5.454,62	Loan - Interest Payment	601	#ERROR
30-6-2017	€ 5.454,62	Loan - Interest Payment	634	#ERROR
31-7-2017	€ 3.818,23	Loan - Interest Payment	651	#ERROR
31-7-2017	€ 1.007.006,	Facility - Paydown	662	#ERROR

regards, Toon

[amitchandak]

@Anonymous , Can share the error, which it is giving. check any hyperlink to get that

also, check

https://www.youtube.com/watch?v=N1WRSFuaFmE

https://community.powerbi.com/t5/Desktop/Create-a-measure-to-calculate-IRR-live/m-p/186302

[Anonymous]

@amitchandak , he just gives me an error that he can't find a solution. (The XIRR function couldn't find a solution.)

[amitchandak · Accepted solution]

@Anonymous , I see need of one change, if this a measure then we need max in place of earlier

ROLLING IRR =
VAR minIndex =
CALCULATE ( MIN ( 'TEST - Working Capital'[Index] ), ALL ( 'TEST - Working Capital' ) )
RETURN
IF (
'TEST - Working Capital'[Index] > minIndex && NOT(ISBLANK('TEST - Working Capital'[Data])),
CALCULATE (
XIRR (
'TEST - Working Capital',
'TEST - Working Capital'[Cash flow amount],
'TEST - Working Capital'[Transaction Date]
),
FILTER (
allselected('TEST - Working Capital'),
MAx ( 'TEST - Working Capital'[Index] ) >= 'TEST - Working Capital'[Index]
)
)
)

Trying to check in a file

[Anonymous]

@amitchandak , thanks that was the bug. Looked at it too long to see the error.

I want to make this a 'Rolling IRR' based on different projects. So that he changes the IRR everyday day per project. How  should I change the formula then?

[amitchandak]

@Anonymous , I tried out, Seem like for me only working combination are

for grand total/card

XIRR (
('TEST - Working Capital'),
calculate(Sum('TEST - Working Capital'[Cash flow amount])),
'TEST - Working Capital'[Transaction Date]
)

or

XIRR (
allselected('TEST - Working Capital'),
calculate(Sum('TEST - Working Capital'[Cash flow amount])),
'TEST - Working Capital'[Transaction Date]
)

refer to this video for around 6 Min, and check the suggestion. if that can help

https://everyday.cc/powerbi/learning/videos/curbal-dax-fridays-142-irr-xirr-and-xnpv-in-dax/