Instructions for creating an Efficient Portfolio
1. Get the monthly data on the prices of stocks you want to include from google or yahoo finance.
a. Go to yahoo or google finance.
b. Enter the name or ticker for the stocks you want to include in your portfolio.
c. Select “Historical Prices” from the side bar.
d. Select the date range for the prices, we usually take the last 5 years of data, but whatever you choose, make sure it is the same for all of your stocks.
e. Make sure you check the “Monthly” data before you click “Get Prices.” All of our formulas in this sheet are based on a monthly return.
f. Scroll to the bottom and download the data to an Excel sheet.
g. All you need is the “Adjusted Return” column. This adjusts for stock splits and dividends
2. Organize data in one workbook in Excel such that the dates are in the 1st column and the prices of stocks are next to it (see the picture below).
3. Sort the dates from oldest to newest. This makes the next step a lot easier.
4. Calculate the monthly return for each stock.
a. Use the formula =LN(T1/T0) where T1=the older price & T0=the newer price.
i. Ex. To calculate rate of return for Feb = LN(Price in Jan/Price in Feb)
5. Find the Mean and Standard Deviation of the returns for each stock.
a. For the mean use the formula =AVERAGE()
b. For the Standard Deviation use the formula =STDEVP()
6. Find the excess return for each month by deducting the stock’s mean from each monthly return. Move the headers down one row so there is no space between the header and the excess return data.
7. Now create the variance/co-variance matrix for the stocks.
a. Use the Data Analysis Tool in Excel to calculate the variance/co-variance matrix.
b. Click on “Data Analysis” (in Excel 2007 on “Data” tab).
c. Select Covariance.
d. Select entire range Excess Returns (including the headers) as the “Input Range”.
e. “Grouped by” should be on “Columns”.
f. Click the “Labels in First Row” box.
g. Select an output option depending on where you want the covariance matrix.
h. The resulting matrix is only “half a matrix “(i.e., all cells above the diagonal are empty) so you have to complete the matrix (it is a symmetric matrix). You can either do that manually or use the following little Excel trick:
i. Copy the “half matrix” but do not include the column names and row names.
ii. Paste the “half matrix” in an empty section of the sheet.
iii. Right click and “Paste Special” and select “Skip blanks” and “Transpose”.
iv. Copy this new “full matrix” and paste it into the original “half matrix”.
8. Create an equal weighted portfolio.
9. Calculate the Mean, Variance, and Standard Deviation of the portfolio.
a. For the Mean, use the formula =MMULT(Means,Weights)
i. The Means are at the top of the sheet from Step 5.
ii. The Weights are the percentages of each stock in the equal weighted portfolio you just created
iii. You need to press CTRL+SHIFT+ENTER to make the formula work
b. For the Variance use the formula =MMULT(MMULT(TRANSPOSE(weights),variance/co-variance matrix),weights)
i. You need to press CTRL+SHIFT+ENTER to make the formula work
c. For the Standard Deviation, use the formula =SQRT(variance)
10. Now maximize the weights using solver so that we get the best return (mean) for the risk (Standard Deviation).
a. Go to the data tab and select solver.
b. Set the target cell as the Mean.
c. Equal to the Max.
d. By changing cells: the weights in your portfolio
e. Add a constraint that the sum of the portfolio weights =1
f. Set the Standard Deviation to the level of risk you want to take on (10% in the example).
g. Sometimes Solver will be unable to find a solution. Adjusting the risk level will solve this problem.
11. In order to create the graph we will need a second efficient portfolio.
a. Copy the first efficient portfolio and “paste special values” to place it in a new location on the spreadsheet.
b. Select a different standard deviation and repeat the solver process.
c. Copy the second efficient portfolio and “paste special values” to place it in a new location on the spreadsheet.
12. Combine the two portfolios by investing X amount in EFP1 and 1-X amount in EFP2.
a. Set up the two portfolios and the weight of each (the weight of the second portfolio is 1-the first portfolio).
b. Calculate the Mean by using the formula =weight of EFP1*mean of EFP1 + weight of EFP2 + mean of EFP2
c. Calculate the Covariance using the formula =MMULT(MMULT(TRANSPOSE(weights in EFP1),matrix),weights in EFP2)
i. Matrix is the original matrix from step 7
ii. You need to press CTRL+SHIFT+ENTER to make the formula work
d. Create a second 2X2 variance/co-variance matrix
i. The top left is the variance from EFP1
ii. The top right is the covariance you just calculated in step c.
iii. The bottom left is the covariance you just calculated in step c.
iv. The bottom right is the variance from EFP2
e. Calculate the Variance by using the formula =MMULT(MMULT(TRANSPOSE(combined weights),2X2 matrix),combined weights)
i. The combined weights are the weights in EFP1 and EFP2
ii. You need to press CTRL+SHIFT+ENTER to make the formula work
13. Graph the efficient frontier.
a. Create a data table with the Standard Deviation and the Mean linked to the combined portfolios.
b. Use values from -5 to 5
c. Select all of the table, go to Data Tools and select What-if-Analysis and select Data Table
d. In the Column Input Cell select the weight of EFP1 from step 12.
e. This will fill in the table with the SD and Mean for all of the weights listed.
f. Select the inside section of the data table and insert a scatter graph.
g. You will need to format the graph a little, but this is basically a visual representation of how much return you can expect for any given level of risk you want to take on. (Remember that these returns and risk levels are monthly)
14. Pick a portfolio
a. Now because we used anywhere from +500% to -500% in each of the efficient portfolios to create this graph, you can’t actually make a portfolio out of every one of these options.
b. For the portfolio competition I would like everyone to pick a portfolio with a 10% risk level.
c. So really you could have stopped at step 10 and optimized your portfolio with a Standard Deviation of .10, but then you wouldn’t have this cool graph! I think you learn a lot though from taking steps 11-13 because it gives you a visual representation of what is going on.