Covariance Matrix in Excel

Covariance Matrix in excel

The covariance matrix is a square matrix to show the covarianceCovarianceCovariance is a statistical measure used to find the relationship between two assets and is calculated as the standard deviation of the return of the two assets multiplied by its correlation. If it gives a positive number then the assets are said to have positive covariance i.e. when the returns of one asset goes up, the return of second assets also goes up and vice versa for negative covariance.read more among the columns and variance in columns. Excel presented with an inbuilt ‘Data analysis’ tool to determine the covariance between the different data sets. The present article will explain the calculation of the covariance matrix in Excel by covering the following topics, including

Explanation

Covariance is one of the measures used for understanding how a variable is associated with another variable. The following formula is used for covariance determination.

COV (X,Y) = ∑(x – x) (y – y) / n

The covariance matrix is a square matrix to understand the relationships presented between the different variables in a dataset. It is easy and useful to show the covariance between two or more variables.

The covariance will have both positive and negative values. A positive value indicates that two variables will decrease or increase in the same direction. A negative value indicates that if one variable decreases, other variable increases, and an inverse relationship exist between them. The covariance matrix is represented in the following format. The three-dimensional covariance matrix is shown as

3 Dimensional Covariance

To create the 3×3 square covariance matrix, we need to have three-dimensional data. The diagonal values of the matrix represent the variances of X, Y, and Z variables (i.e., COV(X, X), COV(Y, Y), and COV (Z, Z)). The covariance matrix is symmetric with respect to diagonal. This indicates that COV(X,Y)=COV(Y,X), COV(X,Z)=COV(Z,X), and COV(Y,Z)=COV(Z,Y). One point to remember about this matrix is the resulting from the NXN covariance matrix for data of n-dimensional.

covariance matrix in excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Covariance Matrix in Excel (wallstreetmojo.com)

How to Use a Covariance Matrix in Excel?

The covariance matrix is used in various applications, including

Examples of Covariance Matrix in Excel

Given below are some of the examples to use the covariance matrix in excel.

You can download this Covariance Matrix Excel Template here – Covariance Matrix Excel Template

Example #1

Performing covariance analysis on the marks obtained by students in different subjects.

Step 1: The following data, including the marks of students in Mathematics, English, and Science, are considered as shown in the figure.

Excel Covariance Matrix- Example 1.1

Step 2: Go to the “Data” tab on the ribbon and find the ‘Data analysis’ ToolPak on the right side corner.

If “Data analysis” ToolPak not available, then follow these steps.

Step A: Go to the ‘File’ tab and then select the “options.”

Covariance - Excel Option

The following screen will be opened.

Excel Options Window

Step B: Go to Add-ins. Under the Manage Option, ensure that ‘Excel add-insExcel Add-insAdd-ins are different Excel extensions that can be found in the options section of the file tab. The first box displays the system's enabled add-ins, and if the user wishes to enable more, they must click on manage add-ins.read more’ is selected and select the ‘Go’ button, as shown in the figure.

Addin Option

Step C: Select the “Analysis-Tool Pak” and “Analysis-ToolPak VBA,” as shown in the screenshot.

Addins

After completing these steps, the “Data analysis” tool pack is added to the ‘Data’ tab.

Data Analysis Option

Step 3: Click on Data Analysis. This opens the “Data Analysis” dialog box. Select the “Covariance” by scrolling up and click on “OK.”

Covariance Option

It displays the “Covariance” dialog box.

Covariance Window

Step 4: Select the range of input, including the subject names, check the “labels in First Row,” and give the “output range” in the existing worksheet. And click on “OK.”

Covariance - Example 1.2

Step 5: We will get the output as follows –

Covariance - Example 1 (Output)

The upper part of the diagonal is empty as the excel covariance matrix is symmetric towards the diagonal.

Example #2

Perform calculation of the covariance matrix to determine variances between the returns of different portfolio stocks.

Step 1: For this example, the following data, including the stock returns, are considered.

Excel Covariance Matrix - Example 2

Step 2: Opens the “Data Analysis” dialog box and select the “Covariance” by scrolling up and click on “OK.”

Covariance Option

It displays the “Covariance” dialog box.

Covariance Window

Step 3: Select the range of input, including the headers, check the “labels in First Row” and give the “output range” in the existing worksheet. And click on “OK.”

Covariance - Example 2.1

Step 4: We will get the output as follows –

Excel Covariance Matrix - Example 2 (Output)

The upper part of the diagonal is empty as the covariance matrix is symmetric towards the diagonal.

Example #3

Calculation of covariance matrix for stock prices of corporate companies

Step 1: For this example, the following data, including stock prices of different companies, are considered.

Excel Covariance Matrix - Example 3

Step 2: Opens the “Data Analysis” dialog box, select the “Covariance” by scrolling up and click on “OK.”

Covariance Option

It displays the “Covariance” dialog box.

Covariance Window

Step 3: Select the range of input, including the headers, check the “labels in First Row” and give the “output range” in the existing worksheet and click on “OK.”

Covariance - Example 3.1

Step 4: We will get the output as follows –

Excel Covariance Matrix - Example 3 (Output)

Things to Remember

  • The covariance tool presented by Excel has some limitations, including the determination of only population variances formula, creation of matrix with only lower diagonal values, and consideration of formulae only for a variance.
  • When return values are changed, it doesn’t automatically update the values of the matrix.
  • The upper half of the matrix is displayed as empty since it is symmetric, and mirror-image values are displayed in the lower diagonal.

Recommended Articles

This has been a guide to the Covariance matrix in Excel. Here we discuss how to use the covariance matrix in excel along with the examples and downloadable excel sheet. You can learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>