cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
juliliscarmo Helper I
Helper I

XIRR in differents periods

Hello everyone,

 

I would like to create a formula that can calculate the IRR at different periods, I would like to put one filter on the dashboard and when I change the date I also change the value of the IRR. The big problem is that I have tree categories A, B and C, and when I'm working with the last 5 years for example, I need to have the first negative A value and just the last A value as positives.

I would like to replace it as a measure, as I would like to have a filter on the panel to change the date.

 

DateTypeValue1 Example2 Example
     
1/1/2016A35000  
1/1/2016A300  
6/1/2016A30000 -30000
7/1/2016A36000  
10/1/2016B100 100
11/1/2016C230 230
11/1/2016C22 22
1/1/2017A35000-35000 
5/1/2017C100010001000
6/1/2017A500500500
10/1/2017B300300 
10/1/2017A34000  
1/1/2018B40004000 
1/1/2018A7000 7000 
5/1/2018B7000  
10/1/2018C4500  
1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Community Champion
Community Champion

Re: XIRR in differents periods

Hi again @juliliscarmo

I have created a sample pbix with an IRR measure hopefully close to meeting your needs.

PBIX link

 

My measure likes this (with a few comments):

IRR = 
VAR First_A_Date =
    CALCULATE( MIN ( Cashflow[Date] ), Cashflow[Type] = "A" )
VAR Last_A_Date =
    CALCULATE( MAX ( Cashflow[Date] ), Cashflow[Type] = "A" )
VAR CashflowTable = 
    ADDCOLUMNS (
        SUMMARIZE ( 
            Cashflow,
            Cashflow[Date],
            Cashflow[Type]
        ),
        "Cashflow",
        -- Aggregate values at a Date & Type level - correct?
        -- For example, 2 values on 1/1/2016 will be aggregated to 35300.
        -- If this is not correct, we may need to add an index to distinguish
        -- values of same type on same date
        VAR OriginalValue = CALCULATE ( SUM ( Cashflow[Value] ) )
        VAR CashflowMultiplier =
            SWITCH (
                TRUE (),
                Cashflow[Type] = "A" && Cashflow[Date] = First_A_Date, -1, --First Type A is negated
                Cashflow[Type] = "A" && Cashflow[Date] <> Last_A_Date, 0,  --Other Type A is ignored if not last
                1 -- All the rest are positive (this includes the final Type A)
            )
        RETURN
            OriginalValue * CashflowMultiplier
    )
RETURN
    XIRR ( CashflowTable, [Cashflow], Cashflow[Date] )

Within the measure, I have aggregated Values at the Type/Date level. However, I notice in your sample data that there are cases of multiple values of the same Type on the same Date. If we need to keep those separate, then we may need some sort of index column.

 

Also, after re-reading your original post, I realised that the last Type A value needs to be positive.

So the logic I have used is:

  1. First Type A value is negated
  2. Other Type A values except last are left out
  3. Type B & C, and last Type A are left positive

If the logic needs to be modified, hopefully it is not too hard to change the above.

 

Best regards,

Owen



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

Proud to be a Datanaut!




View solution in original post

18 REPLIES 18
OwenAuger Community Champion
Community Champion

Re: XIRR in differents periods

Hi @juliliscarmo

You can certainly do this. It's a matter of filtering your cashflow table appropriately, negating the "A" cashflow, then passing this to the XIRR function.

 

Couple of questions before writing a measure:

  1. With the types, confirming you want to take just the first cashflow of type "A" as a negative, then take all subsequent cashflows of types "B" & "C"?
  2. How do you want the date filtering to work? Do you want to select a single date, then take the cashflows from that date onwards? Or do you want to select a range of dates.
  3. Do you limit the cashflow date range to a particular length? Your examples seemed to be roughly a year.

Regards,

Owen



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

Proud to be a Datanaut!




juliliscarmo Helper I
Helper I

Re: XIRR in differents periods

Hi @OwenAuger,

 

Thank you for your attention.

 

1. Yes, absolutely!

2. Yes I would like to select a range of dates.

3. No.

 

Best Regards

Juli

OwenAuger Community Champion
Community Champion

Re: XIRR in differents periods

Hi again @juliliscarmo

I have created a sample pbix with an IRR measure hopefully close to meeting your needs.

PBIX link

 

My measure likes this (with a few comments):

IRR = 
VAR First_A_Date =
    CALCULATE( MIN ( Cashflow[Date] ), Cashflow[Type] = "A" )
VAR Last_A_Date =
    CALCULATE( MAX ( Cashflow[Date] ), Cashflow[Type] = "A" )
