Issue with calculating Tracking Error for Index fund

[polycarpvenant]

Hi all,

I am using the NSE definition here: https://www.nseindia.com/products-services/indices-tracking-error (stdev of fund less TRI daily return annualized via sqrt250)

I have taken Nifty 50 TRI data from here: https://www1.nseindia.com/products/content/equities/indices/historical_total_return.htm

and UTI - NIFTY Index Fund-Growth Option- Direct NAV data from AMFI.

I have replicated the calc in this Google Sheet but unable to reconcile the result (Annualised 1yr Tracking error value of 0.0382) from UTI AMC website for YE 31-Mar-2021:

https://imgur.com/kShYcSt

It is driving me nuts. Please help.

Link to spreadsheet: https://docs.google.com/spreadsheet...9oEv8Pt9Xf8ag1vaEae9sNuo3kUOl/pub?output=xlsx

Disclosure: This calculation will eventually go into my blog so in case that is relevant (the post will deal with choosing index funds)

Any help is highly appreciated and thanks in advance.

Edit: Solved by @gunderson500 below. This was because 14Nov2020 has trading data for Muhurat trading but no MF NAVs.

 

[gunderson500]

@polycarpvenant Your calculations appear to be okay. The difference between your calculations and what UTI AMC has done appears to be on account of you including the data for 14 Nov 2020 which was a non-business day. Also, UTI has (carelessly?) avoided suffixing "%" to their calculation.

 

[polycarpvenant]

@gunderson500 Wow. That was it. Due to Muhurat trading there is Bhavcopy and Index value but no MF navs.

I am now getting 0.0376% vs. 0.0382% for UTI's own numbers. I can live with that. Thank you so much.

 

[flashback]

@polycarpvenant Please don't forget to post your blog post on this sub.

 

[ilykmtns]

@polycarpvenant Excess Return = RF + β(MR – RF) – TR

Where:

RF = Risk-free rate

β = Beta of the security

MR = Expected return of the market

TR = Actual or Total Return from the security

Sayan, you don't simply subtract to find "excess" return.

 

[polycarpvenant]

@ilykmtns Hi, Thanks for the response. However I don't think that is correct simply because the tracking error is a realised figure and not a theoretical one to use the CAPM definition. Using CAPM would also require someone like SEBI to come and specify RF and RM in order to make the numbers comparable across AMCs.

Also, the NSE definition of TE (linked above) and the same from Vanguard, the excess return = fund - index return

https://www.vanguard.com.hk/documents/understanding-td-and-te-en.pdf

 

[zashmaster]

@polycarpvenant A quick thought. The dates are not continuous. Are you accounting for this? Of course, you are calculating return for the fund and TRI using the same formula. Still the number of days could cause an issue.

 

[polycarpvenant]

@zashmaster Srini sir, thank you for the insight.

I just checked the dates and they are the same for fund and index since I have taken the index data from NSE and then used vlookup to get the same day's NAV for the fund. The dates are sorted in descending order.

Also there are exactly 250 data points between 31-Mar-2020 and 31-Mar-2020 and correlation of returns is around .999 which would indicate that the series is not off by one or something like that. I will keep looking.