Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Logout MarketXLS
ETFsPortfolio analysis and optimizationPortfolio ManagementStock investments

Correlation Matrix (Measuring Portfolio Diversification)

Written by Vanshika Kothari
Sun Apr 04 2021
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →

Defining Correlation

Correlation is a stock comparison tool that measures the degree to which two securities (in investment) move about each other. It is a handy statistic used for individual securities, like stocks, to measure general market correlation, such as how asset classes or broad markets move concerning each other. The correlation coefficient value must fall between -1 and +1. A perfect positive correlation is when the coefficient value is equal to +1, which means the two securities move in the same direction with the same level. A perfect negative correlation means that two assets move in the opposite direction and a correlation coefficient of 0 indicates no linear relation between the two securities.

Correlation in Portfolio Management

Correlation is a helpful statistic often used in portfolio management. It measures the amount of diversification among the assets contained in a portfolio. Modern portfolio theory (MPT) uses a measure of the correlation of all the assets in a portfolio to help determine the most efficient frontier. This concept helps to optimize expected returns against a certain level of risk. Including assets that have a low correlation to each other reduces the overall risk for a portfolio.

The correlation coefficient is a linear regression performed on each stock’s returns against the other. If mapped graphically, a positive correlation would show an upward-sloping line. A negative correlation would show a downward-sloping line. While the correlation coefficient is a measure of the historical relationship between two stocks, it may guide the future relationship between the assets.

Correlation Matrix

Correlation works fine if we have two stocks in the portfolio. However, when we have more stocks, we resort to a matrix to find their correlations. So, when we have multiple stocks in the portfolio, the correlations between stocks are all stacked up in a n x n (read it as n by n) matrix. For instance, if it is a 10-stock portfolio (10 being the n here), we need to create a 10 x 10 matrix.  

The correlation matrix gives us the correlation between any two stocks. For example, to know the correlation between AAPL and FB, look under the intersecting cell between AAPL and FB.

MarketXLS Template for Correlation Matrix

Correlation Matrix Template- Stock Comparison Tool
Find this template: https://marketxls.com/template/correlation-matrix-r/

Through this matrix, investors can create a portfolio of stocks based on their risk and correlation coefficients. In the Template, the investor can create an N*N matrix of stocks and compare the overall correlation of stocks. All you need to do is add the ticker of the stocks part of your portfolio in the ticker column.

Why is it important for investors?

While focusing on the portfolio’s individual stocks’ performance, it is equally important to understand how the stocks move alongside each other. It would give a more cohesive view of their portfolio.

Landsberg rightly says that investors usually may not be as diversified as they think and may have stocks in different sectors. Still, if their returns depend on the same thing (e.g., the economy in a particular state), their portfolio is getting almost no protection from diversification.        

Sometimes stock correlation can be predictable so that two stocks in the same industry or sector will tend to move in the same direction and react to the market in the same way. However, it may not be easy to spot if the portfolio consists of various securities such as mutual funds, bonds, or ETFs.

Correlation Matrix Template- Stock Comparison Tool
Find this template: https://marketxls.com/template/correlation-matrix-r/

Not only stocks but the same matrix can help measure the correlation between different combinations of stocks, mutual funds, and exchange-traded debt funds. As seen in the MarketXLS template screenshot, a different set of securities is taken as part of the portfolio to measure the correlation. Other than stocks, the portfolio also includes Vanguard Intermediate, iShares Core S&P Total U.S. Stock Market ETF, and SPDR Portfolio Short Term Corporate Bond ETF. The Short Term Corporate Bond ETF (SPSB) tracks a market-value-weighted index of fixed-rate investment-grade nonconvertible US corporate bonds with 1-3 years remaining in maturity.

The Bottom Line

As it is always suggested not to put all your eggs in one basket similarly, investors should have a diversified pool of assets in their portfolio. This diversification can help them reduce Idiosyncratic risk.

The correlation matrix can be beneficial and handy for investors with a portfolio of multiple securities. The investor cognizes the overall diversification in the portfolio with this matrix’s help. Choosing assets with low correlation with each other can help to reduce the risk of a portfolio.                                                                           




None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person.

The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein.

The article is written for helping users collect the required information from various sources deemed to be an authority in their content. The images, copyrights, and trademarks if any are the property of their owners, and no further representations are made.

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
Download Option Templates
Stock screens
Public saved screens

Top 100 Gainers Today

Top 100 losers Today

Call: 1-877-778-8358
Ankur Mohan MarketXLS
I am so happy you are here. My name is Ankur and I am the founder/CEO of MarketXLS. Over the past four years, I have helped more than 2500 customers to implement their own investment research strategies and monitoring systems in Excel.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONSGet started today
Search for a stock

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today

Top MarketXLS Rank stocks

STORE Capital Corporation logo

STORE Capital Corporation

Optionable: Yes
Market Cap: 8,978 M
Industry: REIT - Diversified
52 week range    
Dillard's Inc. logo

Dillard's Inc.

Optionable: Yes
Market Cap: 5,077 M
Industry: Department Stores
52 week range    
Williams-Sonoma Inc. (DE) logo

Williams-Sonoma Inc. (DE)

Optionable: Yes
Market Cap: 8,796 M
Industry: Specialty Retail
52 week range    
ICF International Inc. logo

ICF International Inc.

Optionable: Yes
Market Cap: 2,040 M
Industry: Business Services
52 week range    
First Interstate BancSystem Inc. logo

First Interstate BancSystem Inc.

Optionable: Yes
Market Cap: 4,622 M
Industry: Banks - Regional - US
52 week range    
Global Cord Blood Corporation logo

Global Cord Blood Corporation

Optionable: Yes
Market Cap: 317 M
Industry: Biotechnology
52 week range    
Penske Automotive Group Inc. logo

Penske Automotive Group Inc.

Optionable: Yes
Market Cap: 7,684 M
Industry: Auto & Truck Dealerships
52 week range    
General Mills Inc. logo

General Mills Inc.

Optionable: Yes
Market Cap: 44,923 M
Industry: Packaged Foods
52 week range    
Allegiance Bancshares Inc. logo

Allegiance Bancshares Inc.

Optionable: Yes
Market Cap: 883 M
Industry: Banks - Regional - US
52 week range    
National Research Corporation logo

National Research Corporation

Optionable: No
Market Cap: 948 M
Industry: Diagnostics & Research
52 week range    

More Features