PLEASE DO IT ON EXCEL AND SHOW STEPS

The CAPM Method for the Cost of Equity

Download historical price for S&P500 and Alphabet Inc. (GOOGL) for 2014-2018. Calculate their monthly returns and excess returns using the given risk free rate. Assume that the market risk premium is 5.5%.

(1) Create a plot that shows the relationship between Alphabet's risk premium and the market risk premium. Add a trendline and its equation to the graph. Label your axes. Make sure the graph is readable for a supervisor.

Date	Risk-Free Rate
1/1/2014
2/1/2014	0.0022308
3/1/2014	0.0022308
4/1/2014	0.0022308
5/1/2014	0.0022308
6/1/2014	0.0022308
7/1/2014	0.0022308
8/1/2014	0.0022308
9/1/2014	0.0022308
10/1/2014	0.0022308
11/1/2014	0.0022308
12/1/2014	0.0022308
1/1/2015	0.0022308
2/1/2015	0.0022308
3/1/2015	0.0022308
4/1/2015	0.0022308
5/1/2015	0.0022308
6/1/2015	0.0022308
7/1/2015	0.0022308
8/1/2015	0.0022308
9/1/2015	0.0022308
10/1/2015	0.0022308
11/1/2015	0.0022308
12/1/2015	0.0022308
1/1/2016	0.0022308
2/1/2016	0.0022308
3/1/2016	0.0022308
4/1/2016	0.0022308
5/1/2016	0.0022308
6/1/2016	0.0022308
7/1/2016	0.0022308
8/1/2016	0.0022308
9/1/2016	0.0022308
10/1/2016	0.0022308
11/1/2016	0.0022308
12/1/2016	0.0022308
1/1/2017	0.0022308
2/1/2017	0.0022308
3/1/2017	0.0022308
4/1/2017	0.0022308
5/1/2017	0.0022308
6/1/2017	0.0022308
7/1/2017	0.0022308
8/1/2017	0.0022308
9/1/2017	0.0022308
10/1/2017	0.0022308
11/1/2017	0.0022308
12/1/2017	0.0022308
1/1/2018	0.0022308
2/1/2018	0.0022308
3/1/2018	0.0022308
4/1/2018	0.0022308
5/1/2018	0.0022308
6/1/2018	0.0022308
7/1/2018	0.0022308
8/1/2018	0.0022308
9/1/2018	0.0022308
10/1/2018	0.0022308
11/1/2018	0.0022308
12/1/2018	0.0022308

(2) Use the information provided and the CAPM framework to estimate the cost of equity for Alphabet .

Cost of equity for Alphabet Inc. (GOOGL) Classic CAPM: rE = rf + b*[E(rM)-rf ]
GOOGL beta
Risk free rate, rf
Equity Market Risk Premium
Cost of equity, rE,