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.
Hi, I am trying to setup a historic IRR dashboard (Private Equity) using XIRR.
I have a set of cashflows for a company and at the end of the quarter the company is being valued (unrealized cashflow) - see screenshot.
How can I create one set of cashflows where I can run a historical XIRR where the unrealized cashflow for the previous period is ruled out of the calculation but is calculated in on the current period.
The result should be that I have a set of casflows where I can run the XIRR on showing the quaurterly XIRRs as shown.
Solved! Go to Solution.
Hi @sjoerdbuis
Here's an example of how you can do it.
I created a basic model with your sample data in a table called Cashflow, related to a 'Date' table.
The measure I created is:
IRR as at latest Unrealised Cashflow = VAR MaxDate = MAX ( 'Date'[Date] ) VAR MaxUnrealisedDate = CALCULATE ( MAX ( Cashflow[Date] ), DATESBETWEEN ( 'Date'[Date], BLANK (), MaxDate ), Cashflow[Status] = "Unrealised" ) VAR CashflowForIRR = CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( Cashflow, 'Date'[Date] ), "CashflowForIRR", VAR StatusFilter = IF ( 'Date'[Date] = MaxUnrealisedDate, "Unrealised", "Realised" ) RETURN CALCULATE ( SUM ( Cashflow[Cashflow] ), Cashflow[Status] = StatusFilter ) ), DATESBETWEEN ( 'Date'[Date], BLANK (), MaxUnrealisedDate ) ) RETURN XIRR ( CashflowForIRR, [CashflowForIRR], [Date] )
Regards,
Owen
HI! this formula helped a lot! Totally genious.
I have one extra issue:
How/where would you incorparate USERELATIONSHIP( Calendario[Date] , 'BD TIR Financieras'[Date]) into the formula to activate de relationship between Calendar Table and Cashflow Date Table?
I alreday have a relationship which can´t delete and must use USERELATIONSHIP to conect them.
And also, I have several companies, and some of them (cashflows dates) starts much further in time than others. This produces an error in the XIRR formula when when i add in a time chart that includes all of them. Would you know how to fix this?
Thanks a lot!
Kind regards, Victoria.
Hi Victoria,
Apologies, I have been away from the forums due to some other commitments that came up.
If you're still having issues with this, I might need a bit more detail to help solve it.
Regarding USERELATIONSHIP, if you want to activate a relationship for the entire calculation, you should wrap the entire calculation like this:
CALCULATE ( <original expression>, USERELATIONSHIP(...) )
Regarding the different initial dates per company, it would require a more complex solution, but it depends on the exact outcome you want. XIRR itself takes the earliest date that it is given as "date zero", and measures all other dates relative to that date.
Could you create a simple example in Excel showing how you would want the calculation to work for multiple companies?
Regards,
Owen
Hi @sjoerdbuis
Here's an example of how you can do it.
I created a basic model with your sample data in a table called Cashflow, related to a 'Date' table.
The measure I created is:
IRR as at latest Unrealised Cashflow = VAR MaxDate = MAX ( 'Date'[Date] ) VAR MaxUnrealisedDate = CALCULATE ( MAX ( Cashflow[Date] ), DATESBETWEEN ( 'Date'[Date], BLANK (), MaxDate ), Cashflow[Status] = "Unrealised" ) VAR CashflowForIRR = CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( Cashflow, 'Date'[Date] ), "CashflowForIRR", VAR StatusFilter = IF ( 'Date'[Date] = MaxUnrealisedDate, "Unrealised", "Realised" ) RETURN CALCULATE ( SUM ( Cashflow[Cashflow] ), Cashflow[Status] = StatusFilter ) ), DATESBETWEEN ( 'Date'[Date], BLANK (), MaxUnrealisedDate ) ) RETURN XIRR ( CashflowForIRR, [CashflowForIRR], [Date] )
Regards,
Owen
thanks! this really helped me out a lot. can i send you a private message?
It looks like it calculates correctly. re-checking all figures but it seems to work great.
two questions on this topic:
1. when i take a look at your dashboard I see the columns shown in percentages XIRR. how do you do this? looks like I dont have an option to do so.
2. I have besides these data columns mentioned 2 other columns:
one sector of the cashflow and one product type.
I tried to have a chart where i split IRR per sector OR per product type so I tried to set those columns as a legend however it doesnt accept this. any thoughts on that?
My purpose is to have a line chart as well showing the quarterly IRR split per sector and per product type.
-We may need the measure to more carefully filter out dates with blank cashflow when other filters are applied."-
Yes I saw with certain filters the XIRR blanks but if i look in the data file I know why. when the XIRR under nomral circumstances gives an error; it does also on the dashboard and if that particular filter is applied it stops the graph.
Ill make sure it the data is XIRR proof for now.
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 |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |