Browse By

Interest Rate Modelling: Principal Component Analysis (PCA) : How to conduct a Principal Component Analysis in Excel: Covariance Matrix

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.

Comodo SSL