Calculating Annualised Rate of Return

[wooand]

I'm struggling to recreate a function I use easily in Excel in Power BI.  In Excel I can select a range of NAVs between two dates and calculate the annualised rate of return for the selected NAV information.  Here's the calculation I used to use for Annualised RoR in Excel to calculate an annualised rate of return of an index:

=(IJ146/IJ25)^(12/COUNT(IJ25:IJ146))-1

IJ25 = Starting NAV

IJ146 is end NAV

^ is Exponent Function

How do I make this work in PowerBI please?? 

I have the following columns in PowerBI.  The date column runs from Dec 29 2017 to the present for each Currency in 'CCY', and the NAV runs for every currency from 100 to whatever it ends at.  At present the NAV change column and the Prior NAV columns contain blanks.  I would like to be able to use a currency filter to return RoR for a currency for a given date range 

[Anonymous]

Hi @wooand,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help anyone in the community find the solution easily when they face the same problem with you. Thank you.

Best Regards

Rena

[wooand]

No, I'm afraid it doesn't give the correct results, so I am working on it and will post a solution once we have it.

[Anonymous]

Hi @wooand ,

If need any assistance from my side about this thread? And whether you can get correct results when use the formula provided by me? Any comment please let me know. Thank you.

Best Regards

Rena

[Anonymous · Accepted solution]

Hi @wooand ,

I just compared the returned value by my measure "ROR" with the value which use the formula "(C12/C2)^(365/COUNT(C2:C12))-1" in excel returned, and found that their values are the same. I don’t know why the returned value is incorrect when applied to your scenario... Could you please provide your pbix file in order to make troubleshooting...Thank you.

Measure value

Excel value

Best Regards

Rena

[amitchandak]

We see power Bi screen shot and excel formula. Can you provide more details

[wooand]

Sure, happy to, what can I tell you that would help?

[Anonymous]

Hi @wooand ,

Could you please provide the related screenshot with column IJ146 and IJ25 in excel file and some sample data(exclude sensitive data)? And which field in Power BI file the column IJ146 and IJ25 in excel correspond to? They correspond to field "NAV" and "NAV Change" separately?

Best Regards

Rena

[wooand]

Sure.  Bear with me.

[wooand]

OK, here you go.  The only columns that matter here are the date, CCY and the NAV.  In excel I take the end index value from the NAV column and put that over the first index value, then use the exponent function as per the formula.  I've changed the numerator in the second element of the excel equation to reflect the fact that this data is daily, not monthly, hence the change from 12 to 365.  Ignore NAV Change and Prior NAV - I can remove that data to make life easier.  Finally, note that the CCY column contains many different currencies, each with an index starting from 29 Dec 2017 at a value of 100, so I'm looking for a RoR caclulation that works when a slicer is used to select a CCY.  I hope that helps! 

[Anonymous]

Hi @wooand ,

You can create a measure as below:

ROR =
POWER (
    DIVIDE (
        CALCULATE (
            MAX ( 'ROR'[NAV] ),
            FILTER ( ALL ( 'ROR' ), 'ROR'[Date] = MIN ( 'ROR'[Date] ) )
        ),
        CALCULATE (
            MAX ( 'ROR'[NAV] ),
            FILTER ( ALL ( 'ROR' ), 'ROR'[Date] = MAX ( 'ROR'[Date] ) )
        )
    ),
    DIVIDE ( 365, COUNTROWS ( 'ROR' ) )
) - 1

Best Regards

Rena

[wooand]

Thank you, but that is returning a negative return irrespective of whether the index has risen or fallen.  Can you correct for that?  Certainly looks like a step in the right direction.

[Anonymous]

Hi @wooand ,

Sorry, I made a mistake on getting the value of C593/C2... The previous one is get the value of C2/C593, please correct the formula of measure as below:

ROR =

POWER (

DIVIDE (

CALCULATE (

MAX ( 'ROR'[NAV] ),

FILTER ( ALL ( 'ROR' ), 'ROR'[Date] = MAX ( 'ROR'[Date] ) )

),

CALCULATE (

MAX ( 'ROR'[NAV] ),

FILTER ( ALL ( 'ROR' ), 'ROR'[Date] = MIN ( 'ROR'[Date] ) )

)

),

DIVIDE ( 365, COUNTROWS ( 'ROR' ) )

) - 1

Best Regards

Rena

[Greg_Deckler]

Hard to say, what is in IJ46, or for that matter IJ25?

[wooand]

They are the start and end NAVs for the selected period for the return to be calculated over...