cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sjoerdbuis Frequent Visitor
Frequent Visitor

Historic IRR Dashboard - Unrealized cashflows

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.

 

 

Historic IRR.PNG

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Historic IRR Dashboard - Unrealized cashflows

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

Capture.PNG

 

 

  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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




5 REPLIES 5
Highlighted
Super User
Super User

Re: Historic IRR Dashboard - Unrealized cashflows

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

Capture.PNG

 

 

  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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




sjoerdbuis Frequent Visitor
Frequent Visitor

Re: Historic IRR Dashboard - Unrealized cashflows

@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 Frequent Visitor
Frequent Visitor

Re: Historic IRR Dashboard - Unrealized cashflows

@OwenAuger 

managed both items by myself.  great stuff.

and all checks worked out perfectly

 

Super User
Super User

Re: Historic IRR Dashboard - Unrealized cashflows

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




sjoerdbuis Frequent Visitor
Frequent Visitor

Re: Historic IRR Dashboard - Unrealized cashflows

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