VAR CashflowTable = 
    ADDCOLUMNS (
        SUMMARIZE ( 
            Cashflow,
            Cashflow[Date],
            Cashflow[Type]
        ),
        "Cashflow",
        -- Aggregate values at a Date & Type level - correct?
        -- For example, 2 values on 1/1/2016 will be aggregated to 35300.
        -- If this is not correct, we may need to add an index to distinguish
        -- values of same type on same date
        VAR OriginalValue = CALCULATE ( SUM ( Cashflow[Value] ) )
        VAR CashflowMultiplier =
            SWITCH (
                TRUE (),
                Cashflow[Type] = "A" && Cashflow[Date] = First_A_Date, -1, --First Type A is negated
                Cashflow[Type] = "A" && Cashflow[Date] <> Last_A_Date, 0,  --Other Type A is ignored if not last
                1 -- All the rest are positive (this includes the final Type A)
            )
        RETURN
            OriginalValue * CashflowMultiplier
    )
RETURN
    XIRR ( CashflowTable, [Cashflow], Cashflow[Date] )

Within the measure, I have aggregated Values at the Type/Date level. However, I notice in your sample data that there are cases of multiple values of the same Type on the same Date. If we need to keep those separate, then we may need some sort of index column.

 

Also, after re-reading your original post, I realised that the last Type A value needs to be positive.

So the logic I have used is:

  1. First Type A value is negated
  2. Other Type A values except last are left out
  3. Type B & C, and last Type A are left positive

If the logic needs to be modified, hopefully it is not too hard to change the above.

 

Best regards,

Owen



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

Proud to be a Datanaut!




View solution in original post

juliliscarmo Helper I
Helper I

Re: XIRR in differents periods

Hi @OwenAuger

 

 

I just want to say thank you! You are the best!

 

Cheers,

Juli

juliliscarmo Helper I
Helper I

Re: XIRR in differents periods

Hi @OwenAuger

 

I would like to know if is possible to calculate the IRR, not XIRR, with the same situation.

 

Thank you!

Juli

OwenAuger Community Champion
Community Champion

Re: XIRR in differents periods

Hi again @juliliscarmo

Yes you certainly can replicate the behaviour of Excel's IRR function.

Basically you need to construct a cashfow table with dates that are at 365 day intervals.

(You can test this in Excel - if you provide XIRR with dates that are at 365 day intervals, you get the same result as the IRR function)

 

So instead of the variable CashflowTable which I created in the original measure which brings through the original Cashflow[Date] values, you would need a similar table which has dates at 365 day intervals.

 

It's probably going to be similar to the original formula but with that CashflowTable defined differently. Could you post an example of which values would feed into the IRR calc and expected output?

 

Cheers

Owen



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

Proud to be a Datanaut!




tonyp
Frequent Visitor

Re: XIRR in differents periods

Hi @OwenAuger 

 

Thanks this is really very useful indeed and I have adapted your code to my own table and needs (see table & code snippet below).  I have one further thing I am trying to achieve which is if the user selects a date for which there is no appropriate cashflow the XIRR function errors.

 

By that I mean you are only guaranteed a result if the date they pick has a valuation on it (type = V and is a positive number).  I would like the user to be able to pick any date and in the case of my example table below if they picked 31/08/2015, for example, I could use the last unrealised valuation (i.e. 30/06/2015 of 4,100,000.00) and add the purchase of 30/08/2015 (i.e. -1,060,000.00 N.B. this needs to be made positive before adding to the valuation) to calculate a notional terminal value of 5,160,000.00 to feed the XIRR function as my last cashflow. Then all I need to do is ignore any preceding valuations in the flows I feed to the XIRR function and just include Type =P. 

 

If there were no valuations at all in the date period then I would use all preceding purchases (Type = P) to calculate a terminal value (as you can see in my data I have explicit valuation transactions at the end of each quarter but if this works in DAX I no longer have to rely on there being one as I can calculate my own terminal value on each date the user selects by either looking at the last valuation and adding any purchases and taking out any sales or, assuming no valuations, adding up all of the purchases & sales).


I have done this in SQL many times, but I am new to DAX.

 

My table is something like this:

 

Cashflows(Firm, Type, Document Date, Description, Cashflows)

 

ABC Company

