XIRR in differents periods

[juliliscarmo]

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.

Date	Type	Value	1 Example	2 Example
1/1/2016	A	35000
1/1/2016	A	300
6/1/2016	A	30000		-30000
7/1/2016	A	36000
10/1/2016	B	100		100
11/1/2016	C	230		230
11/1/2016	C	22		22
1/1/2017	A	35000	-35000
5/1/2017	C	1000	1000	1000
6/1/2017	A	500	500	500
10/1/2017	B	300	300
10/1/2017	A	34000
1/1/2018	B	4000	4000
1/1/2018	A	7000	7000
5/1/2018	B	7000
10/1/2018	C	4500

[Anonymous]

Unable to access the data model can you please share the link to data model .

[OwenAuger]

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

[juliliscarmo]

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 · Accepted solution]

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

[HennemanTJ]

Hey @OwenAuger - how would you tweak this initially for filtering by a part number first.  I have a very similar problem where I'm needing to get IRR for individual part numbers and then provide an IRR by period and your advice is the closest that I've found to what I think I need.

Basically the IRR calc fails when I look at everything because there are multiples of the same date with different outflows and inflows. 

So I think I need to filter it down to look by part # first then run IRR on those.

Thanks in advance!

[OwenAuger]

Hi @HennemanTJ 

Generally speaking, if you need to perform any IRR calculation with a particular filter applied (such as an individual part number), you should be able to apply the relevant filter by slicer or otherwise and get a meaningful result.

In your case, if you include a "single select" slicer on part number, does your IRR measure give a meaningful result?

You can also include a safeguard in the measure itself, to blank out the result if more than one part number is filtered, looking something like:

IRR measure with safeguard =
IF ( 
    HASONEVALUE ( YourTable[Part Number] ),
    [IRR original measure]
)

Please post back with more details if you need some more help.

Regards,

Owen 

[juliliscarmo]

Hi @OwenAuger

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

Thank you!

Juli

[OwenAuger]

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

[Anonymous]

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	P	27/04/2015	Purchase	-       3,000,000.00
ABC Company	P	27/05/2015	Direct Purchase	-          200,000.00
ABC Company	P	27/05/2015	Purchase	-          100,000.00
ABC Company	P	27/05/2015	Direct Purchase	-          200,000.00
ABC Company	P	30/06/2015	Direct Purchase	-          500,000.00
ABC Company	V	30/06/2015	Valuation	4,100,000.00
ABC Company	P	30/08/2015	Direct Purchase	-       1,060,000.00
ABC Company	P	01/09/2015	Buy of ABC common	-          200,000.00
ABC Company	V	30/09/2015	Valuation	5,360,000.00
ABC Company	P	20/10/2015	Direct Purchase	-       1,000,000.00
ABC Company	P	22/10/2015	Direct Purchase	-          100,000.00
ABC Company	P	10/11/2015	Purchase	-            10,000.00
ABC Company	P	15/12/2015	Direct Purchase	-          400,000.00
ABC Company	P	16/12/2015	Purchase	-       1,000,000.00
ABC Company	V	31/12/2015	Valuation	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]

Hi @Anonymous 

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

[Anonymous]

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?

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

Firm	Type	Document Date	Description	Cashflows
ABC Company	P	27/04/2015	Purchase	-    3,000,000.00
ABC Company	P	27/05/2015	Direct Purchase	-       200,000.00
ABC Company	P	27/05/2015	Purchase Amadeus Test	-       100,000.00
ABC Company	P	27/05/2015	Direct 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

Firm	Type	Document Date	Description	Cashflows
ABC Company	P	27/04/2015	Purchase	-      3,000,000.00
ABC Company	P	27/05/2015	Direct Purchase	-          200,000.00
ABC Company	P	27/05/2015	Purchase Amadeus Test	-          100,000.00
ABC Company	P	27/05/2015	Direct Purchase	-          200,000.00
ABC Company	P	30/06/2015	Direct Purchase	-          500,000.00
ABC Company	V	30/06/2015	Valuation	4,100,000.00
ABC Company	P	30/08/2015	Direct 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

