Finance 361
Workshop 2
1
Math Revision: Matrix Multiplication
Matrix 1 Matrix 2
Rows Columns Rows Columns
n m m o
2
Example 1
n m m o
Column 1 Column 2
1 2 1 Row 1
1 2 2 Row 2
?
3
Example 1
Step 1: Multiply Row 1 by Column 1
1 2 1 [(1x1) + (2x2)]
1 2 2 ?
4
Example 1
Step 2: Multiply Row 2 by Column 1
1 2 1 5
1 2 2 [(1x1) + (2x2)]
5
Example 1
Step 1: Multiply Row 2 by Column 1
1 2 1 5
1 2 2 5
6
Example 2
n m m o
Column 1 Column 2 Column 3
1 2 Row 1
1 2 3 ?
1 2 Row 2
1 2 Row 3
7
Example 2
Step 1: Multiply Row 1 by Column 1
1 2 (1x1)+
1 2 3 (2x1)+ ?
1 2 (3x1)
1 2
8
Example 2
Step 2: Multiply Row 1 by Column 2
1 2 (1x2)+
1 2 3 6 (2x2)+
1 2 (3x2)
1 2
9
Example 2
1 2
1 2 3 6 12
1 2
1 2
10
Math Revision: Matrix Transposing
Matrix 1
Matrix 1 (A) (A’)
Transposed
Rows Columns Rows Columns
n m m n
i.e. you switch the rows and the columns
11
Example
3 4 4 3
1 2 3 4 1 5 9
5 6 7 8 2 6 10
9 10 11 12 3 7 11
4 8 12
12
Variance Covariance Matrix (VCV)
Variance Covariance Matrix (VCV)
• A square matrix which maps out the variances of
pairwise covariances of a set of assets
Stock 1 Stock 2 Stock 3
Stock 1 1 5 6
Stock 2 5 6 8
Stock 3 6 8 11
Variance
Covariance
13
Variance Covariance Matrix (VCV)
Variance Covariance Matrix (VCV)
• A square matrix which maps out the variances of
pairwise covariances of a set of assets
Covariance between Stock 1 Stock 2 Stock 3
stock 1&2
Stock 1 1 5 6
Stock 2 5 6 8
Stock 3 6 8 11
Variance
Covariance
14
Workshop 2 Question 1
15
Workshop 2 Question 1
16
Workshop 2 Question 1
17
Workshop 2 Question 1
18
Workshop 2 Question 1
19
Workshop 2 Question 1 (a)
20
Workshop 2 Question 1 (a)
21
Workshop 2 Question 1 (a)
22
Workshop 2 Question 1 (a)
Excel Solution:
1. Excess Returns:
• Select the output range
• Type: “=Expectedreturns-r_f”
• CNTRL+SHIFT+ENTER
2. Σ-1G:
• Select the output range
• Type: “=MMULT(VCVInverse;ExcessReturns)”
• CNTRL+SHIFT+ENTER
3. SUM(Σ-1G):
• Select the output cell
• Type: “=SUM(G25:G27)”
• ENTER
4. Weights:
• Select the output range
• Type: “=G25:27/ScalingFactor”
• CNTRL+SHIFT+ENTER
23
Workshop 2 Question 1 (b)
24
Workshop 2 Question 1 (b)
25
Workshop 2 Question 1 (b)
Excel Solution
5. Transposed weight vector:
• Select the output range
• Type: “=TRANSPOSE(w)”
• CNTRL+SHIFT+ENTER
6. w'Σ
• Select the output range
• Type: “=MMULT(E33:G33;VCV)”
• CNTRL+SHIFT+ENTER
7. Portfolio Variance
• Select the output cell
• Type: “=MMULT(k;w)”
• ENTER
8. Portfolio Standard Deviation
• Select the output cell
• Type: “=L35^0.5”
• ENTER
26
Workshop 2 Question 1 (c)
27
Workshop 2 Question 1 (c)
28
Workshop 2 Question 1 (c)
Excel Solution
9. Portfolio Return
• Select the output cell
• Type: “=MMULT(E33:G33;ExpectedReturns)”
• ENTER
10. Sharpe Ratio
• Select the output cell
• Type: “=(L39-r_f)/L36”
• ENTER
29
Workshop 2 Question 1 (Extension)
30
Workshop 2 Question 2 (2015 S1 Final Q7)
31
Workshop 2 Question 2 (a)
32
Workshop 2 Question 2 (a)
33
Workshop 2 Question 2 (b)
34
Workshop 2 Question 2 (b)
𝜎𝐻2 − 𝜌𝐴𝐻 𝜎𝐴 𝜎𝐻
𝑤= 2
𝜎𝐴 + 𝜎𝐻2 − 2𝜌𝐴𝐻 𝜎𝐴 𝜎𝐻
35
