I am trying to calculate an XIRR function in Power BI but I am having difficulties. Specifically, we are looking to create a dynamic IRR function in Power BI that would allow us to calculate the IRR for each investment based on a certain period end – for example, would like to calculate a since inception to-date XIRR for “investment A” but also have the ability to back into prior period XIRRs as well. As you can see, we have multiple underlying investments, each which has their own specific IRR calculation and that is where we are getting tripped up. Basically, the end goal is to be able to choose a date from a drop down, and have a table showing each investment and their respective IRR based on the chosen date. One thing to note is that the data includes a “Commitment” Type, which needs to be excluded from the calculation as well. Any tips or help is greatly appreciated.
Here is some Sample Data. Let me know if there is anything else I can provide to help the process.
| Security | Type | Value | Trade Date |
| Investment A | Commitment | 100 | 1/1/2022 |
| Investment A | Contribution | -50 | 8/31/2022 |
| Investment A | Valuation | 50 | 9/30/2022 |
| Investment A | Income | 5 | 11/30/2022 |
| Investment A | Distribution | 25 | 12/15/2022 |
| Investment A | Valuation | 23 | 12/31/2022 |
| Investment A | Contribution | -50 | 2/1/2023 |
| Investment A | Distribution | 5 | 3/15/2023 |
| Investment A | Valuation | 70 | 3/31/2023 |
| Investment A | Distribution | 15 | 4/15/2023 |
| Investment B | Commitment | 500 | 1/1/2022 |
| Investment B | Contribution | -200 | 8/15/2022 |
| Investment B | Contribution | -300 | 1/15/2023 |
| Investment B | Distribution | 100 | 9/15/2022 |
| Investment B | Income | 50 | 10/31/2022 |
| Investment B | Valuation | 103 | 9/30/2022 |
| Investment B | Valuation | 55 | 12/31/2022 |
| Investment B | Valuation | 275 | 3/31/2023 |
| Investment B | Distribution | 100 | 2/15/2023 |
| Investment C | Commitment | 50 | 1/1/2022 |
| Investment C | Valuation | 15.5 | 12/31/2022 |
| Investment C | Valuation | 16 | 3/31/2023 |
| Investment C | Valuation | 10 | 6/30/2023 |
| Investment C | Contribution | -30 | 10/15/2022 |
| Investment C | Contribution | -20 | 4/15/2023 |
| Investment C | Distribution | 15 | 11/15/2022 |
| Investment C | Distribution | 25 | 5/15/2023 |
