Correlation Matrix and Portfolio Variance Calculation for Risk Management

Correlation Matrix

The Variance-Covariance Matrix constructed in the preceding chapter produces figures that are mathematically correct but practically difficult to interpret. The numbers are too small in absolute terms to convey meaningful insight about the relationships between stocks. Converting the Variance-Covariance Matrix into a Correlation Matrix solves this problem by expressing those relationships on a standardised scale that is immediately comparable across all stock pairs.

The formula for correlation between any two stocks is as follows.

Correlation (x, y) = Covariance (x, y) divided by (Standard deviation of x multiplied by Standard deviation of y)

Where Cov (x, y) represents the covariance between the two stocks, sigma x represents the standard deviation of stock x, and sigma y represents the standard deviation of stock y.

When a portfolio contains more than two stocks, calculating all possible correlations requires a matrix operation rather than individual pairwise calculations. The resulting matrix has dimensions of n by n, where n equals the number of stocks in the portfolio. For a five-stock portfolio, this produces a 5 by 5 matrix containing 25 correlation values.

The numerator of the correlation formula is the Variance-Covariance Matrix already calculated. The denominator requires the product of the standard deviations for every possible pair of stocks in the portfolio.

Calculating the Denominator: Standard Deviation Products

The first task is to calculate the standard deviation of daily returns for each stock individually. In Excel, this is done using the STDEV function applied to the daily returns array for each stock. This produces five standard deviation figures, one for each holding in the portfolio.

Once the individual standard deviations are in place, matrix multiplication is used to calculate the product of every possible combination across the portfolio. This is achieved by multiplying the standard deviation array by its own transpose, following the same array multiplication logic used in the previous chapter. The skeleton of the n by n matrix is set up first, all cells are kept selected, and the MMULT function is applied to the standard deviation array and its transpose. As with all array functions in Excel, the operation must be confirmed using Ctrl plus Shift plus Enter rather than Enter alone. The result is a 5 by 5 matrix of standard deviation products covering all possible stock pairings.

Producing the Correlation Matrix

With the numerator and denominator both in matrix form, the Correlation Matrix is produced by dividing the Variance-Covariance Matrix element by element by the standard deviation product matrix. This division is again performed as an array operation, requiring Ctrl plus Shift plus Enter to execute correctly.

The resulting Correlation Matrix presents a far more interpretable set of figures than the Variance-Covariance Matrix. Each value in the matrix is a correlation coefficient falling between minus one and plus one.

Reading the Correlation Matrix

The matrix is read in the same way as the Variance-Covariance Matrix. To find the correlation between any two stocks, one locates the row corresponding to one stock and reads across to the column corresponding to the other. Because correlation is symmetrical, the value at row A, column B is always identical to the value at row B, column A. Both positions in the matrix will show the same figure.

To illustrate, consider the correlation between the pharmaceutical company and the specialty chemicals manufacturer in the portfolio. Examining the pharmaceutical company’s row across to the specialty chemicals column produces a correlation of 0.2315. Examining the specialty chemicals row across to the pharmaceutical column returns the identical figure. The matrix confirms this symmetry throughout.

The values running along the diagonal of the matrix, where each stock intersects with itself, always equal exactly 1. This is mathematically inevitable: any variable is perfectly correlated with itself. These diagonal values serve as a useful check that the matrix has been constructed correctly.

The figures above and below the diagonal are mirror images of each other, reflecting the symmetrical nature of correlation. This structure makes the matrix straightforward to navigate once the layout is understood.

What the Correlation Matrix Reveals

The Correlation Matrix gives investors and portfolio analysts a clear, comparable picture of how every stock in the portfolio relates to every other. Low correlation values between holdings confirm that the portfolio is genuinely diversified, with each stock responding to different market conditions. High positive correlations between multiple holdings would suggest that the portfolio carries more concentrated risk than the number of stocks alone might imply.

This insight directly informs decisions about asset allocation and position sizing, both of which have a substantial bearing on overall portfolio risk and returns. With the Correlation Matrix in place, the next step is to use these figures alongside the Variance-Covariance Matrix to calculate overall portfolio variance, which remains the ultimate objective of this analytical sequence.