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.
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
- 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
- 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.
- 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.
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.
