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 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. 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 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). 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.
- 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 the Excel tool (discussed above), as shown below:
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 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 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 –