Project: Derivation of the Efficient Frontier

Part 1

1. Log into WRDS (Wharton) database, using the following information:

Userid: sseechar

Pwd: Plza.123

a. Choose CRSP (Center for the Research of Security Prices) database

b. Choose North America

c. Choose Equities

d. Choose Monthly data files

2. Download data on monthly returns (choose Holding Period Return variable) for the most recent 5-year period on four companies (or mutual funds, ETFs, etc.) from CRSP databaseinto the Excel file. Choose companiesfrom unrelated industries. Use your common sense here. For example, computer hardware and software industries are closely related, but computer hardware and food industries are not. Make sure that your dates are consistent across all four stocks, i.e. you select returns for the same 5-year period.

You can download similar data from Bloomberg

3. Using commands in the Excel Functions menu (click on f_x button), calculate expected monthly returns on all three stocks (use AVERAGE function), their variances (VAR function), standard deviations (STDEV function), arrange them into variance-covariance matrix.

The more efficient way to calculate the variance-covariance matrix is using cov-matrix.xlam application, posted on BB. You need to download this application onto your computer and then open it within your Excel program. Don't forget to enable macros.

In this example, Variance-Covariance is a 4 by 4 table. Suppose that it is located in cells (C98:F101), that is rows 98 through 101, columns C through F. On the diagonal of this matrix are variance estimates, on off-diagonal are covariance terms (we will use these addresses later in mmult command).

4. Now find the correlation coefficient between each pair of companies (CORREL function).Arrange them into the 4 by 4

- Correlation matrix

5. Annualize expected returns, variances and covariances. It means you have to multiply monthlyexpected returns, variances and covariancesby 12. We can do this because we assume markets to be efficient at least in a weak form. Market efficiency means that monthly returns are independent of each other; that is the correlation coefficient between returns in one month and returns in any other month is 0. The annual standard deviation is just the square root of the annual variance. The annual correlation coefficient is the same as the monthly correlation coefficient.

You can multiply the entire variance-covariance matrix by 12. Below is the link to the excellent tutorial on matrix manipulations in excel:

http://facweb.cs.depaul.edu/mobasher/classes/csc575/assignments/MatrixOperations-Excel2007.pdf

6. Report monthly and annual statistics, calculated in (3) -- (5). Present annualized variances and covariance's in the variance-covariance.

Part 2

7. In the next fourcolumns specify weights of the fourstocks in your portfolio. Remember, that the weight of the forth stock in the portfolio can be expressed as w₄ = 1- w₁ - w₂- w₃.Alternatively, you can write a weight constraint into the next column

_i=1?⁴w_­­i=1

You can use any set of numbers as a starting point.

8. In the next threecolumns enter the formula for the variance, standard deviationand expected return (in that order) of the portfolio of four stocks:

σ²_p =(w²₁ σ²₁ + w²₂σ²₂+ w²₃σ²₃ + w²₄σ²₄ + 2w₁w₂ρ₁₂σ₁σ₂ + 2w₁w₃ρ₁₃σ₁σ₃ + 2w₁w₄ρ₁₄σ₁σ₄ + 2w₂w₃ρ₂₃σ₂σ₃ + 2w₂w₄ρ₂₄σ₂σ₄ + 2w₃w₄ρ₃₄σ₃ σ₄₎                                                                            (1)

σ_p= √σ²_p                                                                                      (2)

E[R˜_p]= w₁E[R˜₁]+ w₂E[R˜₂]+ w₃E[R˜₃]+ w₄E[R˜₄]                        (3)

The more efficient way to do calculate the variance or the portfolio and its expected return is to use matrices:

We will use the following matrix form for this equation:

σ²_p= ω Ω ω'                                                                                  (4)

In equation (4), w denotes 1 by 4 row vector of weights, W stands for 4 by 4 variance-covariance matrix, and w' is a transpose of w, a 4 by 1 column vector of weights.

a. To do this in Excel:

(i) Enter the vector (row of four) weights for the four assets, for example in B109:E109.  You can start from any set of numbers. In the next cell (F109) Enter the weight constraint, i.e. enter the formula:

=SUM (B109:E109).

(ii) Now use the following command in Excel to perform matrix multiplication to find the variance of your portfolio

=MMULT(MMULT(B109:E109,$C$98:$F$101),TRANSPOSE(B109:E109))

Press simultaneously CTRL-SHIFT and while holding these two keys, also press ENTER. Suppose that the result of your operation is located in cell G109.

9. Copy everything 10 times (next ten rows).

10. Use Solver to find the weights of the minimum variance efficient (MVE) portfolio of your four chosen stocks in the first row.To do this, you simply minimize the variance of the portfolio, no constraints on the expected return of the portfolio needed.

Steps:

a. Go to Data, find Analysis and click on Solver

b. In Solver window, in "Set Objective", enter G109 (this is where you have your variance function) and click on Min. Your objective is to minimize the variance of the portfolio.

c. In "By Changing Cells" enter the range of weights that Solver can change, i.eB109:E109.

d. Add the constraint on weights: reference cell F109 (sum of all four weights) should be equal to 1.

e. Click OK.

f. In the next cell, H109, find the annualized standard deviation of the MVE portfolio.

11. Calculate the expected return on the MVE portfolio.In matrix form this equation is:

E[R˜_p]= E ω'                                                                                 (5)

In (5) E stands for the 1 by 4 row vector of expected returns and w' is a column vector of weights.

To do this in Excel, in cell I109 write:

=MMULT($B$92:$E$92,TRANSPOSE(B109:E109))

where the first array of cells contains annualized expected returns on individual hedge fund strategies. Done forget to press simultaneously CTRL-SHIFT and while holding these two keys, also press ENTER.

12. Use Solver to calculate efficient portfolios for each level of expected returns. That is, increase expected returns above the level of E[R_MVE] in small increments (for example, the next portfolio's expected return would be 1% higher than the expected return on the MVE portfolio). This new return enters Solver as your constraint. You need to find a portfolio that has the smallest possible standard deviation and pays your chosen expected return. Mathematically, you solve the following optimization problem:

_{W1, W2, W3}Minσ²_p                                                                         (6)

subject to:

E[R_p] = A,

where A is your chosen expected return.

You will need to repeat your optimization with Solver 10 more times, each time increasing return by 1%. Don't use paste and copy at this point.

In Solver, the steps are exactly the same as in Step 10, except that in the "Subject to constraints" window, you now need to enter the return constraint. Click on "Add". The constraint window will open. In the "Cell Reference", enter I110 (in our example the expected return on the next portfolio is located in this cell) and choose =. In the "Constraint" enter =I109+0.01. This means that return on your next portfolio should be equal to the return on MVE plus 1%. Click on "OK."

13. Use Chart menu in Excel to draw the graph of the efficient frontier: expected return (on Y-axis) versus standard deviation (on X-axis). Use XY plot.

Attachment:- Attachments.rar