Arrange the weights of each asset in the portfolio into a row vector:
W = www 123
Next, arrange the pair wise covariances in a square matrix:
Ω =
11 12 13
21 22 23
31 32 33
Now, multiply WΩ, to get :
ww w ww w ww w 1 11 2 21 3 31 1 12 2 22 3 32 1 13 2 23 3 33 . Now post-multiply this
result by the transpose of W which is called W’:
1
1 11 2 21 3 31 1 12 2 22 3 32 1 13 2 23 3 33 2
3
w
ww w ww w ww w w
w
to get:
1 1 11 2 21 3 31
2 1 12 2 22 3 32
3 1 13 2 23 3 33
ww w w
ww w w
ww w w
=
1 1 11 1 2 21 1 3 31
2 1 12 2 2 22 2 3 32
3 1 13 3 2 23 3 3 33
ww ww ww
ww ww ww
ww ww ww
, which can be simplified to
22 22 22
1 1 2 2 3 3 1 2 12 1 3 13 2 3 23 w w w ww ww ww 222 which is the variance of the 3-asset
portfolio. In matrix algebra terms this can be written simply as:
WΩW’
Excel can do matrix arithmetic, however, it can only multiply two matrices at a time, thus it can
either do W(ΩW’) or (WΩ)W’. The Excel function for multiplying matrices is:
MMULT(array1,array2). Following the rules for matrix algebra, the number of rows in
the array1 must equal the number of columns in array2. Thus the order of the arrays cannot be
altered. Further, since Excel does not know ahead of time that we are going to get a (1×1) array
answer (a single number or single cell) it requires us to use Ctrl-Shift-Enter when we finish
typing or editing this function. The expression (WΩ)W’ can be written in excel as:
=MMULT(MMULT(W, Ω),TRANSPOSE(W)), or since we really are not interested in
the variance, but really want the standard deviation we can simply type:
=SQRT(MMULT(MMULT(W, Ω),TRANSPOSE(W))). Likewise, The expression
(WΩ)W’ can be written in excel as:
=SQRT(MMULT(W,MMULT( Ω,TRANSPOSE(W))).
Note that since the matrix algebra does not refer to N (the number of assets) the above
expression remains the same for any number of assets. Finally, Excel can not use greek letters as
range names, so we will have to substitute a name for Ω. I usually call it VCOV which stands
for the variance-covariance matrix. So our actual Excel statement should look like:
=SQRT(MMULT(MMULT(W, VCOV),TRANSPOSE(W))).