Investments HW #6

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Investments HW #6

This homework should reflect your own work. Please use Excel to perform the tasks. It is recommended you create an Excel worksheet for each of the problem questions. Please mark your answers clearly. For questions that doesn’t involve calculations, please type in your answers in the same Excel file as texts and clearly label your answers so it is easy for the TAs to locate them.

Questions 1

(100 points) Perform the following tasks to develop a model to predict Amazon’s stock returns

1) (20 points) Data gathering: a. Go to Prof. French’s website (https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html) and download the monthly five factor data. (search Fama/French 5 Factors (2x3) on the website) with the starting month being July 1963. b. download the historical monthly adjusted close price for Amazon from yahoo finance’s website (https://finance.yahoo.com/quote/AMZN/history?p=AMZN), with the starting month being June 1997. Copy both data into your Excel solution file as separate worksheets. Calculate the Amazon Stock monthly returns using the adjclose data column.

2) (20 points) Create a new worksheet called “data” where you line up the monthly returns of Amazon as well as the Fama-French five factors, for the time period of Jan. 2016 to Dec. 2021. Add another column which is the excess return for Amazon (return of Amazon – riskfree rate, where riskfree rate is the “RF” column from the French website download) Please make sure all returns are in percent. Please pay attention to the units of the numbers you received from the French website and make sure all returns are in percent. Please also pay attention of the date formatting and date convention for each of the data sources so you put returns from the same month on the same row.

3) (20 points) Using the data from step 2, run a regression analysis with the excess return for Amazon as the Y variable and the five Fama-French factors as your X variables. Output all of the model details to a new worksheet called “Model”

4) (40 points) Create a new worksheet called “Prediction” and perform the following calculations (assuming we are at end of 2021 and trying to predict the stock returns for Amazon for January of 2022 using the Fama-French model)

a. Copy over the alpha and beta coefficients from the regression model

b. (10 points) Predict the five Fama-French factor returns for January of 2022, as well as the riskfree rate by two ways: 1. As average of the factor returns and riskfree rate in the year 2021 and 2. Use the January 2022 realized factor returns and risk free rate you downloaded from the French website

c. (20 points) Predict the January 2022 Amazon stock return using the regression model, using the two sets of factor returns and riskfree rates obtained in step b above.

d. (10 points) Which prediction from step c is more accurate? Why?