How to Calculate Tracking Error in Excel (with Detailed Steps)

Get FREE Advanced Excel Exercises with Solutions!

Tracking error is one of the most crucial metrics for evaluating a portfolio’s performance and a portfolio manager’s potential for outperforming the market or a benchmark. This article will show how to calculate the tracking error in Excel.


What Is Tracking Error?

The difference between the return variations of an investment portfolio and the return fluctuations of a selected benchmark is measured by tracking error, a financial performance indicator. Standard deviations are used to calculate the return fluctuations.


How to Calculate Tracking Error in Excel: with Detailed Steps

In this article, we will determine parameters such as Active Return and Squared Active Return to calculate the tracking error in Excel. So, to know all the parameters related to this article for calculating the tracking error in Excel, you can follow the below steps accordingly.

Step 1: Make a Dataset with Proper Parameters

Here, we will create our sample dataset with multiple parameters related to tracking error calculation.

  • In order to calculate tracking errors, we will make a dataset.

Making Data Set with Proper Parameters to Calculate Tracking Error in Excel


Step 2: Determine Active Return

Now, we will determine the active return from the difference between portfolio return and S & P 400 return.

  • Firstly, select cell F5.
  • Secondly, write down the following formula.
=D5-E5

Determining Active Return to Calculate Tracking Error in Excel

  • Then, press Enter and use the Fill Handle to drag rightward to the remaining cells to see the result.

Read More: How to Convert Percentage to Basis Points in Excel


Step 3: Evaluate Squared Active Return

In this section, we will demonstrate how to determine the squared active return by squaring the active return.

  • Firstly, select Cell F5.
  • Then, write down the following formula.
=F5^2

Evaluating Squared Active Return to Calculate Tracking Error in Excel

  • After that, press Enter and use the Fill Handle to drag rightward to the remaining cells to see the result.

Read More: How to Calculate Profitability Index in Excel 


Step 4: Calculate Tracking Error in Excel

In this last section, we will calculate tracking errors in Excel by determining the necessary parameters involving tracking errors.

  • Firstly, choose cell C14.
  • Then, type the following formula to find the sum of all squared active returns.
=SUM(G5:G12)
  • After that, hit Enter.

Calculating Tracking Error in Excel

  • So, you will see the sum of all squares.
  • Again, select cell C15.
  • Then, type the following formula.
=C14/7
  • After wad, press Enter.

  • Now, you will get the output.
  • Besides, write down the following formula in cell C16 to determine the monthly standard deviation.
=SQRT(C15)
  • Now, hit Enter.

  • Therefore, you will the monthly standard deviation.
  • Similarly, type the formula to calculate the tracking error in cell C17.
=C16*SQRT(12)
  • Then, press Enter.

  • Finally, you will see the final result of the annual tracking error here in the below image.

Calculating Tracking Error in Excel

Read More: How to Use Macaulay Duration Formula in Excel


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Conclusion

In this article, we’ve covered step-by-step procedures to calculate the tracking error in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bishawajit Chakraborty
Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo