Calculate the covariance of stocks

Use the closing price to calculate the daily return of the two stocks
day ABC returns XYZ return
1 1.1% 3.0%
2 1.7% 4.2%
3 2.1% 4.9%
4 1.4% 4.1%
5 0.2% 2.5%

Next, we need to calculate the average return of each stock:

  • For ABC, it will be (1.1 + 1.7 + 2.1 + 1.4 + 0.2) / 5 = 1.30.
  • For XYZ, it will be (3 + 4.2 + 4.9 + 4.1 + 2.5) / 5 = 3.74.
  • Then, we multiply the difference between ABC’s return and ABC’s average return by the difference between XYZ’s return and XYZ’s average return.
  • Finally, we divide the result by the sample size and subtract 1. If it is the entire population, you can divide by the size of the population.

This is represented by the following equation:

Covariance = ∑ (return ABC − average ABC) ∗ (return XYZ − average XYZ) (sample size) − 1 text{Covariance}=frac{sum{left(Return_{ABC}text{ }- text{ }Average_{ABC}right)text{ }*text{ }left(Return_{XYZ}text{ }-text{ }Average_{XYZ}right)}}{left (text {Sample size}right)text{ }-text{ }1}

Covariance=(Sample size) 1(resistanceelectronicTonyournOneSecondC OnevelectronicrOneGelectronicOneSecondC) (resistanceelectronicTonyournXYesZ OnevelectronicrOneGelectronicXYesZ)

Using the ABC and XYZ examples above, the covariance is calculated as follows:

  • = [(1.1 – 1.30) x (3 – 3.74)] + [(1.7 – 1.30) x (4.2 – 3.74)] + [(2.1 – 1.30) x (4.9 – 3.74)] + …
  • = [0.148] + [0.184] + [0.928] + [0.036] + [1.364]
  • = 2.66 / (5-1)
  • = 0.665
READ ALSO:   de-trend

In this case, we are using samples, so we divide by the sample size (five) minus one.

The covariance between the returns of the two stocks is 0.665. Because this number is positive, the stock moves in the same direction. In other words, when ABC has high returns, XYZ also has high returns.

Covariance in Microsoft Excel

In Excel, you can use one of the following functions to find the covariance:

  • = COVARIANCE.S() for the sample
  • = COVARIANCE.P() for the overall

You need to set up two return lists in the vertical column, as shown in Table 1. Then, when prompted, select each column. In Excel, each list is called an “array”, and the two arrays should be placed in parentheses and separated by commas.


In this example, there is a positive covariance, so the two stocks tend to move together. When one stock has a positive return, the other stock also often has a positive return. If the result is negative, the returns of the two stocks are often opposite-when the return of one stock is positive, the return of the other stock is negative.

READ ALSO:   Camouflage compensation

Use of covariance

Finding that two stocks have high or low covariance may not be a useful indicator in itself. Covariance can explain how stocks move together, but to determine the strength of the relationship, we need to look at their correlation. Therefore, correlation should be used in conjunction with covariance and expressed by the following equation:

Correlation = ρ = cov (X, Y) σ X σ Y where: cov (X, Y) = the covariance between X and Y σ X = standard deviation of X σ Y = standard deviation of Ybegin{aligned } &text {Correlation}=rho=frac{covleft(X, Yright)}{sigma_Xsigma_Y}\ &textbf{where:}\ &covleft(X, Y right)= text{Covariance between X and Y}\ &sigma_X=text{Standard deviation of X}\ &sigma_Y=text{Standard deviation of Y}\ end{aligned}

Correlation=ρ=σXσYesCv(X,Yes)Where:Cv(X,Yes)=Covariance between X and YσX=Standard deviation of XσYes=Standard deviation of Y

The above equation reveals that the correlation between two variables is the product of the covariance between the two variables divided by the standard deviation of the variable. Although both measures reveal whether two variables are positively correlated or negatively correlated, correlation provides additional information by determining the degree to which the two variables move together. The measure of correlation is always between -1 and 1, and adds a strength value to how the stock moves together.

READ ALSO:   Richard H. Anderson

If the correlation is 1, they move together perfectly, and if the correlation is -1, the stocks move in the opposite direction completely. If the correlation is 0, the two stocks move in random directions with each other. In short, covariance tells you that two variables change in the same way, while correlation reveals how changes in one variable affect changes in another variable.

You can also use covariance to calculate the standard deviation of a multi-stock portfolio. Standard deviation is a generally accepted method of calculating risk and is extremely important when choosing stocks. Most investors want to choose stocks that fluctuate in the opposite direction because the risk will be lower, but they will provide the same amount of potential returns.

Bottom line

Covariance is a common statistical calculation that can show how two stocks move together. Because we can only use historical returns, we can never be completely certain of the future. In addition, covariance should not be used alone. Instead, it should be used in conjunction with other calculations (such as correlation or standard deviation).


Share your love