Question

Regression Analysis Estimate the beta of Amazon. 1. Use Yahoo Finance, download Amazon's historical monthly stock prices for the "time period" (1/1/2009- 12/31/2018) and calculate monthly holding period returns. Holding period return (Ending price-Beginning Price)/ Beginning price. 2. Use Yahoo Finance, download S&P 500 historical monthly prices for the "time period" (1/1/2009 -12/31/2018) and calculate monthly holding period returns. Holding period return (Ending price - Beginning Price) / Beginning price. 3. Use 1% as Risk-free rate during these periods. 4. Run a regression of Y on X. The Y variable is Amazon excess return (which is Amazon's return - risk free rate). X is S&P 500 excess return (or S&P 500 return - risk free rate). Show the regression summary result, the coefficient on X is an estimate of Amazon's beta. Please use the rubric below to guide the development of your assignment Submit the assignment in a new Excel file. M1 Assignment-Sensitivity Analysis Rubric

Answer 1

Calculation of HPR for Amazon and S&P 500 and beta for Amazon using regression:

Formula sheet

A	B	C	D	E	F	G	H	I	J	K	L
2		Risk Free rate	0.01
3		Date	AMZN	S&P 500	AMZN	S&P 500	AMZN	S&P 500
4			Closing Price	Closing Price	Holding period return (HPR)	Holding period return (HPR)	Excess Return	Excess Return
5		39814	58.82	825.880005
6		39845	64.790001	735.090027	=(D6-D5)/D5	=(E6-E5)/E5	=F6-$D$2	=G6-$D$2
7		39873	73.440002	797.869995	=(D7-D6)/D6	=(E7-E6)/E6	=F7-$D$2	=G7-$D$2
8		39904	80.519997	872.809998	=(D8-D7)/D7	=(E8-E7)/E7	=F8-$D$2	=G8-$D$2
120		43313	2012.709961	2901.52002	=(D120-D119)/D119	=(E120-E119)/E119	=F120-$D$2	=G120-$D$2
121		43344	2003	2913.97998	=(D121-D120)/D120	=(E121-E120)/E120	=F121-$D$2	=G121-$D$2
122		43374	1598.01001	2711.73999	=(D122-D121)/D121	=(E122-E121)/E121	=F122-$D$2	=G122-$D$2
123		43405	1690.170044	2760.169922	=(D123-D122)/D122	=(E123-E122)/E122	=F123-$D$2	=G123-$D$2
124		43435	1501.969971	2506.850098	=(D124-D123)/D123	=(E124-E123)/E123	=F124-$D$2	=G124-$D$2
125
126
127		SUMMARY OUTPUT
128
129		Regression Statistics
130		Multiple R	0.449131019219171
131		R Square	0.201718672424851
132		Adjusted R Square	0.194895755095149
133		Standard Error	0.0765821502576298
134		Observations	119
135
136		ANOVA
137			df	SS	MS	F	Significance F
138		Regression	1	0.173392817869697	0.173392817869697	29.5648712533437	3.00089975764199E-07
139		Residual	117	0.686184611355616	0.00586482573808219
140		Total	118	0.859577429225313
141
142			Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
143		Intercept	0.0209744059937192	0.00702030300509093	2.98767816410619	0.00342565328722422	0.007071063765257	0.0348777482221814	0.007071063765257	0.0348777482221814
144		X Variable 1	0.996212498399354	0.183216250031647	5.43735884905013	3.00089975764204E-07	0.633362315984401	1.35906268081431	0.633362315984401	1.35906268081431
145
146		Thus the coefficient is	=D144
147		Hence Beta for Amazon is	=D146
148