Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Another thing to add: I only want to include one Valuation, specifically the one from the period that I have selected. All other "Valuations" should be excluded from the calculation.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
27 | |
23 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |