Question

Year	Rp	Rm	Rf
2000	18.1832	-24.9088	5.112
2001	-3.454	-15.1017	5.051
2002	47.5573	20.784	3.816
2003	28.7035	9.4163	4.2455
2004	29.8613	8.7169	4.2182
2005	11.2167	16.3272	4.3911
2006	32.2799	14.5445	4.7022
2007	-41.0392	-36.0483	4.0232
2008	17.6082	9.7932	2.2123
2009	14.1058	16.5089	3.8368
2010	16.1978	8.0818	3.2935
2011	11.558	15.1984	1.8762
2012	42.993	27.1685	1.7574
2013	18.8682	17.2589	3.0282
2014	-1.4678	5.1932	2.1712
2015	9.2757	4.4993	2.2694
2016	8.5985	23.624	2.4443

When performing calculations in the following problems, use the numbers in the table as-is. I.e., do NOT convert 8.5985 to 8.5985% (or 0.085985). Just use plain 8.5985.

1. Using the basic market model regression, ,R p = α + β R m + ϵ , what is the beta of this portfolio?

2. For precision, find the portfolio beta using the excess return market model: R p − R f = α + β ∗ ( R m − R f ) + ϵ

[Hint: compute annual excess returns first, then run regression.]

Answer 1

Portfolio Beta value = 0.8065

We first calculate the excess returns first

Excess returns are calculated by subtracting the risk-free value from the Rp and Rm

Year	Rp	Rm	Rf	Rp-Rf	Rm-Rf
2000	18.1832	-24.909	5.112	13.0712	-30.021
2001	-3.454	-15.102	5.051	-8.505	-20.153
2002	47.5573	20.784	3.816	43.7413	16.968
2003	28.7035	9.4163	4.2455	24.458	5.1708
2004	29.8613	8.7169	4.2182	25.6431	4.4987
2005	11.2167	16.3272	4.3911	6.8256	11.9361
2006	32.2799	14.5445	4.7022	27.5777	9.8423
2007	-41.039	-36.048	4.0232	-45.062	-40.072
2008	17.6082	9.7932	2.2123	15.3959	7.5809
2009	14.1058	16.5089	3.8368	10.269	12.6721
2010	16.1978	8.0818	3.2935	12.9043	4.7883
2011	11.558	15.1984	1.8762	9.6818	13.3222
2012	42.993	27.1685	1.7574	41.2356	25.4111
2013	18.8682	17.2589	3.0282	15.84	14.2307
2014	-1.4678	5.1932	2.1712	-3.639	3.022
2015	9.2757	4.4993	2.2694	7.0063	2.2299
2016	8.5985	23.624	2.4443	6.1542	21.1797

For running a regression in excel, go to Data => Data analysis => Regression

We now run a regression with Rm-Rf (excess market returns) as X(independent) variable and Rp-Rf (excess portfolio returns) as Y(dependent) variable

We get the following result

Here, the X Variable 1 coefficient is the Beta value = 0.8065

Alpha = Intercept coefficient = 8.94729