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.
It helps in determining dependencies among variables, which is done through a table of matrix depicting correlation across variables as depicted below.
Excerpt of a Correlation matrix across different maturity Bonds.
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 coefficient 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 like in our case; we have taken the Nifty price Index and certain Equity Stocks, which are part of the Nifty index.
- 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:
Xavier Bank has classified its exposure in Bonds based on residual maturity as follows:
It has created a Correlation matrix across different tenor bonds based on the price movement using Excel tool (discussed above) as shown below:
4.9 (927 ratings) 16 Courses | 15+ Projects | 90+ Hours | Full Lifetime Access | Certificate of Completion
Xavier Bank calculated its exposure wise matrix across various tenors, as shown below:
Find the below Excel sheet for detail calculation:
Correlation Matrix vs. Covariance Matrix
|Basis||Correlation Matrix||Covariance Matrix|
|Relationship||It 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 Range||It 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.|
|Dimension||It is dimensionless.||Covariance Matrix has dimension.|
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 –