Measure to calculate IRR

[juliliscarmo]

Hi all,

I'm trying to find a way to calculate the IRR, not the XIRR, just IRR. 

Thank you

Juli

[Anonymous]

Hello Team, IS there any updates for this XIRR function grouping by category. Eg: I have to calculate XIRR value for different projects which was in same table. 

Kindly help on this case Thank you M Logendran

[LivioLanzo]

There is no IRR built in function, but you can calculate it like it with a workaround

IRR % =
XIRR (
    ADDCOLUMNS (
        Data,
        "Date365", CALCULATE (
            COUNTROWS ( Data ),
            Data[Date] < EARLIER ( Data[Date] ),
            ALL ( Data )
        )
            * 365
            + CALCULATE ( MIN ( Data[Date] ), ALL ( Data ) )
    ),
    [Amount],
    [Date365]
)

The above measure returns 8.41%

[juliliscarmo]

Hi @LivioLanzo

Unfortunately, this formula is not working well when I have blank cells. Do you have any idea why?
I really appreciate your help, please see below my table format.

Client	Category	Date	Value	Values Last 1 year
1	A	1/1/2017	300.00	-300.00
1	B	1/1/2017	200.00
1	B	1/2/2017	150.00	150.00
1	C	4/2/2017	250.00	250.00
1	C	4/2/2017	400.00	400.00
1	A	8/2/2017	230.00	230.00
1	B	8/7/2017	300.00
2	B	8/7/2016	300.00
2	A	9/7/2016	500.00	-500.00
2	B	9/17/2016	300.00	300.00
2	C	9/7/2017	150.00	150.00
2	A	9/7/2017	200.00	200.00

Thank you

Juli

[LivioLanzo]

Hi @juliliscarmo

On which column are you calculating it ?  You may need to filter out blank cells

[juliliscarmo]

Hi @LivioLanzo

I am calculating in Values Last 1 Year, I tryed to filter out blank cells but it didnt work.  In Values Last 1 Year, first A need to be negative and last positive, I made a calculated column for that.

[LivioLanzo]

@juliliscarmo

Are you doing this via a calculated column or measure?

how do you calculate value last year? it does not match the actual value of last year

[juliliscarmo]

Hey @LivioLanzo

I did via calculated column, the last year is based in the database, as if I have last data in 12/2015 the last year will be 12/2014, for "X" client.

Thank you

Juli