Forum Discussion
Calculate XIRR
I want to create XIRR and Sum for the following file
| Category 01 | Category 02 | Category 03 | Category 04 | Category 05 | Transaction Type | Date of Transaction | Units | Price | Amount |
| XYZ | SPOC0001 | SC01 | C01 | I01 | Opening | 01-Jan-21 | 10000 | 12 | -120000 |
| XYZ | SPOC0001 | SC01 | C01 | I01 | Purchase | 05-Jan-21 | 2000 | 14 | -28000 |
| XYZ | SPOC0001 | SC01 | C01 | I01 | Sale | 10-Jan-21 | 4000 | 13 | 52000 |
| XYZ | SPOC0001 | SC01 | C01 | I01 | Closing | 31-Jan-21 | 8000 | 13.5 | 108000 |
| XYZ | SPOC0002 | SC01 | C01 | I01 | Opening | 01-Jan-21 | 15000 | 12 | -180000 |
| XYZ | SPOC0002 | SC01 | C01 | I01 | Purchase | 05-Jan-21 | 5000 | 14 | -70000 |
| XYZ | SPOC0002 | SC01 | C01 | I01 | Sale | 10-Jan-21 | 8000 | 13 | 104000 |
| XYZ | SPOC0002 | SC01 | C01 | I01 | Closing | 31-Jan-21 | 12000 | 13.5 | 162000 |
| XYZ | SPOC0003 | SC02 | C01 | I01 | Opening | 01-Jan-21 | 20000 | 12 | -240000 |
| XYZ | SPOC0003 | SC02 | C01 | I01 | Purchase | 05-Jan-21 | 4000 | 14 | -56000 |
| XYZ | SPOC0003 | SC02 | C01 | I01 | Sale | 10-Jan-21 | 8000 | 13 | 104000 |
| XYZ | SPOC0003 | SC02 | C01 | I01 | Closing | 31-Jan-21 | 16000 | 13.5 | 216000 |
I want to Calculate XIRR for Category 02 - SPOC0001 Item. In excel i will do in the following way. (filtered data - first four raws from the above table)
| Category 01 | Category 02 | Category 03 | Category 04 | Category 05 | Transaction Type | Date of Transaction | Units | Price | Amount |
| XYZ | SPOC0001 | SC01 | C01 | I01 | Opening | 01-Jan-21 | 10000 | 12 | -120000 |
| XYZ | SPOC0001 | SC01 | C01 | I01 | Purchase | 05-Jan-21 | 2000 | 14 | -28000 |
| XYZ | SPOC0001 | SC01 | C01 | I01 | Sale | 10-Jan-21 | 4000 | 13 | 52000 |
| XYZ | SPOC0001 | SC01 | C01 | I01 | Closing | 31-Jan-21 | 8000 | 13.5 | 108000 |
| XIRR | 259% | ||||||||
| Income | 12000 | ||||||||
I want to Calculate XIRR for Category 03 - SC01 Item. In excel i will do in the following way. (filtered data - eight four raws from the above table)
| Category 01 | Category 02 | Category 03 | Category 04 | Category 05 | Transaction Type | Date of Transaction | Units | Price | Amount |
| XYZ | SPOC0001 | SC01 | C01 | I01 | Opening | 01-Jan-21 | 10000 | 12 | -120000 |
| XYZ | SPOC0001 | SC01 | C01 | I01 | Purchase | 05-Jan-21 | 2000 | 14 | -28000 |
| XYZ | SPOC0001 | SC01 | C01 | I01 | Sale | 10-Jan-21 | 4000 | 13 | 52000 |
| XYZ | SPOC0001 | SC01 | C01 | I01 | Closing | 31-Jan-21 | 8000 | 13.5 | 108000 |
| XYZ | SPOC0002 | SC01 | C01 | I01 | Opening | 01-Jan-21 | 15000 | 12 | -180000 |
| XYZ | SPOC0002 | SC01 | C01 | I01 | Purchase | 05-Jan-21 | 5000 | 14 | -70000 |
| XYZ | SPOC0002 | SC01 | C01 | I01 | Sale | 10-Jan-21 | 8000 | 13 | 104000 |
| XYZ | SPOC0002 | SC01 | C01 | I01 | Closing | 31-Jan-21 | 12000 | 13.5 | 162000 |
| XIRR | 223% | ||||||||
| Income | 28000 |
I think it is a very basic query but i am knew to Power BI Rolling XIRR Calculation
1 Reply
Chirag1981 , power bi has released xirr function
https://docs.microsoft.com/en-us/dax/xirr-function-dax
I have not checked the usage of this. But I tried few function in this video (PPMT, IPMT, CUMIPMT, CUMPRINC, RRI, PMT, PV, FV, Rate, NPER)
The approach might help https://www.youtube.com/watch?v=9VYqbj2h4zE