[OwenAuger]

Hi again Tony @Anonymous ,

Thanks - great explanation!

I have uploaded a dummy Power BI model testing my measure below.

After looking through your examples and replicating the calculations myself, I think the logic boils down to:

1. Always include all P rows as-is
2. Construct one additional "Valuation row" with value equal to the value from the last V row plus values from any subsequent P rows (negated), assigned to the max filtered date. In the absence of any V row, all P rows are included.
3. Take the union of the tables from 1 & 2, and calculate XIRR over that table.

Based on this, I re-wrote the measure from scratch in a way that I think makes more sense.

IRR = 
VAR Last_V_Date =
    CALCULATE ( MAX ( Transactions[Document Date] ), Transactions[Type] = "V" )
VAR MaxDateSelected =
    MAX ( 'Date'[Date] )
VAR PurchaseRows =
    CALCULATETABLE (
        SELECTCOLUMNS (
            Transactions,
            "Date", Transactions[Document Date],
            "Cashflow", Transactions[Cashflows]
        ),
        Transactions[Type] = "P"
    )
VAR Valuation_Purchases_Component =
    CALCULATE (
        SUM ( Transactions[Cashflows] ),
        Transactions[Type] = "P",
        KEEPFILTERS ( 'Date'[Date] > Last_V_Date || ISBLANK ( Last_V_Date ) )
    ) * -1
VAR Valuation_Valuation_Component =
    CALCULATE (
        SUM ( Transactions[Cashflows] ),
        'Date'[Date] = Last_V_Date,
        Transactions[Type] = "V"
    )
VAR Valuation = Valuation_Purchases_Component + Valuation_Valuation_Component
VAR ValuationRow =
    { ( MaxDateSelected, Valuation ) }
VAR TransactionTable =
    UNION ( PurchaseRows, ValuationRow )
RETURN
    XIRR ( TransactionTable, [Cashflow], [Date] )

To summarise the important variables:

• PurchaseRows is includes Date/Cashflows from all P rows
• Valuation_Purchases_Component is the total value of P rows after the last V Date.
• Valuation_Valuation_Component is just the value of the V row on the last V Date
• Valuation is the sum of these
• ValutionRow is the last filtered date and Valuation put together in a single-row table
• TransactionTable is final table combining PurchaseRows and ValuationRow, and XIRR is calculated over this table

Other notes:

• I ditched the SUMMARIZE as it wasn't really needed and XIRR doesn't mind if we have repeated dates.
• We could also make the measure return 0 by default when there are no V rows

Hopefully that works correctly. Please test! 🙂

Regards,

Owen

[Anonymous]

Hi @OwenAuger 

I hope you are well?  I am migrating the IRR code you orginally devised to a new tabular cube with fact & dimension tables.  I have hit a snag in that the table with the cashflows (vwv_FactGL) does not contain a date any longer but a link to a Date table instead (FYI this is a Live connection so I can only create Measures).  This means my code now fails at the SELECTCOLUMNS statement (shown in red):

IRR =
VAR MaxDateSelected = MAX ( 'Date'[Date] )
VAR PurchaseRows =
SELECTCOLUMNS (
vwv_FactGL,
"Date", Date[Date],
"Cashflow", vwv_FactGL[Cashflows]
)
VAR Valuation_Valuation_Component =
CALCULATE (
SUM ( 'vwv_FactGL'[Investment Balance] ),
'Date'[Date] <= MaxDateSelected
)
VAR Valuation = Valuation_Valuation_Component
VAR ValuationRow =
{ ( MaxDateSelected, Valuation ) }
VAR TransactionTable =
UNION ( PurchaseRows, ValuationRow )
RETURN
IF ( ISERROR (
XIRR ( TransactionTable, [Cashflow], [Date] )
)
,BLANK()
,XIRR ( TransactionTable, [Cashflow], [Date] )
)

My question is how to combine data from the two tables as if it was one all in one in the DAX?

