In Excel, one can multiple a matrix by itself (using the MMULT function), and hence raise a matrix to any power by repeated calculations. However, there is no function to calculate partial powers (e.g. square roots) of matrices. In this blog, we describe one useful way to do so.
A power of a matrix may be required in credit calculations (e.g. for a matrix of one-year transition probabilities, what is the 18-month transition matrix), or when working out a Cholesky decomposition for created correlated random numbers and so on.
In order to calculate such partial powers in Excel, a neat trick is to use the Taylor series expansions for the (natural) logarithm and the exponential function (a google search will reveal the precise expansions, which is not covered here); these expressions apply as much to matrices as to number inputs, and involve only integer powers of the matrix. Such expansions can be implemented directly in Excel, or better, with a VBA user-defined function (this keeps the Excel sheet small, allows the size of the matrix to be changed more easily, and allows one to use more terms of the expansion (i.e. the number of terms will index a VBA loop) in order to increase accuracy where required.
The x-th power of a matrix, M, can then be calculated by taking the logarithm of M, multiplying all terms by the scalar x, and taking the exponential of this product.