80.2K

Verified Solution

Link Copied!

Question 4. Excel Modelling II [5 marks]

Use 36 months of data on the industry portfolio of Agriculture (Agriculture) and other data of the market index excess returns (Rm Rf) and the risk-free return (Rf) contained in the Excel file to perform the following tasks.

date	Agriculture (%)	Rm - Rf (%)	Rf (%)
201601	-6.73	-5.77	0.01
201602	-0.47	-0.07	0.02
201603	-0.98	6.96	0.02
201604	7.17	0.92	0.01
201605	16.6	1.78	0.01
201606	-6.59	-0.05	0.02
201607	3.62	3.95	0.02
201608	-0.09	0.5	0.02
201609	-4.08	0.25	0.02
201610	-2.01	-2.02	0.02
201611	2.11	4.86	0.01
201612	3.31	1.82	0.03
201701	5.6	1.94	0.04
201702	3.96	3.57	0.04
201703	-0.43	0.17	0.03
201704	3.08	1.09	0.05
201705	0.45	1.06	0.06
201706	1.03	0.78	0.06
201707	-0.82	1.87	0.07
201708	0.17	0.16	0.09
201709	2.63	2.51	0.09
201710	1.7	2.25	0.09
201711	-1.56	3.12	0.08
201712	-1.86	1.06	0.09
201801	4.3	5.58	0.11
201802	1.2	-3.65	0.11
201803	-4.92	-2.35	0.12
201804	7.99	0.29	0.14
201805	1.93	2.65	0.14
201806	-3.07	0.48	0.14
201807	-0.69	3.19	0.16
201808	7.15	3.44	0.16
201809	-3.92	0.06	0.15
201810	-1.97	-7.68	0.19
201811	-3.57	1.69	0.18
201812	-10.93	-9.55	0.19

Required:

1. Describe the steps taken to estimate alpha and beta of the industry portfolio using the CAPM. [3 marks]
2. Use a table to summarize your estimation results of annual expected return, annual standard deviation, alpha, and beta of the industry portfolio. [2 marks]

Answer & Explanation Solved by verified expert

See Answer