Browse By

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

We proceed with our construction of the Principal Component Analysis (PCA) process in EXCEL by defining the Diagonal Matrix in the worksheet.

Calculate the diagonal matrix, P?P’, to determine the eigenvalues

A diagonal matrix is a matrix where all cells equal zero with the exception of the diagonal. The diagonal of the matrix P?P’ represents the eigenvalues associated with the PCA denoted by ?j for j =1,…,k where ?1 ?…??k ?0.

As the P values are dummy values, the resulting matrix P?P’ may not initially satisfy these conditions of a diagonal matrix. The idea at this stage is to define the formulas, which link the various parts of the excel sheet. After the solver function is set and run, the matrix should satisfy the conditions of a diagonal matrix and the constraints of a PCA analysis as given by the ?s.

The calculation of the matrix P?P’ is carried out in two stages:

  • In the first stage we will carry out the multiplication of the matrix P and the matrix ? to determine the matrix P?
  • In the second stage we will carry out the multiplication of the matrix  P? with the matrix P’ to determine the matrix P?P’

We will use Excel’s MMULT (array1, array2) function to carry out the multiplication of the matrices. In our example the resulting matrices are given below:

In this post we saw how the calculation cells for the Diagonal matrix was defined in the EXCEL worksheet. In the next post we will tie all the elements of the construction process together through the use of EXCEL’s Solver Function.

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.