How to conduct a Principal Component Analysis in EXCEL – Eigenvectors

2 mins read

In this post we continue with our construction of the Principal Component Analysis (PCA) process in EXCEL. We see how the eigenvector matrix and its inverse are defined in the worksheet.

Assign dummy values for eigenvector matrix, P

The next step in the process is to define a matrix for the eigenvectors. The matrix should have the same dimensionality as the matrix above which is dependent on the number of rate tenors selected.  In our case as 8 tenors were selected the matrix should be 8×8.

At first we will populate the matrix with dummy values for P. When all parts of the excel sheet are appropriately linked and the solver function is defined and run, Excel through solver will determine appropriate values for P.

pi , the ith row of the matrix P, is an eigenvector and represents a principal component or volatility component of the term structure. λi, determined at a later step (see” Calculate the diagonal matrix, PΣP’, to determine the eigenvalues” below) will be the eigenvalue associated with it. In our example as there are 8 rows there will be 8 principal components to the term structure.

Calculate the inverse of the eigenvector matrix, P’

We will now determine P’ which is the inverse of eigenvector matrix. The excel function MINVERSE(array) will be used in calculating the matrix where the array are the cells of the eigenvector matrix, P.  The resulting matrix is given below:

In this post we defined the calculation cells for the Eigenvector matrix and its inverse. In the next post we will see how the Diagonal Matrix of the PCA process will be 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.

3 thoughts on “How to conduct a Principal Component Analysis in EXCEL – Eigenvectors”

Comments are closed.