Correlation Matrix

What is a Correlation Matrix?

A correlation matrix is simply a table which displays the correlationCorrelationA correlation is a statistical measure of the relationship between two variables. The measure is best used in variables that demonstrate a linear relationship between each other. The fit of the data can be visually represented in a scatterplot. coefficients for different variables. The matrix depicts the correlation between all the possible pairs of values in a table. It is a powerful tool to summarize a large dataset and to identify and visualize patterns in the given data.

A correlation matrix consists of rows and columns that show the variables. Each cell in a table contains the correlation coefficient.

In addition, the correlation matrix is frequently utilized in conjunction with other types of statistical analysisBasic Statistics Concepts for FinanceA solid understanding of statistics is crucially important in helping us better understand finance. Moreover, statistics concepts can help investors monitor. For instance, it may be helpful in the analysis of multiple linear regression models. Remember that the models contain several independent variables. In multiple linear regression, the correlation matrix determines the correlation coefficients between the independent variablesIndependent VariableAn independent variable is an input, assumption, or driver that is changed in order to assess its impact on a dependent variable (the outcome). in a model.

How to Create a Correlation Matrix in Excel?

In order to understand the necessary steps in creating a correlation matrix in Excel, let’s consider the following example. You are the stock analyst in the investment bank. Your manager recently asked you to analyze the correlations between prices of stocksCommon StockCommon stock is a type of security that represents ownership of equity in a company. There are other terms – such as common share, ordinary share, or voting share – that are equivalent to common stock. that can be potentially added to the portfolioInvestment PortfolioAn investment portfolio is a set of financial assets owned by an investor that may include bonds, stocks, currencies, cash and cash equivalents, and commodities. Further, it refers to a group of investments that an investor uses in order to earn a profit while making sure that capital or assets are preserved.. You then analyze the stocks of the following companies: NVIDIA, Ford, Shell, and Alphabet.

The best way to analyze the correlations between the stock prices of the abovementioned companies is to create a correlation matrix. It can be done through the following steps:

1. Download the data into ExcelExcel ResourcesLearn Excel online with 100's of free Excel tutorials, resources, guides & cheat sheets! CFI's resources are the best way to learn Excel on your own terms. and arrange the data into the columns.

Each column represents the stock prices of a distinct company for the specified period (from December 2015 to November 2018).

2. Click Data -> Data Analysis -> Correlation
3. Enter the input range that contains the name of the companies and the stock prices.
4. Ensure that Grouped By: Columns option is chosen (because our data is arranged in the columns).
5. Ensure that Labels in First Row option is chosen (the first rows of each column contain the names of the companies).
6. Choose the desired output option (i.e., the location on the spreadsheet where the correlation matrix will appear).
7. Click OK.

Your matrix should look like the image below:

Learn more in CFI’s Advanced Excel Formulas Course.

Additional Resources

CFI offers the Financial Modeling & Valuation Analyst (FMVA)™Become a Certified Financial Modeling & Valuation Analyst (FMVA)® certification program for those looking to take their careers to the next level. To keep learning and advancing your career, the following CFI resources will be helpful:

• CovarianceCovarianceIn mathematics and statistics, covariance is a measure of the relationship between two random variables. The metric evaluates how much - to what extent - the variables change together.However, the metric does not assess the dependency between variables.
• Hypothesis TestingHypothesis TestingHypothesis Testing is a method of statistical inference. It is used to test if a statement regarding a population parameter is correct. Hypothesis testing
• PEARSON FunctionPEARSON FunctionThe PEARSON function is categorized under Excel Statistical functions. It will calculate the Pearson Product-Moment Correlation Coefficient for two sets of values. For example, we can find out the relationship between the age of a person and the appearance of grey hair. As a financial analyst, the PEARSON function is useful
• Poisson DistributionPoisson DistributionThe Poisson Distribution is a tool used in probability theory statistics to predict the amount of variation from a known average rate of occurrence, within