How to Calculate Portfolio Tracking Error in Excel!

This short video shows you how to both calculate and interpret portfolio tracking error. We do all of our work here in Excel. It's pretty easy!

[@HumptyDump37]

Brilliant overview, thanks.

[@hsaxsf]

Thank you, Ric. Great explanation and relevant.

[@ricthomas6436]

Thanks Hermann.

[@alexsims1433]

I'm a little confused on how your manual standard deviation and automatic standard deviation turned out the same. In your manual one, you never subtract the mean from the active return before you take the sum of squares. Am I missing something?

[@ricthomas6436]

You are correct. In fact, because I don't subtract the mean of the series, the difference between the automatic and manual calculation is slightly different. But this doesn't show up in the video because I round to 2 decimals. If I made it more decimals you would see a slight difference. When you take the average of the excess return numbers, it is a number that is VERY close to 0. So, subtract out this mean makes very little difference. Sorry to confuse this issue. But do know that when professionals calculate the tracking error, you can use either method, and they give you almost identical answers. I hope this helps.

[@kaibaing4288]

I have a doubt pertaining to the formula for tracking error Do we take the standard deviation of (the differences obtained between benchmark return & portfolio return) Or Do it as shown in the video I.e (calculate deviation from benchmark return,and use the sum of squares for the same,for TE)?

[@ricthomas6436]

Hi @kailbaing4288, excellent question. You can do it either way, as they will give you almost identical answers. In the manual method that I used - I did not subtract the mean excess return. In reality, the mean excess return is going to be a number very close to 0, so the differences between the two methods will be close. Technically, the manual method (where you do not subtract out the mean) is what many authors on the subject recommend when they show the formula. However, in practice you will find that most investment professionals will use an excel stdev function (also shown in the video) which does subtract the mean. In fact, the only reason my two calculations show the same number here is because of rounding. In reality they are (very slightly) different.

[@KaziUmmeSumaiya]

If I want to get the annualized tracking error from weekly excess return, should I multiply by sqrt(52)?

[@ricthomas6436]

Yes. And if you use daily returns, multiply by sqrt of (252) since that is approximately the number of trading days in a year.

[@eyalakhdhar520]

Hi, please i have a question : You said we know that 66% probability of any given year that the excess return will be within 6%. How did you determine le percentage of 66% ?

[@ricthomas6436]

Hi, the actual number is 68% so, my mistake. The reason: In a normal distribution, 68% of the observations will fall within 1 standard deviation of the mean (plus or minus). Since Tracking Error is a 1 standard deviation "event", we can state that the historical probability of an observation inside of the calculated tracking error is 68%. I hope this helps.

[@ricthomas6436]

Actually, I misspoke in the video. It should state 68% of the time. That is because in a normal distribution 68% of the observations are between +1 and -1 standard deviations of the mean.

[@MaximilianPlasota]

Ric, Respectfully I think you should give a heads-up that the method you show in this video is not technically correct. I see you've responded to some comments saying that the difference (in most cases) will be small, but that's a big assumption to make knowing some people may use your videos to make important decisions or draw important conclusions.

[@ricthomas6436]

@@MaximilianPlasota thank you for comments. In reality, it isn’t so clear cut. Some industry experts subtract the mean, while others do not. This is true for online courses as well. For example, the attached link shows an example where the mean is not subtracted. But I take your point that I can clarify this in my video description, and I will do so. Thank you. Here’s the link…. study.com/buy/academy/lesson/what-is-tracking-error-definition-formula-example.html?src=ppc_adwords_nonbrand&rcntxt=aws&crt=668899128939&kwd=&kwid=dsa-799763748178&agid=150848004863&mt=&device=c&network=g&campaign=%7Bcampaign%7D&gad_source=1&gbraid=0AAAAADjpCfSBHo0cU4lXVfQXQf4NjdQr7