1 min read

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 λ.

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:

How to conduct a Principal Component Analysis in EXCEL -  Diagonal Matrix

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.