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. The most commonly used correlation coefficient is Person 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). Think of the independent variable as the input and the dependent variable as the output. In financial modeling and analysis, an analyst typically performs sensitivity analysis 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 and may include bonds, stocks, currencies, cash and cash equivalents, and. 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, and more! CFI's Excel resources are the best way to learn Excel on your own terms. These guides and articles teach spreadsheet formulas, shortcuts, and functions step-by-step with screenshots, templates, lessons, guides, and more 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)™FMVA™ CertificationThe Financial Modeling & Valueation Analyst (FMVA)™ accreditation is a global standard for financial analysts that covers finance, accounting, financial modeling, valuation, budgeting, forecasting, presentations, and strategy. certification program for those looking to take their careers to the next level. To keep learning and advancing your career, the following 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. In other words, it is essentially a measure of the variance between two 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 is a powerful tool for testing the power of predictions. A business owner for example might want to make a prediction of that the mean value a customer would greater
• PEARSON FunctionPEARSON FunctionThe PEARSON function is categorized under 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