Reference no: EM133114415
1. Go to Yahoo! Finance and download historical monthly prices for the following stocks: Johnson & Johnson (JNJ), General Mills (GIS), and Conoco Philips (COP). You should download prices from October 2016 through October 2021. Note that when you download the monthly prices into excel, Yahoo! will display prices as of the first day of the month; so if you calculate a return for JNJ using the prices given at 10/1/2021 and 11/1/2021 that will be the return for JNJ stock during the month of October, 2021. Also remember that you can use the "Adjusted Close" column from Yahoo! to calculate total returns (price change plus dividends) and not worry about accounting for dividends separately. Calculate total returns for these three stocks for the months from November 2016 to October 2021. This should give you five years' worth of data (60 monthly observations). For each stock, display the average monthly return, the geometric mean of the monthly returns, the standard deviation of monthly returns, and the minimum and maximum return for each stock. (You can find these with the MIN and MAX function in excel.) Using these metrics, which stock appears to be the riskiest? Which is the least riskiest?
2. Using the Regression tool in the Data Analysis package for excel, calculate the beta for each stock over this time period. Use Ken French's "Mkt-RF" returns as the market factor. (I have provided this in the accompanying excel file; copy and paste the returns into your spreadsheet.) Based upon market beta, which stock is the riskiest? Which is the least riskiest? Is the answer the same as your determination from question 1? Why or why not? (Give a short explanation.)
3. For each pair of stocks, determine the correlation. (You need to provide three numbers: JNJ-GIS, JNJ-COP, GIS-COP.) You can do this either with the correlation tool in Data Analysis or with the "CORREL" function in excel.
4. For each pair of stocks, calculate the expected return and standard deviation of a portfolio with weights that vary between 0% and 100%, in 10% intervals. For example, take JNJ-GIS. Your first portfolio will be 0% in JNJ and 100% in GIS. The second portfolio will be 10% in JNJ, and 90% in GIS. Your third portfolio will be 20% in JNJ, and 80% in GIS, and so on, all the way until your final portfolio, which is 100% in JNJ, and 0% in GIS. Perform this same calculation for the other two pairs of stocks. For each portfolio, calculate the expected return and standard deviation using your estimates from the questions above. This means you're going to conduct calculations for 11 portfolios, for three different pairs of stocks, for a total of 33 return-standard deviation pairs. (Hint: I have provided a formula for calculating the standard deviation of a portfolio in the Ken French data excel sheet. You can use that, but make sure you reference the appropriate cells if you apply it in your own spreadsheet.)
5. In excel, create a scatterplot of the 33 portfolios you have created. Put standard deviation on the x-axis and expected return on the y-axis. Color-code the dots for each one of the three portfolis. (I.e., the 11 dots for the JNJ-GIS portfolios can be one color, the 11 dots for JNJ-COP should be another color, etc.). If you prefer you can graph these by hand on a sheet of graphing paper and submit a picture of your chart.
6. Trace out in red the set of efficient portfolios in your graph, choosing the best options from all 33 of the portfolios you have generated. Remember, an efficient portfolio is one that delivers the most return for a level of standard deviation, or the least risk for a given level of return. What can you say about the set of portfolios that lie to the right or fall underneath this group of efficient portfolios?
7. Call portfolio A the 50%-50% portfolio between COP and GIS, and call portfolio B the 50%-50% portfolio between JNJ and GIS. Conduct the same exercise (0-100, 10-90, 20-80, etc.) between portfolios A and B as you did in part (3) using individual stocks. Here, the 0-100 portfolio will be portfolio A, the 10-90 will be 10% portfolio A and 90% portfolio B, etc. For each of these mixes between portfolios A and B, calculate the expected return and standard deviation. HINT: You'll want to start by calculating the monthly returns of portfolios A and B, which will give you two time-series. Think about the returns of these portfolios just like you would think about the returns of individual stocks. For example, you have an estimate of the mean return of portfolio A, and an estimate of the volatility of portfolio A. Same for B. You can also calculate a sample covariance between the returns of portfolio A and portfolio B. You will use these quantities to trace out the curve required for this question.
8. Using a purple line, trace out the new set of efficient portfolios that also includes the portfolios you created in step 7. How has this changed from the previous set of efficient portfolios in red? What is the intuition behind this - has it improved the efficient frontier? Why does this make sense?