Markowitz instruction 2020, map, excel PDF

Preview

Summary

Markowitz instruction...

Description

2019/20 BMAN20072 Investment Analysis Dr. Yoichi Otsubo, Alliance Manchester Business School Lecture 4: Markowitz with Excel Spread sheet exercise instruction 0. Contents 1. Example spread sheet

[P.1]

2. Data preparation: Expected Excess Returns and Covariance matrix [P.2] 3. Find the Optimal Portfolio

[P.6]

4. EXTRA exercise: Efficient Frontier and Optimal CAL

[P.9]

1. Example spread sheet a. Course content > Portfolio choice assignment materials > Markowitz example spreadsheet b. There are 4 sheets: i. monthly_data: Monthly data of price series for the five stocks, BWY, SDRY, SAFE, BVIC, SMIN and 1month LIBOR (risk free rate; 1m LIBOR is already in rate). 61 months. ii. returns: monthly returns of the five stocks and 1m LIBOR. 60 months. iii. Excess returns: monthly excess returns of five stocks. 60 months. iv. efficient frontier: there are six blocks 1. Expected Excess Returns: Expectation of one month excess returns of five companies, selected from three possible scenarios. [expected_excess_returns_scenario.xlsx] 2. Covariance Matrix: Covariance matrix of the five stocks 3. Adjusted Covariance Matrix: Covariance matrix adjusted for degree of freedom 4. Efficient frontier: One global minimum portfolio, seven minimum variance portfolios for given risk premium, and one optimal portfolio. 5. Graph: Plots the efficient frontier and optimum Capital Allocation Line (CAL). 6. Solver parameters: Parameters to define optimisation problem and restrictions on Solver. There are three settings: a. Find Min. Variance Portfolio b. Efficient Frontier c. Find Optimal Portfolio

1

2. Data preparation: Expected Excess Returns and Covariance matrix a. Load the Solver Add-in and open a new excel file. b. Download monthly data from Blackboard > Course content > Portfolio Choice Assignment Material > Monthly Historical Data 2015-2019 c. In the first sheet of your excel file, copy and paste the monthly price series of five stocks you picked and 1month LIBOR (1m LIBOR is already in rate). 61 months. Name the sheet [monthly_data]. Here I use BWY, SDRY, SAFE, BVIC, SMIN. d. In the second sheet, calculate the monthly returns as in the example file. Name the sheet [returns]. e. In the third sheet, calculate the monthly excess returns as in the example file. Name the sheet [excess returns]. f. Open a fourth sheet; name it [efficient frontier]. Select the Expected excess returns from [expected_excess_returns_scenario.xlsx]. Here BWY and SDRY are expected “Good” returns; SAFE and BVIC are “Very good”; SMIN is “Outstanding”.

g. Use Covariance function to calculate the covariance matrix. Data > Data analysis > Covariance.

2

Input Range: ‘excess returns’!$B$1:$F$61

Output Range: $B$9:$G$14

The function only provides the lower triangle of the matrix. Fill in the upper triangle. Covariance matrix is a “symmetric” matrix, i.e., elements in the 1st row = elements in the 1st column, 2nd row = 2nd column,… etc.

3

