Fincance 2

Summer 2021

FIN 430 Research Project Segment 2: The Efficient Frontier and Mean-variance Optimization

Objectives:

This segment requires you to identify the efficient frontier from all the investment opportunities that can be obtained by investing in the stocks you have selected in Segment 1. The efficient frontier needs to be estimated and shown in plots using Excel. You are also expected to construct the optimal portfolio – finding the portfolio weights for each stock based on the mean- variance optimization rule.

To-do list

1. Estimate the covariance matrix of returns of all your stocks for the past 5 years.  It is VERY important to make sure the covariance matrix is correctly estimated,

otherwise you will encounter problems in the later steps.  Estimate the sample covariance (=covariance.s) instead of population covariance

(=covariance.p).  See Appendix 1 for an example.

2. Estimate the efficient frontier of all your stocks, with and without short-selling restriction.  Plot both efficient frontiers in Excel.  See Appendix 2 for step-by-step instruction.

3. Identify the portfolio weights that meet the mean-variance optimization rule.  Document the portfolio weights.  Graphically show the portfolio allocation (without short-selling) using Pie Chart

and allocation with short-selling using Bar Chart.  See Appendix 3 for step-by-step instruction.

4. Each student submits one report.  Present the efficient frontiers and the optimal portfolio of your selected stocks.  Discussion of the impact of short-selling restriction on efficient frontier.  Discussion of the optimal portfolio weights, for instance, why Stock A has a higher

weight than Stock B, and why Stock C has a zero weight, etc.  See Appendix 4 for report outline.

Summer 2021

Appendix 1 An Example of Covariance Matrix.

Assume, for three stocks, A, B, and C, the monthly returns are stored on Excel as A1:A60, B1:B60, C1:C60, respectively. The covariance matrix can be estimated as follows:

Stock A Stock B Stock C Stock A =covariance.s(A1:A60,A1:A60) =covariance.s(A1:A60,B1:B60) =covariance.s(A1:A60,C1:C60) Stock B =covariance.s(B1:B60,A1:A60) =covariance.s(B1:B60,B1:B60) =covariance.s(B1:B60,C1:C60) Stock C =covariance.s(C1:C60,A1:A60) =covariance.s(C1:C60,B1:B60) =covariance.s(C1:C60,C1:C60)

Note: make sure that the covariance matrix is symmetric – the elements marked with the same color should be identical.

Appendix 2 Estimating Efficient Frontiers.

1. Identify the set of feasible portfolio returns. First, find the lowest (e.g., -10%) and the highest (e.g., 15%) historical average returns from all the selected stocks. Set the lower boundary of feasible portfolio return as the lowest return and the upper boundary as the highest return. Next, interpolate the range defined by the lower boundary and the upper boundary with 10 numbers which are the feasible portfolio returns. Ideally, these 10 numbers should be evenly spaced.

2. Create a table on Excel like the example shown below: Row\Col A B C D E F G H

1 Weights 0.17 0.17 0.17 0.17 0.17 0.17 2 1 FB M JNJ GOOG BA KO 3 0.15 FB 0.0040 0.0001 0.0000 0.0020 0.0009 0.0000 4 0.15 M 0.0001 0.0093 0.0000 -0.0002 0.0014 -0.0001 5 0.15 JNJ 0.0000 0.0000 0.0016 0.0005 0.0006 0.0010 6 0.15 GOOG 0.0020 -0.0002 0.0005 0.0033 0.0013 0.0007 7 0.15 BA 0.0009 0.0014 0.0006 0.0013 0.0046 0.0009 8 0.10 KO 0.0000 -0.0001 0.0010 0.0007 0.0009 0.0016 9 0.0005 0.0000 0.0003 0.0000 0.0005 0.0000

Note: (a) The red area is the covariance matrix. (b) The blue area indicates the stocks of your choice. (c) The yellow area is the initially set portfolio weights. You can preset the portfolio

weights with any value as long as the sum of the weights is equal to 1.To assure that, you can insert “=sum(A3:A8)” in A2 and check if A2 always has the value of 1.

(d) Do not simply copy and past A3:A8 to C1:H1. Use the formula instead. For instance, type in “=A3” in C1, “=A4” in D1, etc. By doing this, when you (or the Excel Solver) change the values in A3:A8, the values in C1:H1 will be changed as well.

