How to Calculate Portfolio Tracking Error in Excel

What is Tracking Error?

Tracking error is a quantitative measure that shows how closely a portfolio tracks the performance of a chosen benchmark. It is defined as the standard deviation of active returns, where active (or excess) return is the difference in return between the portfolio and the benchmark over a specific period. Rather than measuring absolute volatility, tracking error captures relative volatility, or the variability of deviations in performance from the benchmark.

A lower tracking error indicates that a portfolio consistently mirrors its benchmark, while a higher tracking error indicates frequent divergence from the benchmark, both above and below it. As it is a volatility measure, rather than a volatility measure, a portfolio can have a higher tracking error even if its long-term average return matches that of the benchmark.

Importance of Tracking Error

Tracking error is important for various types of investors and investment strategies because it measures benchmark-relative risk. While total volatility captures absolute risk, tracking error isolates the variability of active returns (i.e., the extent to which a portfolio deviates from its benchmark). This is fundamental for any portfolio that is managed relative to a benchmark, either passive or active.

Ultimately, tracking error helps investors distinguish between skill and noise, and assess whether the portfolio behaves in line with its stated investment objectives and risk parameters.

Formula for Tracking Error

Tracking error is calculated as the standard deviation of active returns, where active return is the difference between the portfolio return and the benchmark return. Below is the formula:

Where:

Rp = portfolio return

Rb = benchmark return

o(Rp-Rb) = standard deviation of the active return (portfolio minus benchmark)

It is important to note that tracking error measures volatility around the mean active return and should not be mistaken with tracking difference (which shows the consistent under- or outperformance). This means that a portfolio could theoretically have an average active return of zero, yet can exhibit a high tracking error if returns fluctuate widely relative to the benchmark. Generally, tracking error can be calculated in two ways:

Ex-Post (or “Historical”) Tracking Error

This method measures the actual past volatility of the difference between a portfolio’s returns and those of its benchmark. It shows how closely the portfolio has historically followed the benchmark. For example, if a fund has a tracking error of 1% over the past year, its returns deviated from the benchmark with a standard deviation of 1%.

Ex-Ante (or “Predicted/Future”) Tracking Error

As the name suggests, this method estimates the expected deviation in the future between the portfolio and its benchmark. Its calculation uses expected returns, variances, and covariances of the portfolio against its benchmark, and is usually derived from factor or risk models. It is used by portfolio managers to predict risk before making trades. For example, if an active manager is considering a strategy that could deviate 3% from the benchmark, the ex-ante tracking error helps anticipate that.

Example of Tracking Error Calculation Excel

Below is a practical example of the tracking error calculation in Excel. If you like more examples of related topics, take our portfolio management certification and master practical skills needed to succeed in your finance career.

By using the provided portfolio and benchmark returns, we need to first find the active (or excess) return.

Then we need to calculate the standard deviation of the active returns.

Factors Impacting Tracking Error

There are various factors around the construction, management, and operations of a portfolio that influence its tracking error. Below, we review the most common ones:

Active Management Decisions

Active managers deliberately position their portfolio differently against the benchmark. Examples of this include:

• Overweighting or underweighting certain sectors – for example, this may be a conviction, risk or liquidity-driven decision
• Selecting securities not included in the index – off-benchmark positions increase the overall risk, but may generate additional alpha if the investment case holds
• Avoiding specific benchmark constituents – this could be due to many reasons, for example, an overvalued stock price, declining profits or structural/management change within the company

Each active choice introduces the possibility of divergence from the benchmark, increasing tracking error. The more concentrated the portfolio, the greater the potential deviation from benchmark returns.

Index Replication Method

Strategies that aim to replicate the performance of an index (such as ETFs) may use:

• Full replication – this means that it holds every constituent in the benchmark
• Sampling – i.e. holding a representative subset of securities. This approach could reduce transaction costs but often increases the tracking error. This is often evident in indices with a large number of holdings or high turnover

Rebalancing Frequency

Market indices are periodically rebalanced. If a portfolio rebalances at different times or uses different methodologies, it may cause return deviations relative to the benchmark. For example, less frequent portfolio rebalancing can result in higher tracking error during periods of market turbulence.

Fees and Other Costs

Management fees, trading costs, and operating expenses reduce portfolio returns but are not reflected in benchmark returns. Therefore, higher expenses usually increase tracking error. This is more visible in passive strategies that are aiming to closely replicate the performance of an index.

Portfolio Turnover

Higher portfolio turnover means more frequent trading and higher transaction costs respectively. This could also result in timing differences relative to the benchmark. These effects accumulate over time and can meaningfully increase tracking error (especially in the case of more volatile or less liquid markets).

Market Volatility

During periods of elevated market volatility, even small differences between the portfolio holdings and benchmark weights can generate large return gaps. As a result, tracking error often rises during market stress. Strategies that aim to closely track an index are also not immune to producing higher tracking error.

Tax Considerations

Benchmarks typically ignore taxes and therefore tax-aware portfolio management (for example portfolios that aim to optimize net returns through capital gains realization or dividend taxation) can increase tracking error despite improving after-tax outcomes.

The Use of Derivatives

Derivative instruments such as futures, options, or swaps are sometimes used for cash management or exposure adjustments. While they can improve efficiency, if not implemented appropriately, hedging (or contract roll costs) may introduce additional sources of tracking error.

Currency Exposure

Portfolios that invest globally may use currency hedging strategies. This often impacts tracking error materially.

What is a Good Tracking Error for a Portfolio?

The appropriate level of tracking error depends on the portfolio’s objectives, investment approach and risk constraints.

If we look into passive portfolios, they are designed to replicate the performance of a benchmark and are expected to exhibit very low tracking error. This would typically be in the range of 0.05% (for established funds that track more efficient markets such as the S&P 500) to 0.50% (for strategies that aim to track less liquid and/or efficient markets). Usually, higher tracking error may signal inefficiencies, higher costs, or poor replication.

For enhanced index or factor strategies (which aim to slightly outperform the benchmark while maintaining similar risk characteristics), a typical tracking error range is between 0.50% and 2.00%. In this case, moderate deviations should be expected, due to active tilts.

Actively managed strategies that intentionally deviate from their target index in order to generate excess returns may expect a tracking error of 2% and above. This is highly subjective and depends on the agreed risk parameters, higher tracking error indicates a higher level of active risk. This means that returns may differ significantly from the benchmark in either direction.

Because tracking error is a relative risk measure (not absolute risk), a portfolio can have low volatility but high tracking error if it consistently diverges from the benchmark.

What Does a 5% Tracking Error Mean?

A 5% tracking error indicates that the portfolio’s returns materially deviate from those of the benchmark. Assuming annualized data and a normal distribution of returns, this means that the portfolio may out-or underperform the index by roughly 5 percentage points in any given year about two-thirds of the time. For example, if a benchmark returns 10% in a year, there is roughly a two-thirds probability that the portfolio will generate returns between 5% and 15%, based on its tracking error.

This level of tracking error suggests a highly active investment approach with meaningful deviations in terms of underlying holdings, sector, or regional weights. It is common for concentrated equity funds, thematic or unconstrained strategies with a long-term investment horizon.

Conclusion

To sum up, tracking error provides investors with insight into how a portfolio behaves relative to its benchmark. Analyzing the portfolio’s consistency and the magnitude of return deviations also helps set realistic expectations. However, it is important that tracking error is considered alongside the portfolio’s investment objectives, both for passive and active strategies.

Additional Resources

How to Calculate Portfolio Tracking Error in Excel
How To Get A Job In Asset Management?
How to Monitor Portfolio Performance
Hub and Spoke Structure