P27/04/2015Purchase-       3,000,000.00
ABC CompanyP27/05/2015Direct Purchase-          200,000.00
ABC CompanyP27/05/2015Purchase -          100,000.00
ABC CompanyP27/05/2015Direct Purchase-          200,000.00
ABC CompanyP30/06/2015Direct Purchase-          500,000.00
ABC CompanyV30/06/2015Valuation        4,100,000.00
ABC CompanyP30/08/2015Direct Purchase-       1,060,000.00
ABC CompanyP01/09/2015Buy of ABC common-          200,000.00
ABC CompanyV30/09/2015Valuation        5,360,000.00
ABC CompanyP20/10/2015Direct Purchase-       1,000,000.00
ABC CompanyP22/10/2015Direct Purchase-          100,000.00
ABC CompanyP10/11/2015Purchase-            10,000.00
ABC CompanyP15/12/2015Direct Purchase-          400,000.00
ABC CompanyP16/12/2015Purchase-       1,000,000.00
ABC CompanyV31/12/2015Valuation      10,000,000.00

 

Your modified code which works perfectly as long as you pick a quarter end date in the date filter:

 

IRR =
VAR Last_V_Date = CALCULATE( MAX ( Transactions[Document Date] ), Transactions[Type] = "V" )

VAR TransactionTable =
        ADDCOLUMNS (
                 SUMMARIZE (
                          Transactions,
                          Transactions[Document Date],
                          Transactions[Type]),
                          "Transactions",
                 VAR OriginalValue = CALCULATE ( SUM ( Transactions[Cashflows] ) )
                 VAR TransactionMultiplier =
                        SWITCH (
                                TRUE (),
                                Transactions[Type] = "V" && Transactions[Document Date] <> Last_V_Date, 0,
                                 1
                         )
                 RETURN
                         OriginalValue * TransactionMultiplier
       )
RETURN
      XIRR ( TransactionTable, [Transactions], Transactions[Document Date] )
 
Do you think this is possible to do in DAX?
 
Kind regards
 
 
Tony
OwenAuger Community Champion
Community Champion

Re: XIRR in differents periods

Hi @tonyp 

 

I am sure what you have described is possible - it's just a matter of correctly constructing the TransactionTable variable using DAX.

 

Just summarising my reading of your post, with one question in bold:

 

  1. If at least one Valuation (Type = V) exists in the filtered date range, then:
    1. The last Valuation and any subsequent Purchases (Type = P) with sign reversed, are summed to produce the "last cashflow", $5.16m in your example. Which date is assigned to this last cashflow - the last V date?
    2. All rows with Type=P before the last Valuation row are also included as cashflows for XIRR
  2. If there are no Valuations in the filtered date range, then simply sum all purchase rows (Type = P)?

Let me know if I've got that right and then I can help with the DAX.

 

It might help if you could provide an example of the resulting dates/cashflows you want to feed into XIRR for both scenarios (with and without V) just to make sure. 

 

Regards,

Owen



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

Proud to be a Datanaut!




Highlighted
tonyp
Frequent Visitor

Re: XIRR in differents periods

Hi @OwenAuger 

 

 

Thank you for your prompt reply.  

 

My responses as follows:

 

1.1. The date assigned would be that used in the date filter the user selects.  In your example it was based on an auto created table Date ( using =CALENDARAUTO() ) which is linked in my example to Document Date in the Transactions table (see Scenario 2 below).  But perhaps I should be using a Parameter instead?

image.png

 

 

 

 

 

 

 

 

 

 

 

2. As in 1.1. if there are no valautions simply sum all transactions of Type P to create a valuation at cost and this would also take as its date the date selected by the user (see Scenario 1 below).

 

Scenario 1 - "Date" selected 31/05/2015

 

FirmTypeDocument DateDescription Cashflows 
ABC CompanyP27/04/2015Purchase-    3,000,000.00
ABC CompanyP27/05/2015Direct Purchase-       200,000.00
ABC CompanyP27/05/2015Purchase Amadeus Test-       100,000.00
ABC CompanyP27/05/2015Direct Purchase-       200,000.00
ABC Company 31/05/2015***Calculated Valuation Row***     3,500,000.00

 

XIRR = 0.00%

 

Sceanrio 2 - "Date" selected 31/08/2015

 

FirmTypeDocument DateDescription Cashflows 
ABC CompanyP27/04/2015Purchase-      3,000,000.00
ABC CompanyP27/05/2015Direct Purchase-          200,000.00
ABC CompanyP27/05/2015Purchase Amadeus Test-          100,000.00
ABC CompanyP27/05/2015Direct Purchase-          200,000.00
ABC CompanyP30/06/2015Direct Purchase-          500,000.00
ABC CompanyV30/06/2015Valuation        4,100,000.00
ABC CompanyP30/08/2015Direct Purchase-      1,060,000.00
ABC Company 31/08/2015***Calculated Valuation Row***        5,160,000.00

 

XIRR= 8.19%

 

I hope this make sense but if not let me know?

 

Kind regards

 

 

Tony

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors