Question

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 month’s stock price. For example, Amazon’s 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 portfolio’s 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 Amazon’s return, calculate the Amazon’s 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

Answer 1

Stock Price
Date	Amazon	Amazon Monthly Return	Pfizer	Pfizer Monthly return	S&P 500	S&P 500 Montly return
2017/12	116947		3338		267361
2018/01	145089	24.06	3413	2.25	282381	5.62
2018/02	151245	4.24	3346	-1.96	271383	-3.89
2018/03	144734	-4.30	3301	-1.34	264087	-2.69
2018/04	156613	8.21	3405	3.15	264805	0.27
2018/05	162962	4.05	3342	-1.85	270527	2.16
2018/06	169980	4.31	3407	1.94	271837	0.48
2018/07	177744	4.57	3750	10.07	281629	3.60
2018/08	201271	13.24	3899	3.97	290152	3.03
2018/09	200300	-0.48	4174	7.05	291398	0.43
2018/10	159801	-20.22	4078	-2.30	271174	-6.94
2018/11	169017	5.77	4379	7.38	276017	1.79
2018/12	150197	-11.13	4166	-4.86	250685	-9.18
	2105900	32	47998	23	3553436	-5
Arthmatic mean	2.69			1.96		-0.44
Standard Deviation	11.25468			4.594059		4.415681
HPR is Unable to Calculate due to unavailabilty of Purchase and Sale price of Stock for the period

Next questions i dont have any prectical clue