EFFICIENT FRONTIER

By now, you should all know that investing in a single stock is not efficient, no matter how bullish you happen to be on one name. You can always achieve a better risk/return trade-off by investing in multiple stocks, truth of which will become clear after you complete this exercise.

A portfolio is defined by a set of weights; hence the portfolio problem can be posed as finding the set of weights that minimizes the variance of a portfolio for a given level of return. This turns out to be a well-known optimization problem that can be solved algorithmically (details are beyond the scope of this course). We will use Excels built-in Solver tool to do the optimization and not worry about what goes on in the background.

There are three parts to the task: first, prepare the inputs to be fed into Solver, second, run Solver to find the optimal weights, third, save the outputs from Solver and plot them on the Mean-Variance axes.

Part 1: Preparing the inputs

1. Pick any 10 stocks that trade on Borsa Istanbul. These may be stocks that you currently hold, or are considering as potential investments, or simply household brands you are familiar with (or even be random!). It is good idea to pick stocks from different industries so that they do not all move in the same direction. Do NOT pick the same stocks that your friends have picked I will view that as plagiarism. There are more than 500 public companies in Turkey, hence the probability of any two groups picking the exact same 10 stocks by chance is practically zero. There can naturally be some overlap in the selections. Also, do not pick stocks that have recently IPOed. You wont have enough data.

2.Download the monthly returns for these stocks over the last 5 years (60 months), using adjusted closing prices. Take the last day of the prior month as the starting point (in this case that would be October) and go back from there (do not use the date that you happen to be doing the assignment on). Returns should be in local currency, Turkish Liras, and include dividends (adjusted as they are called). You can access this data through Rasyonets EquityRT platform. Our library has a subscription to this data provider, and I will email you the link, along with the password. I go over the interface and where to find the relevant data in the lecture (if you missed it, your bad).

3.Collect all the individual stock returns in one spreadsheet (one column for each stock). Calculate the means and standard deviations using Excel functions. Leave the formulas as formulas otherwise I have no way of knowing how youve got your results. Remember that these statistics are based on monthly numbers, thus they should be annualized. Multiply the means by 12, and the standard deviations by the square root of 12. Next, estimate the correlation matrix of the 10 stock return series. The easiest way to do this is to use the Data Analysis Add-In in Excel. You should see its icon on the right side of the ribbon bar under the Data tab. Click that and select Correlation from the pop-up window. Then specify the range of the data and hit OK.

Part 2: Markowitz Optimization

1. Now that you have all the inputs (means, standard deviations, and the correlation matrix), you can use the Excel file I used in class as a template to solve the portfolio problem. If you understand the formulas in that spreadsheet, it should not be too difficult to extend it to any number of securities. Set up the bordered covariance matrix and calculate the portfolio variance and the portfolio mean. Start with the equal-weighted portfolio which will make it easier to double-check your formulas. Once you are happy with it, copy/paste the output somewhere else because you will be playing around with the weights.

2. Run Solver, minimizing the portfolio variance by changing the cells that house the weights. The output is the global minimum variance portfolio. Save the mean and standard deviation of this portfolio along with the weights.

3. Go to Solver again, this time minimizing the portfolio variance for a range of expected returns (add this as a constraint). There are no hard rules about what that range should be; use common sense. You could start by using the mean return of the global minimum variance portfolio and increment by 1-2 (5?) percent until you get to the maximum expected return of the individual stocks. In the Solver dialog box, make sure the Make Unconstrained Variables Non-negative option is checked. This option is equivalent to short-sales constraints (only positive weights are allowed). Save your output in a table after each iteration.

Part 3: Plotting the Efficient Frontier

1. Now we turn that table into a chart. Use the Excel Scatter Plot with lines and markers to construct the Efficient Frontier. Standard deviation goes on the X-axis and mean goes on the Y-axis. Add the individual stock means and standard deviations onto the same graph. If you did everything right, individual stocks should plot below the efficient frontier.

2.One more thing: Take any one of the 10 stocks you started with (did you have a favorite?) and find the portfolio that gives you the same average return. This portfolio should have lower standard deviation than the stock itself. Calculate the Sharpe ratio of the stock and this portfolio, taking the risk-free rate as 20%.