Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

IRR error

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 DateCash flow amountTransaction TypeIndexROLLING IRR
1-10-20151.000.000, -€Facility - Purchase128#ERROR
31-10-2015€ 5.416,67Loan - Interest Payment136#ERROR
30-11-2015€ 5.416,67Loan - Interest Payment145#ERROR
31-12-2015€ 5.416,67Loan - Interest Payment168#ERROR
31-12-20156.000, -€Facility - Commitment Increase170#ERROR
31-1-2016€ 5.449,17Loan - Interest Payment190#ERROR
29-2-2016€ 5.449,17Loan - Interest Payment207#ERROR
31-3-2016€ 5.449,17Loan - Interest Payment228#ERROR
30-4-2016€ 5.449,17Loan - Interest Payment258#ERROR
31-5-2016€ 5.449,17Loan - Interest Payment274#ERROR
30-6-2016€ 5.449,17Loan - Interest Payment303#ERROR
31-7-2016€ 5.449,17Loan - Interest Payment326#ERROR
31-8-2016€ 5.449,17Loan - Interest Payment340#ERROR
30-9-2016€ 5.449,17Loan - Interest Payment373#ERROR
31-10-2016€ 5.449,17Loan - Interest Payment402#ERROR
30-11-2016€ 5.449,17Loan - Interest Payment428#ERROR
31-12-2016€ 5.449,17Loan - Interest Payment454#ERROR
31-12-20161.006, -€Facility - Commitment Increase465#ERROR
31-1-2017€ 5.454,62Loan - Interest Payment486#ERROR
28-2-2017€ 5.454,62Loan - Interest Payment512#ERROR
31-3-2017€ 5.454,62Loan - Interest Payment559#ERROR
30-4-2017€ 5.454,62Loan - Interest Payment574#ERROR
31-5-2017€ 5.454,62Loan - Interest Payment601#ERROR
30-6-2017€ 5.454,62Loan - Interest Payment634#ERROR
31-7-2017€ 3.818,23Loan - Interest Payment651#ERROR
31-7-2017€ 1.007.006,Facility - Paydown662#ERROR

 

regards, Toon

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

       

       

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        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