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

< 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:

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.

2 thoughts on “How to conduct a Principal Component Analysis in EXCEL – Diagonal Matrix”

  1. Thank you Sam for stopping by and asking a question. Hope the following answers it:

    The steps used in the PCA are as follows:
    • Obtain the historical times series rate data. At time ti we observe rate, r(ti, ?j) for j =1,…,k, where j represents the number of maturities in or dimensionality of the data.
    • Calculate the difference di,j = r(ti +1 , ?j)- r(ti, ?j)
    • Calculate the covariance between all di,j and write it in the form of a covariance matrix, ?.
    • Find a matrix P such that P?P’ is a diagonal matrix, where P’ is the inverse of the matrix P.
    • The diagonal of the matrix P?P’ is given by ?j for j =1,…,k and such that ?1 ?…??k ?0

    Matrix ? is the covariance matrix (covariance between the successive rate differences of one tenor with the successive rate differences of another tenor) and its calculation methodology is given at the following link:

    https://financetrainingcourse.com/education/2010/11/interest-rate-modelling-principal-component-analysis-pca-how-to-conduct-a-principal-component-analysis-in-excel-covariance-matrix/

    Regards

    Jawwad

Comments are closed.