you should have stocks from several different sectors like Consumer Staples, Consumer Discretionary, Energy, Financials, Healthcare, Industrials, Information Technology, Materials, Telecommunication, and Utilities. You can find a list of the S&P 500 stocks and their sectors at http://en.wikipedia.org/wiki/List_of_S%26P_500_companies. Consider picking some stocks that you have not heard of so you can learn about some new companies. The stocks must have at least 60 months of data and to keep things from getting messy, I suggest you make sure their mean returns are positive over the last 5 years. I would also suggest they have a positive beta but we may not be that far in the course by the time you start this assignment. We could perform our optimization on any number of securities but I think 10 will give everyone the practice needed if you want to try other quantities of stocks, bonds, or mutual funds.
- Instructions for using Yahoo Finance are in a file called “Yahoo Finance Basic Instructions” in the same area that you found this file in D2L. You should probably familiarize yourself with that file first.
- Download adjusted monthly closing prices from finance.yahoo.com.
- Go to finance.yahoo.com and download 61 adjusted monthly closing prices for each of your 10 stocks ANDthe S&P 500 (there is no symbol for the S&P 500 so Yahoo uses a fictitious symbol ^GSPC, if you were using MoneyCentral it would be $INX).
- Starting date January 1, 2008.
- Ending date January 31, 2013.
- Delete everything except the dates and adjusted closing prices and merge all stocks and the S&P data into a single file as shown in the example at the end of instruction number 5 below.
- Put the price data in alphabetical order by ticker with the S&P data at the end (after the last stock). Downloading the data alphabetically by ticker will save you some time reorganizing later.
- Leave the prices in the date order that they are downloaded (most recent first).
- See the example at the end of instruction number 5 below.
- Compute 60 monthly returns for each of the 10 stocks and the S&P 500. Put the returns in percentage format by multiplying by 100. DO NOT use the command Format | Cells | Percentage. I ask you do this so that the variances and other parameters we will be computing will not have multiple zeros at the beginning of their numbers. Be very careful when you compute the first return.If it is wrong and you copy that formula over the rest of the spreadsheet, all returns and everything after the returns will be incorrect! (You will get at most 35% on this assignment). Returns are computed as (ending price – beginning price) / beginning price for each month (times 100 to get them in percentage format). For example, you buy in April (beginning price) and sell in May (ending price). The ending price is first in the file since the most recent prices are at the top. This is a holding period return - how much you made (or lost) as a percentage of what you invested in each month. If you copy the formula all the way to the last row, you will get division by zero. Either stop before the last row or delete the division by zero warnings. The example shown below assumes you will put these computations in new columns (rather than new rows). For reasons that may become clear in this or later assignments, I believe this method will produce the least work over the course of the semester but what is most important is that the returns remain together by column (but as usual, how you set up any assignment is up to you).
- Create a summary worksheet and if necessary, move it so that it is the first worksheet listed (grab the tab and pull it to the left). Label the worksheet tab "Summary" (right click the tab and rename it). In the summary worksheet, for each of the 10 stocks and the S&P 500, compute the following:
- The arithmetic average of monthly returns – use the function AVERAGE.
- The geometric average of monthly returns. Yahoo prices are adjusted for dividends, so you can use the RATE function to find the geometric average. The RATE function defaults to percentage format which you do not want. To match the other cells, multiply the answer by 100 and use the "format cells" dialog box to get rid of the percent sign and match the number of decimals.
- The standard deviation of monthly returns- use the function STDEV.
- The highest monthly return - use the function MAX.
- The lowest monthly return - use the function MIN.
Here is an example format:
- You cannot get an “A” with low quality presentation. Presentation allows me to determine the level of your Excel skills and should be designed to make the submission easier to read than an unformatted version. Minimum requirements:
- Start with a white background. (click the button above row 1 and to the left of column A to select the entire spreadsheet and then “fill” with the color white).
- Put your group number along with your name(s) on the top line e.g. Group 9: Pavel Datsyuk, Henrik Zetterberg.
- Format the summary sheet to 2 decimal points. The numbers are already in percentage format since you multiplied the returns by 100.
- Format the prices to 2 decimal points.
- Format the returns to 4 decimal points (when I click on a return, the cell must have formula in it� not a number � the formulas make the assignment generalizable and therefore much more useful).
- Keep the prices together. Keep the returns together. Keep the stocks in alphabetical order by symbol with the S&P 500 after the stocks.
- Fix the date formatting so that it is in MMM-YY format (see the example below).
- Add some formatting/color. Please avoid dark text on a dark background (e.g. black text on a red background) or light text on a light background – it is hard to read and prints very poorly on a B&W laser printer.
- Make the negative returns stand out from the positive returns. Conditional formatting has a much broader set of options than Format Cells, so you might want to try the conditional formatting command.
- Please do not use fancy fonts, they are hard to read.
- Use the merge and center button to merge and center the main titles across all the main title cells.
- Use middle alignment to center the titles between the top and bottom of the cells.
- Spell check the worksheet (F7).
Below are screen shots of an example format. It has the wrong dates and different stocks but it will give you the idea. Note that these examples use "Comments" to show the full stock name (right click on the cell and choose "Insert Comment"). This is useful where the client is not familiar with the ticker symbols. If you decide to use comments, please delete everything from the comment except the stock name and resize the comment so it fits the stock name. Thanks. Note the use of the S&P 500's actual name not the fictitious symbol.
- Use the monthly RETURNS to compute a covariance matrix and a correlation matrix for the 10 stocks and the S&P 500. You do this by going to the Data Ribbon and selecting Data Analysis and then selecting Covariance or Correlation. The input range is the 61 (rows) by 11 (columns) matrix of monthly RETURNS including the stock tickers, the data is in columns, and the labels are in the first row. For example:
Please put the matrices on a new worksheet with the correlation matrix under the covariance matrix. Note that you will only get 1/2 the matrix since one part of the matrix is the mirror image of the other. You need to fill in the rest of the matrix by copying and pasting. If you know how to use Paste Special | Values-Transpose it will save you some time. Be careful.
Please format the matrices to five (5) decimal points. Merge and center the tiles, etc. Below is an example format. It is not necessary to replicate the example but having the diagonal clearly marked is useful since it distinguishes variances from covariances and same-stock from across-stock correlations.
- Insert a new worksheet (if one is not already available).
- Fill the spreadsheet with a white background (as in 5a above).
- Now you can begin the Markowitz set-up. On a new worksheet, put the title "E(Ri)" (expected return for each stock). Copy and paste the arithmetic average returns for each stock into the E(Ri) column. Use Paste Special | Values-Transpose so that you get the numbers, not the formulas. There are many decimal points, so use copy and paste special – do not try to retype the numbers. With a little formatting, it might look something like this:
- To the right, put the title "Transposed Weights" and below that put in the number “0.1” into 10 cells (you have 10 stocks). These are the weights we will be optimizing to find the best portfolio return for a given risk (standard deviation). Merge and center the title to match the weights.
- Below this put the title "Variance-Covariance Matrix" and copy and paste the covariance matrix you computed previously below the title. Use Paste Special | Values (like you did with the average returns) so that you get the numbers, not the formulas. There are many decimal points so use copy and paste special – do not try to retype the numbers. Merge and center the title to match the matrix. With a little formatting, it might look something like this:
- To the right of the variance-covariance matrix put the titles "Weights" and in the next column "Stocks". Reference the transposed weights (cells D3-M3 in the example above) in the cells below the "Weights" title. DO NOT put the number “0.10” into these cells. They MUSTbe cell references. For example you would set cell O5 to equal cell D3 (=D3), and cell O6 equal to cell E3 (=E3), etc. so that if you change cells D3-M3, cells O5-O14 will change also. Try it to make sure it works. Add the ticker symbols below the title "Stocks". Once you add some formatting, it could look something like this:
- To optimize the portfolio, we will need the expected return on the portfolio, E(RP), and the standard deviation of the portfolio, and the slope. To compute the slope we will need the T-bill rate. We also need to know the sum of the weights. To make things easy to copy, put the E(RP), standard deviation, T-bill and Slope together below the variance-covariance matrix. The sum of the weights belongs below the weights. With a bit of formatting, it might look something like this:
- I'll assume you know how to sum the weights, so I'll focus on the formulas for the other variables. The E(Rp) is the "dot" product of two matrices - the transposed weights and the expected returns, E(Ri). In the example above, that is cell E17. Click on the cell and then click on the function button:
You can search for the correct function.
- Select the "Math & Trig" category from the drop down box and scroll down to the MMULT function, select it and click OK.
- After you click OK, you will get another dialog box:
For Array1, select the transposed weights and for Array2, select the expected returns. Click OK:
- For the standard deviation we need the square root of the variance. The variance is the product of three matrices: the transposed weights times the variance-covariance matrix times the weights. The MMULT function does not have 3 Arrays so we will type in the correct formula. Select the cell for the standard deviation. Type "= MMULT(" [without the quotes but with the round bracket] then use your mouse to select the transposed weights. Then type a comma followed by "MMULT(" and use your mouse to select the entire variance-covariance matrix. Then type a comma and use your mouse to select the weights. Then type the closing brackets "))" [two of them]. Then hit enter. You now have the variance of the portfolio. We need the square root of that formula. One way to do this is to use the SQRT function. If you do it this way, the formula now starts like this: =SQRT(MMULT( ... and has three brackets at the end ))).
- This term we will use the monthly T-bill rate of 0.05% (pretty close to nothing). This is a monthly percentage. All of our other numbers are monthly percentages and you didn't use a "%" sign with them, so enter 0.05 as the risk free rate in the appropriate cell. DO NOT enter 0.05% or you will get the wrong number and everything that uses the risk free rate will be wrong.
- Add the formula for the slope of the CAL in the appropriate cell. Recall that the slope is: [E(Rp) - rf] / sigma(p).
- Format this entire sheet to 5 decimals.
- Once you have correctly completed the above steps, you can start the optimization. To find the maximum possible return for a given standard deviation we will use the Excel tool Solver.
- First we will find the minimum variance (standard deviation) portfolio. When you click on the Solver tool, the Solver Parameters box pops up
- In the above example, the standard deviation was cell D17. So that cell should be your objective (line 1 of the Solver parameters). You want to minimize the standard deviation so click on Min (line 2 of the Solver parameters), by changing the amount you put in each stock (put the location of your transposed weights in line 3 of the Solver parameters), subject to the constraint that the sum of the weights must be 1 - we want to spend 100% of our money but not more or less than 100% of our money (click on the Add button and add this constraint). We are not allowing short selling, so make sure the check box "Make Unconstrained Variables Non-Negative" is checked.
- Once your setup is correct, Click Solve. If everything was set up correctly, Solver will come back with:
- In the above example, the weights have changed to:
So to achieve the smallest possible standard deviation, you put about 3.8% of your money in CAT, 4.4% in CVS, 18.1% in CVX, 2.3% in HET and TAP, 0.0% in HIG and OO, 41.4% in K, 26.9 in WB, and less than 1% in WDC. Since we will be making a graph of the efficient frontier, this point will be at the bottom of the chart. Insert a new worksheet (if you don't already have one available) and copy the standard deviation and expected return for this portfolio onto the new sheet. Put it around row 11 or 12 since you will need about 10 points to get a good looking graph.
The top of the graph will be 100% in the stock with the highest return. So copy that return (cell B12 in the above example) onto the new sheet and put it around row 2. You also need its standard deviation (the square root of cell M14 in the above example). Note that the highest return does not always have the highest variance. You always pick the stock with the highest return, then pick the matching variance (and take the square root to get the standard deviation). At this point, the chart data might look something like this (you only see 5 decimals because of the formatting but all the decimals are there):
- Now you have to fill in the portfolios between the top and the bottom of the graph. To do this you can minimize the standard deviation for a given return, or maximize the return for a given standard deviation. Try both ways to convince yourself that it makes no difference. There will be three constraints:
- The first constraint is the same as before, that the sum of the weights must be equal to 1. Unless you deleted it, that constraint should still be there.
- The second constraint is the same as before, that each of the weights must be greater than or equal to zero. Unless you changed it, the check box on "Make Unconstrained Variables Non-Negative" should still be checked.
- The third constraint changes each time you make a run. If you are maximizing return for a given standard deviation, then pick a standard deviation between the lowest and highest. Sometimes your graph will look good if you pick numbers equal distant (in the above example that would be [(13.37780 - 2.61603) / 9], but in other cases you will want the points more closely spaced near the minimum point and more widely spaced near the top. There is no way to know until you plot the graph and see what it looks like. You can always add more points by inserting rows. As an example add the constraint that the standard deviation = 12.18205 and click OK:
- In this example, the parameters now look something like this (this example is maximizing return for a given standard deviation so the objective cell is the return (maximized) and one of the constraints is standard deviation):
- If everything works, Solver will find a solution. All constraints and optimality conditions will be satisfied. Click on OK. Copy the standard deviation and expected return to the chart data sheet. Then go back to Solver and change the standard deviation constraint (select the standard deviation constraint, then click Change). Click Solve. Solver finds a solution, click OK. Copy the new standard deviation and expected return to the chart data sheet. Continue doing this until you fill in all your points. Then the chart data might look something like this:
- Next we need to find the maximum possible slope - the tangency point. So go into Solver and set the correct cell as the objective. You want to maximize the slope by changing the weights. You still need the weights to add to one but you do not need any particular standard deviation - so if you still have that constraint, you can delete it. You still need unconstrained variables to be non-negative. Once you have everything set correctly, click Solve. You will need to copy and paste the standard deviation and expected return of this portfolio in two places. First, paste it below the slope equation:
then paste it on the chart data sheet:
- Now that we have the tangency portfolio, we can find the optimal portfolio for the client. First we need the client's risk aversion coefficient. In most cases a risk aversion coefficient, "A", of 10 will keep borrowing to a minimum (but as you can see that did not work with the example data I am using). Then we can use the formula from the lectures to find y*, the optimal amount to put in the tangency portfolio for this client. The remainder, 1 - y* goes into T-bills (or a money market mutual fund). We also want to compute the expected return and standard deviation of this combination. With a bit of formatting, it might look like this:
Note that cells D25 to D28 in this example (your cells could be different) are formulas. If you do not have formulas in these cells, they will be marked as incorrect.
- Now we need to plot the efficient frontier. You can insert a chart and then select the data but I prefer to select the data and then insert the chart. So go to the chart data and select all the standard deviations and expected returns (NOT the titles or the tangency data - we will add the tangency data as a separate series). Once that data is selected, click on "Insert", then on "Scatter" charts, then on Scatter with Smooth Lines and Markers.
A chart will open on the same page.
- The first thing we need to do is move the chart. Click on the outside of the chart to select it. Then in the upper right corner of the worksheet, click on Move Chart.
If you don't see the move chart button, then click on "Design" under "Chart Tools.
When the move chart dialog box opens, select "New Sheet" and type in a name other than "Chart1".
This isn't that hard but so many people have trouble with this that I will deduct 5% from your grade if you can't figure out how to put the chart on a new sheet.
- When the chart opens, it will not look good. You have to fix it.
- Make sure the chart is on a "New Sheet".
- Do not truncate either end of the graph (minimum variance at one end and maximum return at the other).
- There is no need for a legend (delete the legend).
- Do not start your graph at (0,0) - pick better numbers so the opportunity set fills the chart area (right click the axis, select "Format Axis..." and change the "Minimum" and "Maximum" from Auto to Fixed and put in appropriate numbers).
- Format the number of decimals in your source data to 5 and the number of decimals on your chart axes to 1 or 2.
- The chart should be a smooth curve. Add more points or move points to get a smooth curve.
- Change the default colors. Do not use the same color for the plot area and chart area.
- Add some labels. Label the tangency point (see below for adding the tangency point) with a text box if you are not using Excel data labels. Put a border around the text boxes. Use solid fill or shape styles to fill the text boxes with an appropriate color. Center the text both horizontally and vertically in the text boxes and the tangency label using the Format Shape or format Text Box dialog boxes or the Alignment dialog box.
- Do not get too fancy – you are trying to make the chart more readable – WordArt and fancy fonts usually make the information less readable.
- Use F7 to spell check the chart. Use F7 to spell check all your other worksheets too.
- Set the Zoom (view) on the chart at 115%. It might not fit on your screen but it fits perfectly on mine.
- Excel has some quick and dirty "one click" chart formatting. Pick your own formatting, do not use the one click stuff.
- Add the tangency point to your graph. One way to do this is to right click on the plot line and click "Select Data".
When the Select Data dialog box opens, click Add.
When the Add dialog box opens, enter the correct data for the Tangency point and select OK.
Right click on the tangency point and select Format Data Series ... to change the format of the marker for the tangency point if you so desire.
- When you are finished, the graph might look something like the example below. Note that I had to add some additional points near the minimum variance point and move some of the other points to get a good curve.
- Make it look professional.
- Rename the tabs something useful, for example:
- Save the file with your last names in the file name (e.g. Smith-Jones-Johnson.xlsx). Make sure you are saving it as an Excel Workbook file and NOT a CSV (Comma Delimited) or any other of the possible "save as" file types.
- You must submit the entire spreadsheet with all data from prices and returns to the Markowitz graph. Excel will always retain the last Solver solution you did. I will check the file for “file discrepancies” which include but are not limited to: incomplete Solver data, blank Solver, last Solver number is not the same number as in the chart data file and chart data does not match Solver output. You can do some of this yourself - open the file, go to the Markowitz Setup worksheet and start Solver - the last Solver solution should be there. If it is not, don't submit that file unless you want to lose a large chunk of points.
- Submit the file through D2L – one file per group. D2L will not accept late assignments and I do not accept submissions by e-mail. Consider getting it done early and sending it in early. Late assignments will not be accepted for any reason. Computers know when you are in a hurry, then they crash or eat your file – Murphy’s law requires this. You do not get to submit the file multiple times, make sure it is right before you submit it.
- I think I caught everything but if you have a query, shoot me an e-mail, ask in office hours, or ask in class.
- You can get an extra 5% on this assignment (your maximum score is 105 out of 100) if you correctly compute and add to the chart the solution for the optimal portfolio if there was no rf (this is different than rf = 0). Put the appropriate equation below the other computations on the set-up sheet. I won't tell you how to do this, that's what the bonus points are for. Assume the same risk aversion coefficient.
- For some interesting ideas on charting (and a lot of other stuff) in Excel, visit Chandoo.org.
|Due By (Pacific Time)
||02/19/2013 08:59 pm