excell port

7/21/2019 excell port

http://slidepdf.com/reader/full/excell-port 1/27

1

FINANCE 441

- Investments -

Matthew Ringgenberg

Fall 2012

Using Excel to Calculate

Markowitz Optimized Portfolios*

*Citation: these notes were prepared using publicly available information including Bodie, Kane, andMarcus (9th edition) and lecture notes created by Eric Zivot (http://faculty.washington.edu/ezivot/).

http://faculty.washington.edu/ezivot/





2

Outline

•Markowitz Optimization• Review of matrix algebra

• Markowitz with matrix algebra



Maximizing the Sharpe Ratio

• We want to solve the following maximization

problem:

The solution to this problem is given by equation

7.13 in Bodie, Kane, and Marcus (9th ed)

3

=

. . � = 1



4

Review of Matrix Algebra: Definitions

• First, let’s define a matrix:

• And a vector :

= 2 ⋯ 2

⋮ ⋱ ⋮

⋯

=

2

⋮

Here, matrix has

a dimension of n

rows x m columns

The vector has

a dimension of n

rows x 1 column



5

Review of Matrix Algebra: Notation

• A bold capital letter is a matrix (i.e., )

• Italic capital letters refer to elements in a matrix

• (i.e., is the element in row n column m)

•

A bold lower case letter is a vector (i.e., )• Italic lower case letters refer to elements in a vector

• (i.e., is the element in row n)



6

Review of Matrix Algebra: Addition and Subtraction

• To add matrices we add each element:

• To subtract matrices we subtract each element:

• Note: for addition and subtraction, the matrices must havethe same dimension

= 4 57 2

, = 3 21 4

+ =4 + 3 5 + 2

7 + 1 2 + 4 =

7 7

8 6

=4 3 5 2

7

1 2

4

=1 3

6

2



7

Review of Matrix Algebra: Transpose and Inverse

• The transpose of a matrix (denoted by ′)interchanges the rows and columns:

•

The inverse of a matrix (denoted by-1

) is like thereciprocal of a number

• i.e., the reciprocal of 8 is 1/8

• The inverse of is −

• When you multiply any matrix by its inverse you get theidentity matrix, which is like getting 8 * 1/8 = 1

=1 2 3

4 5 6 then ′ =

1 4

2 5

3 6



8

Review of Matrix Algebra: Scalar Multiplication

• We can also multiply matrices

•

Scalar multiplication• Matrix multiplication

• Scalar multiplication is when we multiply each

element in a matrix by a number

• We can multiply matrix by the scalar, c

=4 5

7 2 = 4

c ∙ = 4 ∙ 4 5 ∙ 47 ∙ 4 2 ∙ 4

= 16 2028 8



9

Review of Matrix Algebra: Matrix Multiplication

• Matrix multiplication is when we multiply a matrix

by a matrix• It can only happen when the dimensions are

correct: the number of columns in must equal

the number of rows in

=4 57

2

2

3, =

3 2 5

1 7 8



10

Review of Matrix Algebra: Matrix Multiplication

∙ =4 57

2

2

3 ∙ 3 2 5

1 7 8

=4 ∙ 3 + 5 ∙ 1 4 ∙ 2 + 5 ∙ 7 4 ∙ 5 + 5 ∙ 87 ∙ 3 + 2 ∙ 1

2 ∙ 3 + 3 ∙ 1

7 ∙ 2 + 2 ∙ 7

2 ∙ 2 + 3 ∙ 77 ∙ 5 + 2 ∙ 8

2 ∙ 5 + 3 ∙ 8

=17 43 6023

9

28

2551

34

• We multiply the elements in the first row of by

the elements in the first column of and addthem together



11

Using Matrix Algebra to Create Optimal Portfolios

• Let’s develop a portfolio with three risky assets

• We’ll define the return vector, r, and the weightvector, w

• We can calculate the expected returns:

=

=

[] =

[]

[][] =

=



12


• We can also calculate the variance of returns:

() =cov(, ) cov(, ) cov(, )cov(, ) cov( , ) cov( , )

cov( , ) cov( , ) cov( , )

= σ2 σ σσ σ2 σσ σ σ2

= Σ




• What is the expected return on the portfolio?

• Using regular algebra we have:

= + +

•Using matrix algebra we have:

=

= [

]

∙

=

+

+

13



14


• What about the variance of the portfolio?

• Last class we saw (for 3 assets) it was:

• Using matrix algebra, it’s much cleaner:

σ2 = w′ Σw = [ ] ∙σ2 σ σσ σ

2 σ

σ σ σ2

C BC BC AC A B A B AC C B B A A p wwwwwwwww ,,,

2222222222 σ σ σ σ σ σ σ +++++=




Remember:

• We want to form a risky portfolio that either:

1. Maximizes expected return for a given level

of risk2. Or, minimizes risk for a given level of return

15




• In practice, we usually use the second

method:

• Minimize risk for a given level of return

•

In matrix notation, our optimization problem is:min σ2 = w′ Σw s.t.

= w

= ,0 and w

1 = 1

16




• To solve this problem, we use Lagrange Multipliers

• We’ll skip over the Lagrange multiplier part of thecalculation (although it’s relatively easy to do)

• Instead, we’ll go straight to the solution of the

problem (in matrix notation) and we’ll plug it into

Excel

• If we specify a target rate of return, the solution (the

optimal weight vector z) is given by:

17

zx = Ax−b0




where:

• Ax is a matrix that has 2 × the covariance matrixin the top left and adds two more rows & columns

•The 2nd to last column and row contain the expected

return for each asset ()•The last column & row

contains a 1 for each

asset and a zerootherwise

18

zx = Ax−b0

Ax =

2σ2 2σ 2σ 1

2σ 2σ2 2σ 1

2

σ2

σ2

σ

2

1

0 01 1 1 0 0




where:

• zx is a vector and the first n rows contain

the optimal weights for our n risky assets

• b

0 is vector that contains a zero for each

of the n risky assets (in this case, three

zeros for three assets) and then the

second to last row is our target return,

,0 , and the last row is a 1

19

zx = Ax−b0

zx =

∗

∗

∗45

b0 =

00

0

,0

1




• The vector zx gives us the optimal weights that

determine the most efficient portfolio, given ourtarget rate of return

• In other words, it gives us a portfolio on the frontier

• Note: if we calculate zx for different target rates of

return, we can trace out the efficient frontier

20

zx = Ax−

b0

C O f




• But, what if we want the “best” possible portfolio on

the frontier?• Remember: the optimal portfolio is tangent to the CAL

• This tangency portfolio is given by:

21

= Σ−( ∙ 1)

1′Σ−( ∙ 1)

C l l i h Effi i F i



Calculating the Efficient Frontier

Solving for frontier portfolios in Excel is easy:

1. Pick a target expected return,

,0

2. Type Ax and b0 into Excel

3. Compute the inverse of Ax (i.e., compute Ax−)

• Use the function =MINVERSE() & hit ctr+shift+enter

4. Multiply Ax− ∙ b0 to get the optimal weights, zx

•Use the function =MMULT() & hit ctr+shift+enter

See “Example Markowitz with Matrix Algebra in Excel.xlsx”The bullet numbers correspond to the steps in the Excel file

22

C l l ti th Effi i t F ti



Calculating the Efficient Frontier

• As you pick different target rates of return (,0 ) you’ll

get different portfolios on the efficient frontier

• In fact, you can trace-out the frontier by calculating zx

for many different target returns

• It turns out, you can also trace-out the frontier by

combining any two frontier portfolios

• If you calculate two frontier portfolios, you can trace-out

the rest of the frontier by creating a portfolio of these two

frontier portfolios and varying the portfolio weights

• i.e., put 100% in 1, 0% in the other, 90% / 10%, etc.

23

C l l ti th O ti l (T ) P tf li



Calculating the Optimal (Tangency) Portfolio

What if you want the best frontier portfolio?

Solving for the optimal portfolio in Excel is easy too:7. Type in Σ (the covariance matrix), a row vector of

1s, and (the expected return vector - r f )

8. Compute the inverse of Σ (i.e., compute Σ−) • Use the function =MINVERSE() & hit ctr+shift+enter

See “Example Markowitz with Matrix Algebra in Excel.xlsx

24

C l l ti th O ti l (T ) P tf li



Calculating the Optimal (Tangency) Portfolio

9. Calculate the numerator by multiplying Σ− and

the vector (

)

Use the function =MMULT() & hit ctr+shift+enter

10.Calculate the denominator by multiplying Σ−

and the vector ( ) and then take thevector of 1s (transposed) and multiply it by the

solution of Σ−( )

11.Finally, divide the numerator and thedenominator

25

E l i M t i Al b t C t O ti l P tf li



Example: using Matrix Algebra to Create Optimal Portfolios

• You can invest in 3 possible risky assets and you

calculate the expected return (μ), standard deviation

(σ) and covariance matrix (Σ)

26

Stock i μi σi

A 8.9% 0.10B 12.7% 0.16

C 5.9% 0.14

Covariance Matrix (Σ)

A B C

A 0.0100 0.0020 0.0010B 0.0020 0.0256 0.0030

C 0.0010 0.0030 0.0196

E l i M t i Al b t C t O ti l P tf li



Example: using Matrix Algebra to Create Optimal Portfolios

• What are the portfolio weights for the efficient portfolio

that has a target expected return of 9%?

W A = 56.53%

WB = 20.65%

WC = 22.82%

• What are the optimal (tangency) portfolio weights?W A = 56.38%

WB = 30.20%

WC = 13.42%• Which portfolio has the better Sharpe ratio and why?

27

excell port

Documents