The purpose of this assignment is to apply the statistical and portfolio concepts we have discussed using a spreadsheet, such as Excel, to compute and compare the returns and the relationships among the three stocks you selected in the first assignment (Assignment #1).
The requirements for this assignment are:
1. Collect monthly prices for each stock you selected in Assignment #1 as well as for the NYSE composite index for the period beginning December 2012 and ending December 2017. The data you need can be obtained by going to one of the following websites (or comparable websites). You should also be able to easily download data for the NYSE composite index from these sites.
NASDAQ:* https://www.nasdaq.com/symbol/ibm/historical–you can download stock price data even for stocks that are not traded on NASDAQ.
• Enter the stock symbol in the search box at the top of the page
• Change the “Select the Timeframe” from 3 months to 6 years.
• Once the data are loaded, go to the bottom of the page to download the numbers into an Excel spreadsheet
2. Using the spreadsheet functions, compute the monthly returns for each stock you selected and for the NYSE Index. Compute the mean and standard deviation of returns for each stock and the NYSE Index. Briefly discuss the results you find. Also, compare the attractiveness of each stock with respect to its risk and return and to the market’s risk and return.
3. Using the appropriate spreadsheet function (CORREL), determine the relationship between each stock by computing the correlation coefficient for each pair (e.g., Stock 1 and Stock 2 would be one pairing, Stock 1 and Stock 3 should be another pairing, and Stock 2 and Stock 3 should be the final pairing). Discuss the meaning of the results you attain. Explain why you think the relationships you found exist. Do you think the systematic risks associated with the stocks help to explain the relationships you found? Explain.
4. Using the spreadsheet’s regression function, run three regression analyses—for each regression, the return on the NYSE Index will be the independent variable and the return on one of the stocks will be the dependent variable. Discuss your results (i.e., the meaning of ß, r2, etc.). Are the results you found from these regressions similar to those you found with the correlations computed in Part 3 of this assignment? Explain.
To find the regression function in Excel, click on the Data tab at the top of the page. On the far right side of the menu you should see the option Data Analysis. After clicking on Data Analysis, scroll down to Regression. Enter the range of cells that contain the data you want analyzed. Before using the Regression function, make sure the cursor is located in cell that is empty with empty cells to the right and below. The output from the regression analysis will be printed at this location.
If you do not see the Data Analysis option on the Data page, click on File on left side of the menu at the top of the page. Next, click on Options at the bottom of the menu that appears. Then, click on Add-Ins on the next menu. Finally, click on Analysis ToolPak, click Go, and then check Analysis ToolPak and Analysis ToolPak-VBA on the menu that appears, which is named Add-ins available. Click, OK, and the Data Analyis option will be added to the Data page on your Excel.
5. Compare the beta coefficients you computed in part 4 with the beta coefficients reported on at least one online financial website, such as Yahoo Finance or Bloomberg. Why might the betas you computed differ from the betas you found online? Explain/Discuss.