Power of a Matrix formula using recursive LAMBDAs

I have been experimenting with recursive LAMBDAs in Excel and have come up with this nice little formula to calculate the Power of a square (n x n) matrix.

This has interesting applications when used to model transition matrices for regular Markov chains but also it is worth having in your stack as prior to LAMBDAs, calculating the Power or a Matrix was only possible using VBA.

The steps to code this custom function are as follows:

Step 1: Press Ctrl + F3 to bring up the Name Manager and press 'New':

Step 2: Add the custom function name:

I have named this function LAMBDA_MatPower but the name could really be anything you want, subject to then customising the formula that follows in the next step.

Step 3: Add the custom function in the Refers to menu box and then press 'OK':

The full function that goes into the 'Refers to' menu box is as follows:

=LAMBDA(n,TransitionM,IF(n<1,0,IF(n=1,TransitionM,MMULT(TransitionM,LAMBDA_MatPower(n-1,TransitionM)))))

Step 4: Call the function from within Excel with its 2 arguments like below:

The first argument of the function is the matrix Power (in this case 3) and the second argument is the n x n matrix to be called, in this case our Transition Matrix {0.8, 0.1, 0.2, 0.9}.

To make this example more applied and extend it further, let us follow Example 2 in Section 5.5 of Anton's Elementary Linear Algebra (11e) p. 333 and assume our Transition Matrix above represents 2 TV Channels' market share dynamics in a zero sum game.

In this game Channel 1 retains 80% of its original market share and captures 10% of Channel 2's share, while Channel 2 retains 90% of its share and gains 20% of Channel 1's share. That is what our Transition Matrix represents. Applying the formula above for n = 3 gives us the Transition Matrix after 3 years have lapsed so it is effectively:

{0.8, 0.1, 0.2, 0.9} x {0.8, 0.1, 0.2, 0.9} x {0.8, 0.1, 0.2, 0.9}

If the initial state vector assumes a 50%/50% market share for both channels, multiplying this resulting 2x2 Transition Matrix at the end of year 3 with the initial state vector which is 2x1 or {0.5, 0.5} as follows:

Step 5: Multiplying the Transition Matrix in Year 3 with the Initial State Vector:

suggests that the likely market share for Channels 1 and 2 at the end of Year 3 will be 39.05% for Channel 1 and 60.95% for Channel 2 as Step 6 below shows:

Step 6: Resulting Market Shares - Year 3:

Of course, the formula could be extended to error trap for cases where the matrix selected is not square etc. but this is beyond the scope of the above example.

Also, I have not tested for any performance related considerations namely the impact on calculation speed and associated workbook overhead when the square matrix grows considerably in size.

Please note the above steps can only be executed in O365 or, in the future, for versions of Excel which allow for the creation of LAMBDAs.