Historic IRR Dashboard - Unrealized cashflows

[sjoerdbuis]

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.

[GDCPAI]

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.

[OwenAuger]

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

[OwenAuger · Accepted solution]

Hi @sjoerdbuis 

Here's an example of how you can do it.

PBIX link

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] )

1. This formula looks at the maximum date filtered and finds the latest Unrealised cashflow date up to and including that date, which is stored in the variable MaxUnrealisedDate.
2. It then creates a table variable called CashflowForIRR which contains Realised cashflows for earlier dates and Unrealised only on the MaxUnrealisedDate.
3. The last step is to apply the XIRR function to the columns of CashflowForIRR.
4. The formula will always use the last Unrealised cashflow as the cut-off for the calculation, and always looks back at the entire history. For example, if you filtered on April 2018, you would still see the same result as at March 2018.

Regards,

Owen

[sjoerdbuis]

@OwenAuger 

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.

[sjoerdbuis]

@OwenAuger 

managed both items by myself.  great stuff.

and all checks worked out perfectly

[OwenAuger]

That’s good news 🙂 feel free to post back if needed 🙂

[sjoerdbuis]

-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.