Forum Discussion

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago
Solved

Calculate XIRR

Hi,

My objective is to calculated the XIRR as of 31 January 2023 using a measure.  These are my measures

 

Purchase date of shares = MIN(Shares[Date of Purchase])
Cost of shares (Rs. cr) = -divide(SUM(Shares[Total cost of shares in Rs]),10^7)
Market value of shares (Rs. cr) = divide([Number of shares]*[MV per share],10^7)

 

 In the Table visual when i drag Investee Company, these measures reuturn the following results

Purchase date of shares = 11 January 1999

Cost of shares = -1.41

Market value of shares = 397.12

In a slicer, i select the end date as 31 January 2023.

In MS Excel, the XIRR as of 31 January 2023, would be 26.41%.

How can i do this calculation in DAX via a measure.

Thank you for your help.

 

Need Help

10 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    I have made some progress.  I wrote the following 2 DAX formulas:

    Calculated table formula

     

     

    Table 2 = DATATABLE("Period",DATETIME,"CF",CURRENCY,{{"01/11/1999",-1.41},{"31/01/2023",397.12}})

     

     

    Measure

     

     

    XIRR of shares = XIRR('Table2',[CF],[Period])

     

     

    I get the correct XIRR result i.e. 26.41%

    The modification that i wish to bring here is that i would like to replace hard coded entries in the DATATABLE function with measures that i have written.  I'd like

    1. 01/11/1999 to be replaced with the measure [Purchase date of shares]
    2. -1.41 to be replaced with the measure [Cost of shares (Rs. cr)]
    3. 31/01/2023 to be replaced with the measure [Selected period]
    4. 397.12 to be replaced with the measure [Market value of shares (Rs. cr)]
    How can i do this?
    Thank you for your help.