COVARIANCE

Calculates the covariance between two sets of data.

Syntax:

COVARIANCE(arrayOne, arrayTwo)

arrayOne is required, and is the first set of data. It can be a range of cells that represents an array of values.

arrayTwo is required, and is the second set of data. It can be a range of cells that represents an array of values.

Example:

If arrayOne, found in A1:A5, contains numbers 10, 15, 20, 25, 30 and arrayTwo, found in B1:B5, contains numbers 5, 8, 12, 15, 20:

COVARIANCE(A1:A5, B1:B5)

returns 37

This example finds the covariance between the values for Sales and the values for Advertising.

	Sales	Advertising
	A	B	C
1	10	5	37
2	15	8
3	20	12
4	25	15
5	30	20

Application:

Analyzing Stock Returns for Portfolio Diversification

In finance, covariance is a crucial tool for Modern Portfolio Theory (MPT). It helps investors understand the relationship between the returns of different assets and is used to build diversified portfolios that aim to maximize returns for a given level of risk.

Scenario: An investor wants to analyze the relationship between the daily returns of two stocks, Stock A (a tech company) and Stock B (a utility company), to see if they are a good pair for a diversified portfolio. They collect the daily return data for five days.

Data Table:

	Day	Daily Return of Stock A (%) - X	Daily Return of Stock B (%) - Y
	A	B	C
1	1	2.5	1
2	2	-1.5	2
3	3	3	0.5
4	4	0.5	1.5
5	5	-2	2.5

Applying the COVARIANCE Function

You would use the COVARIANCE function to calculate the covariance. The function takes two arguments: the range of the first variable (Daily Return of Stock A) and the range of the second variable (Daily Return of Stock B).

The formula would be:

COVARIANCE(B1:B5, C1:C5)

Where B1:B5 is the range for Daily Return of Stock A and C1:C5 is the range for Daily Return of Stock B.

Conclusion:

The calculated covariance is -1.4.

Since the covariance is a negative number (-1.4 < 0), it indicates a negative linear relationship between the daily returns of Stock A and Stock B.
This means that when the return of Stock A is above its average, the return of Stock B tends to be below its average, and vice versa.
In portfolio management, this is a highly desirable characteristic. By combining assets with negative covariance, an investor can offset losses in one investment with gains in another, thereby reducing the overall risk (volatility) of the portfolio. This is a fundamental principle of diversification.

Result for COVARIANCE(B1:B5, C1:C5):

-1.4