[go: up one dir, main page]
Scribd
Upload
36 views2 pages

Matrix in Excel

This document discusses using matrix algebra in Excel to calculate portfolio risk and return. It provides examples of how to use the MMULT function to perform matrix multiplication needed to calculate the expected return of a two-asset portfolio and the variance of a portfolio. The portfolio expected return example multiplies a returns matrix by a weights vector. The portfolio variance example shows multiplying a weights row vector by the covariance matrix and then the resulting row vector by the weights column vector.

Uploaded by

Navin Kumar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
36 views2 pages

Matrix in Excel

This document discusses using matrix algebra in Excel to calculate portfolio risk and return. It provides examples of how to use the MMULT function to perform matrix multiplication needed to calculate the expected return of a two-asset portfolio and the variance of a portfolio. The portfolio expected return example multiplies a returns matrix by a weights vector. The portfolio variance example shows multiplying a weights row vector by the covariance matrix and then the resulting row vector by the weights column vector.

Uploaded by

Navin Kumar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Matrix Algebra in Excel

M Kannadhasan,
Professor of Finance, IIM Raipur

Excel has several built‐in array formulas that can perform basic matrix algebra
operations, which can be used to calculate risk and return of multiple assets. While an
array function in Excel, you must use the key stoke combination of: <CTRL>‐<SHIFT>‐
<ENTER> (hold down all three keys at once then release).

Let us calculate the expected return of a two-asset portfolio using matrix multiplication

| R1 R2| × | w1 |
| w2 |
= W1R1 + W2R2

The MMULT term in the Excel sheet below performs the multiplication, of the row
matrix (array) of RETURN by the WEIGHT matrix.

=MMULT (A1:B1, B2:C2)

Let us move on to calculation of variance of a portfolio

| w1 w2| × | σ11 σ12 | × | w1 |


|σ21σ22 | | w2
Step 1: we have the row matrix of weights multiplied by the square covariance matrix
multiplied by the column matrix of weights If we perform the first multiplication

|w1 w2| × |σ11 σ12 |

| σ21 σ22 |

we will get a row matrix (vector) with two elements. The first element will be
w1σ11+w2σ21 and the second element will be w1σ12+w2σ22. This row matrix is

| w1σ11+w2σ21 w1σ12+w2σ22|

Our complete matrix multiplication

| w1 w2| × | σ11 σ12 | × | w1 |


|σ21σ22 | |w2 |

Therefore it becomes, STEP-2


| w1σ11+w2σ21 w1σ12+w2σ22| | w1 |

|w2 |

= w1σ11w1 + w2σ21w1 + w1σ12w2 + w2σ22w2

The expression we had before from the direct expansion of the summation was

w1w1σ11 + w1w2σ12 + w2w1σ21 + w2w2σ22

and these two are the same.

The MMULT term in the Excel sheet below performs the first multiplication, of the
row matrix (array) of weights by the square covariance matrix.

Row Vector Weight Matrix Colum Vector Weight Matrix

=MMULT(MMULT(TRANSPOSE(A1:A3), D1:C3), A1:A3)

Covariance Matrix

You might also like