h. As the Excel’s Covariance function does not adjust for degree of freedom, you need to do it by yourself. Multiply all the elements of the Covariance Matrix by 60/59. (Note: 60 is number of returns you have in your data (61 months of price. Hence degree of freedom = 60 -1 = 59. In general, N/N-1 must be mutiplied, where N is number of returns.)

Give equal weights of 0.2 to each stock (does not have to be equal weight; they are used as initial values of optimization procedure).

Formula in cell C18: = A20, … , Formula in cell G18: = A24.

Complete the block: 1. Formula in cell A25: =SUM(A20:A24). 2. The last row of bordered covariance matrix, Cell C25:G25. Formula in cell C25: =C18*SUMPRODUCT($A$20:$A$24,C20:C24) … Formula in cell G25: =G18*SUMPRODUCT($A$20:$A$24,G20:G24)

4

3. Mean (Risk premium) of portfolio with given weights, Cell A26. Formula in cell A26: =SUMPRODUCT($A$20:$A$24,$B$2:$B$6) 4. Standard deviation (Risk) of portfolio with given weights, Cell A27. Formula in A27: =SUM(C25:G25)^0.5 5. Slope (Sharpe ratio) of portfolio with given weights, Cell A28. Formula in cell A28: =A26/A27

i.

Copy and paste the block [6. Solver parameters] from the example spread sheet to your spread sheet. You will use these to give restrictions and objective function to Excel Solver function in the next step.

5

3. Find the Optimal Portfolio a. Find the optimum portfolio that maximizes the slope (Sharpe ratio) of CAL. Data > Solver

Click Load/Save button. (screen shot below is the one already with the loaded setup. Should be empty or show previously used setup).

Load setting for [c. Find Optimal Portfolio] from the block [6. Solver parameters].

6

You should see the below setting: It maximizes the number in cell A25, i.e. slope of the portfolio, by changing the weights, subject to the constraints of 1. sum of the weights equals to one; 2.each weight should not be smaller than 0.05 (constraint for our assignment). Click Solve.

Click OK.

7

You will see the results in the Block 3.

b. The optimal weights are not necessarily in the multiple of 0.05 as required in the assignment; so you need to adjust. Make sure the weights sum up to one. For instance, given these weights, I would adjust to BWY 0.05; SDRY 0.05; SAFE 0.35; BVIC 0.05; SMIN 0.50.

8

4. EXTRA exercise: Efficient Frontier and Optimal CAL a. Find the Global Minimum Variance Portfolio, G. Open Solver and load optimization setting to find global minimum variance portfolio. Select the setup for [a. Find Min Variance Portfolio] in the block [6. Solver Parameters].

You should then see the below setting: It minimize the number in cell A27, i.e. standard deviation of the portfolio, by changing the weights, subject to the constraints of 1. sum of the weights equals to one; 2.each weight should not be smaller than 0.05 (constraint for our assignment).

You will see the results in block 3. Adjusted Covariance Matrix 9

Save it in the block 4. Efficient Frontier.

Note that the results for Optimum portfolio are saved here as well. b. Use the expected excess return of the global minimum variance portfolio, 0.0490, as the starting point to draw the efficient frontier. For given expected excess return, find a portfolio that minimize its variance (standard deviation). Here the given risk premiums are 0.0502, 0.0513, 0.0524, 0.0535, 0.0547, 0.0558 (optimum), 0.0569, 0.0580.

Give the value of expected excess return in cell L2. Formula in cell L2: =L5

10

Open solver. Load the setting for [b. Efficient frontier].

You should see the below setting: It minimizes the number in cell A27, i.e. standard deviation of the portfolio, by changing the weights, subject to the constraints of 1. sum of the weights equals to one; 2.each weight should not be smaller than 0.05 (constraint for our assignment); 3. Expected excess return is fixed to the value given in the cell L2.

You will see the result in Block 3. 11

Save the result in Block 4.

Repeat it for other risk premiums to complete the block.

Calculate risk premium for optimal CAL. Its slope is the slope of the optimal portfolio, 1.5424. Give a starting point of standard deviation, 0.0320, in this case. Then for each standard deviation, calculate the risk premium of optimal CAL in cells J13:S13. The formula in J13: =$Q$7*J6, …, formula in S13: = =$Q$7*S6.

12

c. Prepare the inputs needed to draw efficient frontier and optimal CAL from Block 4: standard deviations (sd); risk premium (mean) of portfolios on efficient frontier; risk premium (CAL) of portfolios on optimal CAL.

Select I17:K27, then Insert > Scatter > Scatter with smooth lines and markers

With a bit of editing and formatting, you can draw a plot like the one below.

13...