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

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.

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

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

• After that, hit Enter.

• So, you will see the sum of all squares.
• Again, select cell C15.
• Then, type the following formula.

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

• Now, hit Enter.

• Therefore, you will the monthly standard deviation.
• Similarly, type the formula to calculate the tracking error in cell C17.

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

Related Articles

• XIRR vs IRR in Excel
• Excel XNPV vs NPV: Comparison with Examples
• How to Calculate WACC in Excel
• How to Calculate Mileage Reimbursement in Excel
• How to Calculate Time Weighted Return in Excel

<< Go Back to Excel Formulas for Finance | Excel for Finance | Learn Excel