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

Measure to calculate IRR

Hi all,

 

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

 

Thank you

Juli

6 REPLIES 6
Super User
Super User

Re: Measure to calculate IRR

 

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

 

Capture.PNG

 

 

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%

 


 


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


Proud to be a Datanaut!  

juliliscarmo Frequent Visitor
Frequent Visitor

Re: Measure to calculate IRR

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.

 

ClientCategoryDateValueValues Last 1 year
1A1/1/2017300.00-300.00
1B1/1/2017200.00 
1B1/2/2017150.00150.00
1C4/2/2017250.00250.00
1C4/2/2017400.00400.00
1A8/2/2017230.00230.00
1B8/7/2017300.00 
2B8/7/2016300.00 
2A9/7/2016500.00-500.00
2B9/17/2016300.00300.00
2C9/7/2017150.00150.00
2A9/7/2017200.00200.00

Thank you

Juli

Super User
Super User

Re: Measure to calculate IRR

Hi @juliliscarmo

 

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

 

 

 


 


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


Proud to be a Datanaut!  

juliliscarmo Frequent Visitor
Frequent Visitor

Re: Measure to calculate IRR

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.

Super User
Super User

Re: Measure to calculate IRR

@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

 

 

 


 


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


Proud to be a Datanaut!  

juliliscarmo Frequent Visitor
Frequent Visitor

Re: Measure to calculate IRR

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