cancel
Showing results for
Did you mean:
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.

 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
1 ACCEPTED SOLUTION

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

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

Proud to be a Datanaut!

18 REPLIES 18
Community Champion

## Re: XIRR in differents periods

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

Proud to be a Datanaut!

Helper I

## Re: XIRR in differents periods

Hi @OwenAuger,

1. Yes, absolutely!

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

3. No.

Best Regards

Juli

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.

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

Proud to be a Datanaut!

Helper I

## Re: XIRR in differents periods

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

Cheers,

Juli

Helper I

## Re: XIRR in differents periods

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

Thank you!

Juli

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

Proud to be a Datanaut!

Frequent Visitor

## Re: XIRR in differents periods

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

Proud to be a Datanaut!

Highlighted
Frequent Visitor

## Re: XIRR in differents periods

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

Announcements

#### 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!

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

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

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

Top Solution Authors
Top Kudoed Authors