Monday, May 01, 2006

MMULT Formulas in Excel

I vaguely remember solving simultaneous equations back in high school.
Boring Algebra maybe, but Excel can handle it.

I recently came across a spreadsheet that had a 29 by 29 array that need to be solved as a simultaneous equation.

I did some googling and found a bunch of smarter people than I had written excellent methodologies and examples on how to use Excel in this instance.

Kardi Teknomo's Page
Professor Stanley David Gedzelman - City College of New York
dslimited
Duncan Williamson


Bob from xldynamic.com was also of great assistance writing this fantastic explanation for me. Unfortunately I cannot include the spreadsheet for confidentiality reasons.

This looks like formulae for calculating the volatility of a portfolio on N risky assets. To calculate this, there is a standard formula, SQRT(WT.V.W), which is a matrix formula for calculating such volatility. This can be converted to an Excel formula using matrix multiplication, MMULT, and TRANSPOSE.

In this formula, W is the relative weights of each asset in the portfolio (to the overall portfolio value). WT is the transpose of the weights, and V is the variance of excess returns for each asset.

The sheet WT in the attached spreadsheet shows the matrix result of the TRANSPOSE function (=TRANSPOSE(E7:E35), which creates a 1x29 array.

The sheet V shows the calculation of the variance (=TRANSPOSE(sdsrp2)*corrrp2*sdsrp2), which creates a 29x29 array. (note sdsrp2 and corrrp2 are named ranges in the example spreadsheet)

WT.V shows the result of the inner MMULT (=MMULT(TRANSPOSE(E7:E35),TRANSPOSE(sdsrp2)*corrrp2*sdsrp2)). Because WT is a 1x29 array, and V is a 29x29 array, WT.V results in a 1x29 array (see MMULT help for an explanation as to why).

WT.V.T shows the final calculations (=MMULT(MMULT(TRANSPOSE(E7:E35),TRANSPOSE(sdsrp2)*corrrp2*sdsrp2),E7:E35)). Because WT.V is a 1x29 array and W is a 29x1 array, we get a 1x1 array.


Posted by: Jethro | 2:07 PM |