Forum Discussion

Nagesh20's avatar
Nagesh20
Icon for Helper I rankHelper I
2 years ago
Solved

Calculate XIRR from two tables

Hi all,

 

Trying to calcaulate XIRR from the below two tables. It woud be helpful if anyone can provide the right DAX formula.

 

Getting IRR in excel is 17.13%

 

Cashflow table

Date           Fund         Cashflow

3/1/2022Quantum2000
3/1/2022LIC500
4/1/2022LIC300
4/1/2022Quantum3000

 

NAV table

Date          Fund       Cashflow

3/1/2024Quantum-3000
3/1/2024Quantum-4000
3/1/2024LIC-900

 

Relationship

 

Need Help
  • AmiraBedh's avatar
    AmiraBedh
    2 years ago

    Try the following :

     

    IRR2 = 
SUMMARIZE(
    Transactions_All,
    Transactions_All[Fund],
    "IRR", [Your_IRR_Calculation_Measure] // I assume you have a measure that calculates IRR
)

5 Replies

  • Nagesh20's avatar
    Nagesh20
    Icon for Helper I rankHelper I
    2 years ago

    Hi AmiraBedh 

     

    I am getting the correct IRR number of 17.13% with this formula 

     

    IRR =
    VAR Total_Cashflow = UNION(SELECTCOLUMNS(Cashflow,"Cashflow",Cashflow[Cashflow],"date",Cashflow[Date]),
    SELECTCOLUMNS(NAV,"Cashflow",NAV[Cashflow],"date",NAV[Date]))
    Return
    XIRR(Total_Cashflow,[Cashflow],[date])
     
    But when i apply the filter for the specific "Fund", it's getting different IRR number 
    In Excel, Getting IRR for Quantum is 18.81%, for LIC is 6.16%
     
    In reality, there are huge list of fund names to filter
     
    Please help me how to get this sorted out.
    • Nagesh20's avatar
      Nagesh20
      Icon for Helper I rankHelper I
      2 years ago

      Hi AmiraBedh 

       

      Finally it's resolved it by creating table and created a measure function

       

      Transactions_All = UNION(SELECTCOLUMNS(Cashflow,"Cashflow",Cashflow[Cashflow],"date",Cashflow[Date],"Account",Cashflow[Account],"Fund",Cashflow[Fund]),
      SELECTCOLUMNS(NAV,"Cashflow",NAV[Cashflow],"date",NAV[Date],"Account",NAV[Account],"Fund",NAV[Fund]))
       
      IRR2 = XIRR(Transactions_All,Transactions_All[Cashflow],Transactions_All[date])
       
      I would like to get IRR reflect in a table with Fund name like below. It would be great if provide a solution on this
       
      FundIRR2
      Quantum18.81%
      LIC6.61%
      • AmiraBedh's avatar
        AmiraBedh
        Icon for Super User rankSuper User
        2 years ago

        Try the following :

         

        IRR2 = 
SUMMARIZE(
    Transactions_All,
    Transactions_All[Fund],
    "IRR", [Your_IRR_Calculation_Measure] // I assume you have a measure that calculates IRR
)