Correlation Matrix

Correlation Matrix

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: Correlation Matrix (wallstreetmojo.com)

What is the Correlation Matrix?

Correlation Matrix is a statistical method of showing the relationship between two or more variables and the interrelation in their movements etc. In short, it helps in defining the relationship and dependence among the variables. It is a very commonly used mechanism and finds its application in the field of Investment Management, Risk Management, Statistics as well as Economics to name a few.

Explanation

It helps in determining dependencies among variables, which is done through a table of matrix depicting correlationCorrelationCorrelation is a statistical measure between two variables that is defined as a change in one variable corresponding to a change in the other. It is calculated as (x(i)-mean(x))*(y(i)-mean(y)) / ((x(i)-mean(x))2 * (y(i)-mean(y))2.read more across variables as depicted below.

Excerpt of a Correlation matrix across different maturity Bonds.

Correlation Matrix (Maturity Buckets)

The above table is a correlation matrix between different Bonds issued by the Government with different residual maturity stated in the form of years in both horizontal and vertical buckets. It enables us to interpret that a bond with 0.25 years to maturity and a bond with 0.5 years to maturity has a correlation coefficientCorrelation CoefficientCorrelation Coefficient, sometimes known as cross-correlation coefficient, is a statistical measure used to evaluate the strength of a relationship between 2 variables. Its values range from -1.0 (negative correlation) to +1.0 (positive correlation). read more of 0.97 in their price movements and similarly for other maturity bonds.

How to Create a Correlation Matrix in Excel?

  • Create the data for which correlation needs to be undertaken. In our case, we have taken the Nifty price Index and certain Equity Stocks, which are part of the Nifty index.
Correlation Matrix Example 1
  • Use the Correlation function under the Data Analysis feature available under the Data tab in Microsoft Excel.
  • Select the Input range of data as presented above and click OK.
  • The matrix will be created by Excel, as shown below:
Correlation Matrix Example 1-1

Examples

Xavier Bank has classified its exposure in Bonds based on residual maturity as follows:

Correlation Matrix Example 2

It has created a Correlation matrix across different tenor bonds based on the price movement using the Excel tool (discussed above), as shown below:

Example 2-1

Xavier Bank calculated its exposure wise matrix across various tenors, as shown below:

Example 2-2

Find the below Excel sheet for detail calculation:

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

Correlation Matrix vs. Covariance Matrix

BasisCorrelation MatrixCovariance Matrix
RelationshipIt helps in measuring both the direction (Positive/Negative) as well as the intensity of interrelationship (Low/Medium/High) between variables.It measures only the direction of the relationship between variables.
Subset and Well defined RangeIt is a subset of Covariance and has a defined range of values between (-1 to 1).It is a broader concept, however, doesn’t have any defined range (can go up to infinity), nor its values alone can help in determining the relationship fully.
DimensionIt is dimensionless.Covariance MatrixCovariance MatrixThe covariance matrix is a square matrix depicting how one variable is associated with another variable or data sets. Excel has an inbuilt ‘Data analysis’ tool to determine the covariance among different columns and variance in columns. Formula: COV (X,Y) = ∑(x – x) (y – y)/n read more has dimension.

Recommended Articles

This has been a guide to what is correlation matrix and its definition. Here we discuss examples, reason, application, and how to create a correlation matrix in excel. You may learn more about financing from the following articles –

  • 16 Courses
  • 15+ Projects
  • 90+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>