How to conduct a Principal Component Analysis in EXCEL – Covariance Matrix

< 1 min read

Continuing with our construction of the Principal Component Analysis (PCA) process in EXCEL, we will see how the adjusted rates computed earlier will be used to arrive at the next stage in the process, the covariance matrix of differences between consecutive rates in a particular time series of rates.

Calculate consecutive rate differences

For each rate series calculate the difference between successive rates. For example for the three month tenor the rate difference on 3-Jan-2008 will be = 3.24% -3.26% =0.02%. Below is an extract of the differences derived from the selected rate data:

In a more generalized form we calculate the difference di,j = r(ti +1 , τj)- r(ti, τj).

Calculate covariance matrix, Σ

We will calculate covariances between all the differences time series obtained in the previous step. These covariances are presented in the form of a matrix. The covariances are calculated using the Excel function COVAR(array1,array2). For example if we are determining the covariance between the differences of the 3 month tenor and the differences of the 6 month tenor we will select array 1 to be the entire differences times series data for the 3 month tenor and array 2 to be the entire differences times series data for the 6 month tenor. The resulting covariance matrix is given below:

We have seen how the covariance matrix was determined in this stage of the PCA process. In the next post we will see how the eigenvector matrix and its inverse are defined.

If you would like to buy this course as a PDF file or the sample EXCEL sheets, please see the Interest Rate Modelling section at our online finance course store.

2 thoughts on “How to conduct a Principal Component Analysis in EXCEL – Covariance Matrix”

  1. the feelings esxerpsions of every western journalist specialy those who working mainstream media they try to defend corporation’s mistakes which send milions western jobs to the china and bring false news every single day, they don’t need to discuse whats? wrong western economy, they must explain who create this catastrophic economic depression otherwise stop fake news, people knew what’s going on!

Comments are closed.