Microsoft Excel Tutorial — How to use Covariance function

What Is Covariance?

Covariance measures how two variables move together. It measures whether the two move in the same direction (a positive covariance) or in opposite directions (a negative covariance). In this article, the variables will usually be stock prices, but they can be anything.

Covariance calculations can give an investor insight into how two stocks might move together in the future. Looking at historical prices, we can determine if the prices tend to move with each other or opposite each other. This allows you to predict the potential price movement of a two-stock portfolio.

The covariance of two variables tells you how likely they are to increase or decrease simultaneously.

A high, positive covariance value between two stocks means that when the price of one goes up, that of the other usually does too.

A high negative figure or value means that when one stock advances, the other generally retreats. If the figure is close to zero, with either a positive or negative sign, the two stocks act mostly independently.

Excel Covariance Syntax

The syntax for the Microsoft Excel COVARIANCE function is:

Array 1 & Array 2 are two ranges or arrays of integer values.

The covariance is:

Where are the sample means AVERAGE(array1) and AVERAGE(array2), and n is the sample size.

• The arguments must either be numbers or be names, arrays, or references that contain numbers.

• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• If array1 and array2 have different numbers of data points, COVAR returns the #N/A error value.
• If either array1 or array2 is empty, COVAR returns the #DIV/0! error value

Example: Covariance of Two Assets

A simple example can help illustrate how we can use covariance to describe the relation between two investments. Consider a portfolio with the following two assets.

At first glance, we can see that Asset A has more variability. Asset A has an overall higher average return, but the variability of the return is also much greater. Asset B could be described as slow and steady.

Lets use Excel to calculate the covariance of this simple example. Open Excel and enter the returns of these two assets as they are shown above. Format Column B & Column C to percentage.

In a new cell B9, use the COVAR function to calculate the covariance. Change the format of cell B9 to percentage (up to 3 decimal places).

Enter the first parameter Array 1, B3 to B7 (B3:B7)