(e) The green area is for calculating the portfolio standard deviation in the next step. C9 = sumproduct(A3:A8, C3:C8)*C1, D9 = sumproduct(A3:A8, D3:D8)*D1,…, H9 = sumproduct(A3:A8, H3:H8)*H1.

3. Calculate the Standard deviation of your portfolio with the initial portfolio weights. The standard deviation = sqrt(sum(C9:H9)). Assume, you store the value (formula) of the standard deviation at J1.

Summer 2021

4. Calculate the expected return of the portfolio which is the weighted average of returns of all the selected stocks. Assume, you store the value (formula) of the expected portfolio return at M13.

5. On Excel, select Data>Analysis>Solver. If you do not see Solver, you need to change in the option setting and add this add-in feature. You can get some direction here (https://support.microsoft.com/en-us/office/load-the-solver-add-in-in-excel-612926fc- d53b-46b4-872c-e24772f078ca).

6. Set up the Solver as follows:

Note: (a) You need to repeat the “Solve” process for each of the feasible portfolio returns you

create in step 1. And each time, you should be able to obtain a set of portfolio weights such that the expected return is equal to the feasible portfolio return you choose and the standard deviation is the smallest you can ever get.

(b) Record the Solve results in a table like below:

Portfolio Weights Portfolio Expected

Return Standard Deviation

Stock 1 Stock 2 … Stock n

Highest return #1 #2 … Lowest return #12

7. Plot the target portfolio returns (Y-axis) against the portfolio’s standard deviation (on the X-axis). You should use a scatter graph with curved line connecting the dots.

In Subject to the Constraints box, we input three constraints: first, the weights of stocks should add up to 100% (In the example above, A2 = sum(A3:A8), so you need to set $A$2=1). Second, if we do not allow short selling, the portfolio weights should not be negative ($A$3:$A$8>=0). Third, we want the expected return to be the target expected return ($M$13 = $V$4, V4 is one of the feasible portfolio returns you created in step 1). Later you will drop the second constraint to allow short selling to get another set of efficient frontier points.

Once all of these conditions are coded into Solver’s input window, press the Solve button. The initial equal weights will be changed and the set of weights satisfying the target expected return appears. You copy and paste the set of weights and the corresponding return/standard deviation pair to a row in this excel sheet.

Summer 2021

Appendix 3 Mean-variance Optimization.

1. Calculate the Sharpe ratio of your portfolio.

𝑆𝑆ℎ𝑎𝑎𝑎𝑎𝑎𝑎𝑎𝑎 𝑅𝑅𝑎𝑎𝑅𝑅𝑅𝑅𝑅𝑅 = 𝐸𝐸�𝑅𝑅𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝� − 𝑅𝑅𝑝𝑝𝑝𝑝𝑟𝑟𝑟𝑟−𝑝𝑝𝑝𝑝𝑓𝑓𝑓𝑓

𝜎𝜎𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝𝑝

Risk-free rate can be estimated using the historical average of 3-month T-bill rate. 2. Use Excel Solver to maximize the Sharpe ratio.

a. Similar to the set up in Appendix 2 step 6, but change the “Objective” to your Sharpe ratio and change the “To:” from Min to Max.

b. Remove the third constraint which forces the portfolio return to be equal to the target portfolio return.

c. You can try to keep or remove the second constraint (short-selling) and see which generates better results.

Appendix 4 Recommended Report Outline and Rubric.

Part 1. Efficient Frontiers

• Present the efficient frontiers with and without short-selling constraint in the same plot. • Discuss the differences between two frontiers and explain the reasons.

Part 2. Optimal Portfolio Weights

• Present the optimal portfolio weights using Pie chart (no short selling) and Bar chart (with short selling).

• Mark the return-standard deviation pair of the optimal portfolio on the efficient frontiers. • Discuss the optimal portfolio weights.

o Why is it optimal to short sell some stocks? o Why is it optimal to have zero weight on some stocks while long the other stocks

that are in the same industry? o Are the portfolio weights consistent with your prediction based on company

fundamentals summarized in Segment 1?

Rubric: Part 1 is worth 12 pts. 5 pts for the efficient frontier with short-selling and 5 pts for the frontier without short-selling. 2 pts for discussion. Part 2 is worth 8 pts. 3 pts for the optimal weights and 3 pts for the CAL. 2 pts for the discussion.