To make the calculations of a covariance matrix easier we use below custom array function that will loop through each data column and calculate pair wise covariance using excels built in COVAR function We choose to use a covariance matrix in this example. To run PCA on the data we need to generate a correlation or covariance matrix. We would like to reduce the dimension to as few factors as possible that describe the variability in the data. We start out with daily changes in US swap rates for abovementioned tenors. Let’s walk through an example to gain a better understanding. The orthogonal factors are computed from the correlation or covariance matrix of the original (sometimes standardized) data. There are as many principal components as there are variables in the original data set but they are ordered in such a way that only a few factors explain most of the original data. PCA finds a set of standardized linear combinations where each individual factor is orthogonal (meaning not correlated). Linear combinations where the sum of squared coefficients equal to 1 are called a standardized linear combinations. Our vector of coefficients C= is called a linear combination. Having set the goal of reducing dimension of our data set to a smaller number of factors a simple choice would be to use the average. This is often called a reduction in the data set’s dimension. With so many variables it may be easier to consider a smaller number of combinations of this original data rather than consider the full data set.

Our data set has nine variables in total. We consider changes in 2y, 3y, 4y, 5y, 7y, 10y, 15y, 20y, 30y swap tenors. For example, we may have a time series of daily changes in interest rate swap rates for the past year. The idea of PCA is to find a set of linear combinations of variables that describe most of the variation in the entire data set. Alternatively the reader can download this excellent addin for free from. This book comes with a free excel addin Matrix.xla that can be used to implement PCA in excel. One book which we really like is Carol Alexander’s Market Risk Analysis Volume 1. For those who are interested to know the mathematics behind this technique we recommend any multivariate statics book. In practice it is less important to know the computations behind PCA than it is to understand the intuition behind the results. In the current post we give a brief explanation of the technique and its implementation in excel. We decided to write a series of posts on a very useful statistical technique called Principal Component Analysis (PCA).

How to use mmult in excel with macros