How to Calculate Tracking Error in Excel: 4 Methods

Method 1 – Make a Dataset with Proper Parameters

• To calculate tracking errors, we will make a dataset.

Method 2 – Determine Active Return

• Select cell F5.
• Wwrite down the following formula.

• Press Enter and use the Fill Handle to drag rightward to the remaining cells to see the result.

Method 3 – Evaluate Squared Active Return

• Select Cell F5.
• Write down the following formula.

• Press Enter and use the Fill Handle to drag rightward to the remaining cells to see the result.

Method 4 – Calculate Tracking Error in Excel

• Choose cell C14.
• Type the following formula to find the sum of all squared active returns.

• Hit Enter.

• You will see the sum of all squares.
• Select cell C15.
• Type the following formula.

• Press Enter.

• You will get the output.
• Write the following formula in cell C16 to determine the monthly standard deviation.

• Hit Enter.

• You will see the monthly standard deviation.
• Type the formula to calculate the tracking error in cell C17.

• Press Enter.

• You will see the final result of the annual tracking error here in the below image.

Download Practice Workbook

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

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