The two tables are Date[Date] and vv_FactGL[Cashflows] & [Investment Balance] (see below) and I am trying to create the IRR measure in the vwv_FactGL table:

Kind regards

Tony

[OwenAuger]

Hi @Anonymous 

I'm fine thanks, hope you're well 🙂

The simplest fix I can think of is to change

SELECTCOLUMNS (
    vwv_FactGL,
    "Date", Date[Date],
    "Cashflow", vwv_FactGL[Cashflows]
)

to 

SELECTCOLUMNS (
    SUMMARIZE ( vwv_FactGL, 'Date'[Date] ),
    "Date", 'Date'[Date],
    "Cashflow", CALCULATE ( SUM ( vwv_FactGL[Cashflows] ) )
)

This will effectively group by Dates that exist in vwv_FactGL and sum Cashflows on those dates. For the IRR calculation, it makes no difference whether dates are distinct or not.

Does this work in your current model?

Kind regards,

Owen

[Anonymous]

This result is the following error:

I should have said the field Cashflows is itself a measure:

Cashflows = SUM('vwv_FactGL'[Investment IRR Cashflows]) * -1

It is needed to invert the sign of the actual cashflow field Investment IRR Cashflows.  So I changed your code to reference the actual field (ignoring for now the sign being wrong) and got this instead:

 

IRR = 
VAR MaxDateSelected = MAX ( 'Date'[Date] )
VAR PurchaseRows =
SELECTCOLUMNS ( 
        SUMMARIZE ( 
        'vwv_FactGL',
        'Date'[Date] 
		           ), 
        "Date", 'Date'[Date], 
		"Cashflow", 
        CALCULATE ( SUM ( 'vwv_FactGL'[Investment IRR Cashflows] )
                  )  
               )
VAR Valuation_Valuation_Component =
    CALCULATE (
        SUM ( 'vwv_FactGL'[Investment Balance] ),
        'Date'[Date] <= MaxDateSelected
    )
VAR Valuation = Valuation_Valuation_Component
VAR ValuationRow =
     { ( MaxDateSelected, Valuation ) }
VAR TransactionTable =
    UNION ( PurchaseRows, ValuationRow )
RETURN
        IF ( ISERROR ( 
            XIRR ( TransactionTable, [Cashflow], [Date] )
            )
            ,BLANK()
            ,XIRR ( TransactionTable, [Cashflow], [Date] )
            ) 

[Anonymous]

First time 👍 !  I think have some small issues to reconcile in the data but the calculation looks perfect.

[OwenAuger]

Hi again Tony

I'm not certain, but there may be an issue with the {} table constructor in your Analysis Services Tabular version.

Also, we can use the Cashflow measure within the definition of PurchaseRows.

You could use the ROW function instead in this case. Also since Valuation is set equal to Valuation_Valuation_Component, we can eliminate one of those variables.

Does the below code work?

IRR =
VAR MaxDateSelected =
    MAX ( 'Date'[Date] )
VAR PurchaseRows =
    SELECTCOLUMNS (
        SUMMARIZE ( 'vwv_FactGL', 'Date'[Date] ),
        "Date", 'Date'[Date],
        "Cashflow", [Cashflow]
    )
VAR Valuation =
    CALCULATE (
        SUM ( 'vwv_FactGL'[Investment Balance] ),
        'Date'[Date] <= MaxDateSelected
    )
VAR ValuationRow =
    ROW ( "Date", MaxDateSelected, "Cashflow", Valuation )
VAR TransactionTable =
    UNION ( PurchaseRows, ValuationRow )
RETURN
    IF (
        ISERROR ( XIRR ( TransactionTable, [Cashflow], [Date] ) ),
        BLANK (),
        XIRR ( TransactionTable, [Cashflow], [Date] )
    )

 Regards,

Owen

[Anonymous]

Hi Owen

Once again thank you for looking at this for me.  I will go away and test on a broader data set and let you know. But on initial limited testing this looks perfect!

Kind regards

Tony

[juliliscarmo]

Hi @OwenAuger

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

Cheers,

Juli