Question

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

a)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.

b)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

c)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 (b)

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

a)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.

Monthly Return of Portfolio - Provided in Image

Average Portfolio Return = 2.32%

Standard Deviation = 6.96%

b)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

monthly market return - Provided in Image

average market return - -0.44%

market variance using S&P500 Index = 0.001949824

covariance between market return and Amazon’s return = 0.003889721

Beta = Covariance / Variance = 0.003889721 / 0.001949824 = 1.99

c)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 (b)

CAPM Return of Amazon = Risk Free Rate + Beta * (Expected Market Return - Risk Free rate)

CAPM Return of Amazon = 1% + 1.99 * (5% - 1%)

CAPM Return of Amazon = 8.96%

Please dont forget to upvote