Question 2. Go to the Blackboard and download the MS excel file, stock_return.xlsx. It contains a year of monthly stock price data of Amazon, Pfizer, and S&P 500 (Market Index). Using the data, answer the following questions. (50 points)

(1) Compute the monthly return of Amazon and Pfizer. You should get 12 monthly returns for each. To get a monthly return, you need to use previous months stock price. For example, Amazons stock return of 2018-01 will be [(Stock price of 2018-01) - (Stock price of 2017-12)] / (Stock price of 2017-12) (5 points)

(2) Compute the average stock return (arithmetic average), standard deviation, and holding period return for Amazon and Pfizer. Use Excel function of =AVERAGE() for average return, =STDEV.S() for standard deviation, and =PRODUCT() for holding period return. (10 points)

(3) Form a portfolio investing 50% of your money into Amazon and 50% into Pfizer.

Calculate the monthly return for each month of the portfolio. Then, provide arithmetic average and standard deviation of the portfolio return. Check and report whether the linear combination (weighted average) of standard deviation of two companies is smaller than portfolios standard deviation or not. (15 points)

(4) Calculate the monthly market return, average market return, and market variance using S&P500 Index. Using the variance of market return and covariance between market return and Amazons return, calculate the Amazons beta. Use =VAR.S() for variance and =COVARIANCE.S() function to get covariance (15 points)

(5) Suppose risk free rate is 1% and expected market return is 5%. Compute the expected return of Amazon using CAPM. You would get the beta in (4) (5 points)

Date	Stock Price
	Amazon	Pfizer	S&P 500
2017/12	1169,47	33,38	2673,61
2018/01	1450,89	34,13	2823,81
2018/02	1512,45	33,46	2713,83
2018/03	1447,34	33,01	2640,87
2018/04	1566,13	34,05	2648,05
2018/05	1629,62	33,42	2705,27
2018/06	1699,80	34,07	2718,37
2018/07	1777,44	37,50	2816,29
2018/08	2012,71	38,99	2901,52
2018/09	2003,00	41,74	2913,98
2018/10	1598,01	40,78	2711,74
2018/11	1690,17	43,79	2760,17
2018/12	1501,97	41,66	2506,85