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.

About the authorJawwad Farid

Jawwad Farid has been building and implementing risk models and back office systems since August 1998. Working with clients on four continents he helps bankers, board members and regulators take a market relevant approach to risk management. He is the author of Models at Work and Option Greeks Primer, both published by Palgrave Macmillan. Jawwad is a Fellow Society of Actuaries, (FSA, Schaumburg, IL), he holds an MBA from Columbia Business School and is a computer science graduate from (NUCES FAST). He is an adjunct faculty member at the SP Jain Global School of Management in Dubai and Singapore where he teaches Risk Management, Derivative Pricing and Entrepreneurship.