You will conduct performance analysis on Apple Inc. with historical monthly data. First, you need Apples monthly return data. As you did in the first assignment, visit http://finance.yahoo.com. Enter Apple or its ticker symbol AAPL in the Get Quotes box. Then click on Historical Prices.

Choose MAX for time period and Monthly frequency, click Apply, and then Download Data. Keep only Date and Adj. Close. In the third column, calculate monthly returns in percentage from adjusted prices. Notice that Yahoo Finance matches the first trading day of the month and the closing monthly price (because it also provides monthly opening prices) for example, what appears to be 1/3/2013 adjusted closing price is in fact the end of March 2013 price (retrospectively) adjusted for dividend payments.

Calculate monthly returns from January 1981 to August 2018 i.e., manually delete the September 2018 and October 2018 adjusted prices in Excel after downloading every adjusted close price available from Yahoo Finance and compute monthly returns. Note that you need the December 1980 price to compute the January 1981 return.

You will also need monthly US market excess returns, SMB, HML, and risk-free rates. For those, download the monthly Fama-French factor data from Ken Frenchs website: http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_Factors_TXT.zip (no space) and unzip the file. The text file contains monthly and yearly factor data. Delete the descriptions and yearly data and save the text file (in other words, keep only the monthly figures and headings, Mkt-RF, SMB, HML, and RF) and save the text file.

Load the text file in Excel. To do so, click on Open in Excel and choose All Files at the bottom right corner (as opposed to All Excel Files which is the default) to see the text file you saved. Excel will ask what kind of file it is. Choose Delimited and click on Next. Check Space as well as Tab as delimiters and Excel will load the file correctly month, Mkt-RF, SMB, HML, and RF in separate columns.

Notice that the return figures in the Fama-French data are in percentage i.e., 1 means 1 percent or 0.01 whereas the Apple returns you calculated might be decimal numbers. Align the units of the two series either decimal numbers or percentage figures.

Merge the two datasets. Double-check that the data sets are time-lined correctly after discarding 192607 198012 observations in the Fama-French dataset

Calculate Apples monthly excess returns in another column by subtracting RF from its raw returns. Again, ensure that they are of the same unit e.g., percentage or raw decimal figures. Now you should have eight columns now Month, AAPL Adjusted Price, AAPL Return, Mkt-RF, SMB, HML, RF, and AAPL Excess Return

Regression

You are required to run the following regression: R_i R_f = a_i + b_i[R_M R_f] where R_i, R_f, and R_M are monthly returns on the stock (AAPL), the riskless security and the market respectively.

The regression dialogue box that appears on your screen is shown below (Notice that your data ranges would be different).

You should enter the data range for your Y variable (Excess return on Apple) from your Excel spreadsheet into the box described Input Y Range. Similarly, enter the data range for your X variable (Mkt-RF) into the box Input X Range. This is most easily done by selecting the range for each column in your excel spreadsheet (do not use the range shown in the screenshot).

You may elect to click the Labels box. If you click this box your data range for the Y and the X variables MUST include the column headings R_i-R_f and R_M-R_f or Excel will ignore the first observation in your dataset. If you do not click the Labels box your data range for each column should include numbers only do not include the column headings in that case. Including the column headings in your data range automatically labels the slope coefficient with its variable name.

Clicking New Worksheet Ply places your regression output on a new worksheet.

Next click the small box beside Line Fit Plots. This automatically generates a line of best fit plot (regression or characteristic line)

Finally, click on OK to generate the regression results and line of best fit plot.

To include more than one factors, specify a block of ranges in Input X Range: e.g., $D$2:$F$322 (entire range of the X variables) in the example would run a regression with three explanatory variables (factors) located in column D, E, and F.

1).Gather the data and make necessary adjustments and perform calculations as instructed above. Also print out the first 12 observations, monthly figures from September 2017 to August 2018 and attach them to your written assignment answers to show that you generated data correctly. (If you are typing your assignment in MS Word, copy and paste the first 12 observations of data from Excel.)

2)Attach a print out of your Excel results, specifically (1) the Excel regression table (3 rows) showing the coefficients obtained for the intercept and R_M-R_f and (2) the line of best fit plot obtained from your regression output. Based on your Excel output report and interpret (i.e. provide a brief description of what each of these estimates tells you about AAPL) the following results:

a_i , the alpha coefficient.

b_i, the beta coefficient

R², the coefficient of determination

3)Now estimate Fama-French three factor model by regressing AAPL excess return on MKT-Rf, SMB, and HML and attach the Excel regression table. Comment on Apples historical performance relative to the expected return from the Fama-French three factor model.

4)Compute a) Apples Sharpe ratio, b) M² measure, and c) Treynor Index based on the data (entire sample period) and interpret the results. For all standard deviations in this part, use raw returns, not excess returns. For expected excess return (risk premium), use the sample average of excess returns (or average of raw return minus average Rf). Note that in order to obtain raw market returns you can add back R_f to the market excess returns in the Fama-French dataset.