In: Finance
Use the uploaded price data to answer the questions that follow. Assume that the S&P 500 is the proxy for the market portfolio. • A: Price data for the American Funds Growth Fund of America (ticker symbol AGTHX) and S&P 500 is given in the Excel Upload file. Each month, calculate the excess returns for both AGTHX and the S&P 500.
• B: Using the excess returns, estimate the fund’s alpha and beta (assume that the CAPM is the appropriate benchmark model). What is the 95% confidence interval for beta?
• C: Does AGTHX significantly outperform, significantly underperform, or perform as expected (again using CAPM as the benchmark model)? Why?
AGTHX | S&P 500 | Risk-free rate | |
Month | Price | Price | |
1 | 22.147606 | 1400.380005 | 0.01% |
2 | 20.705042 | 1280 | 0.00% |
3 | 19.993551 | 1267.380005 | 0.00% |
4 | 19.980497 | 1282.829956 | 0.00% |
5 | 17.591454 | 1166.359985 | 0.00% |
6 | 14.510504 | 968.75 | 0.00% |
7 | 13.309449 | 896.23999 | 0.00% |
8 | 13.368198 | 903.25 | 0.00% |
9 | 12.783327 | 825.880005 | 0.00% |
10 | 11.957958 | 735.090027 | 0.00% |
11 | 12.981416 | 797.869995 | 0.00% |
12 | 14.242583 | 872.809998 | 0.00% |
13 | 15.180202 | 919.140015 | 0.00% |
14 | 15.15379 | 919.320007 | 0.00% |
15 | 16.131027 | 987.47998 | 0.00% |
16 | 16.461176 | 1020.619995 | 0.00% |
17 | 17.180901 | 1057.079956 | 0.00% |
18 | 16.778114 | 1036.189941 | 0.00% |
19 | 17.709133 | 1095.630005 | 0.00% |
20 | 18.045885 | 1115.099976 | 0.00% |
21 | 17.382166 | 1073.869995 | 0.00% |
22 | 17.901236 | 1104.48999 | 0.00% |
23 | 18.946026 | 1169.430054 | 0.00% |
24 | 19.072468 | 1186.689941 | 0.00% |
25 | 17.521915 | 1089.410034 | 0.00% |
26 | 16.730003 | 1030.709961 | 0.00% |
27 | 17.768139 | 1101.599976 | 0.00% |
28 | 16.989536 | 1049.329956 | 0.00% |
29 | 18.520124 | 1141.199951 | 0.00% |
30 | 19.298727 | 1183.26001 | 0.00% |
31 | 19.205561 | 1180.550049 | 0.00% |
32 | 20.257013 | 1257.640015 | 0.00% |
33 | 20.770506 | 1286.119995 | 0.00% |
34 | 21.468224 | 1327.219971 | 0.00% |
35 | 21.501766 | 1325.829956 | 0.00% |
36 | 22.092144 | 1363.609985 | 0.00% |
37 | 21.716448 | 1345.199951 | 0.01% |
38 | 21.354174 | 1320.640015 | 0.01% |
39 | 21.052277 | 1292.280029 | 0.01% |
40 | 19.609879 | 1218.890015 | 0.01% |
41 | 17.892424 | 1131.420044 | 0.01% |
42 | 19.764179 | 1253.300049 | 0.00% |
43 | 19.656843 | 1246.959961 | 0.00% |
44 | 19.274439 | 1257.599976 | 0.01% |
45 | 20.819939 | 1312.410034 | 0.00% |
46 | 21.739561 | 1365.680054 | 0.00% |
47 | 22.260225 | 1408.469971 | 0.00% |
48 | 22.219656 | 1397.910034 | 0.00% |
49 | 20.684702 | 1310.329956 | 0.00% |
50 | 21.327084 | 1362.160034 | 0.00% |
51 | 21.509653 | 1379.319946 | 0.00% |
52 | 22.179085 | 1406.579956 | 0.00% |
53 | 22.895847 | 1440.670044 | 0.01% |
54 | 22.611847 | 1412.160034 | 0.00% |
55 | 23.08518 | 1416.180054 | 0.00% |
56 | 23.22718 | 1426.189941 | 0.00% |
57 | 24.466576 | 1498.109985 | 0.00% |
58 | 24.602913 | 1514.680054 | 0.01% |
59 | 25.420967 | 1569.189941 | 0.01% |
60 | 25.85726 | 1597.569946 | 0.01% |
61 | 26.59351 | 1630.73999 | 0.01% |
62 | 26.218565 | 1606.280029 | 0.01% |
63 | 27.677425 | 1685.72998 | 0.01% |
64 | 27.22068 | 1632.969971 | 0.01% |
65 | 28.638636 | 1681.550049 | 0.01% |
66 | 29.729378 | 1756.540039 | 0.01% |
67 | 30.465618 | 1805.810059 | 0.01% |
68 | 29.313528 | 1848.359985 | 0.01% |
69 | 30.721369 | 1782.589966 | 0.01% |
70 | 32.347878 | 1859.449951 | 0.01% |
71 | 31.654972 | 1872.339966 | 0.00% |
72 | 31.31946 | 1883.949951 | 0.00% |
73 | 32.420818 | 1923.569946 | 0.01% |
74 | 33.223133 | 1960.22998 | 0.00% |
75 | 32.66151 | 1930.670044 | 0.00% |
76 | 34.061916 | 2003.369995 | 0.01% |
77 | 33.369007 | 1972.290039 | 0.01% |
78 | 34.018154 | 2018.050049 | 0.01% |
79 | 34.579773 | 2067.560059 | 0.01% |
80 | 31.129822 | 2058.899902 | 0.00% |
81 | 33.938644 | 1994.98999 | 0.01% |
82 | 35.813839 | 2104.5 | 0.02% |
83 | 35.508015 | 2067.889893 | 0.01% |
84 | 36.07943 | 2085.51001 | 0.00% |
85 | 36.594501 | 2107.389893 | 0.00% |
86 | 35.998943 | 2063.110107 | 0.03% |
87 | 36.843987 | 2103.840088 | 0.08% |
88 | 34.856125 | 1972.180054 | 0.15% |
89 | 33.58453 | 1920.030029 | 0.13% |
90 | 36.361111 | 2079.360107 | 0.15% |
91 | 36.747417 | 2080.409912 | 0.17% |
92 | 33.230415 | 2043.939941 | 0.18% |
93 | 33.495281 | 1940.23999 | 0.18% |
94 | 33.144459 | 1932.22998 | 0.17% |
95 | 35.293278 | 2059.73999 | 0.13% |
96 | 35.880913 | 2065.300049 | 0.21% |
97 | 36.635193 | 2096.949951 | 0.27% |
98 | 36.310677 | 2098.860107 | 0.34% |
99 | 37.89817 | 2173.600098 | 0.32% |
100 | 38.126209 | 2170.949951 | 0.32% |
101 | 38.661224 | 2168.27002 | 0.42% |
102 | 37.950798 | 2126.149902 | 0.40% |
103 | 39.055901 | 2198.810059 | 0.40% |
104 | 36.872002 | 2238.830078 | 0.41% |
105 | 41.057636 | 2278.870117 | 0.44% |
106 | 42.029194 | 2363.639893 | 0.43% |
107 | 42.458923 | 2362.719971 | 0.38% |
108 | 43.290348 | 2384.199951 | 0.44% |
109 | 44.252563 | 2411.800049 | 0.40% |
110 | 44.121777 | 2423.409912 | 0.42% |
111 | 45.588455 | 2470.300049 | 0.41% |
112 | 45.597801 | 2471.649902 | 0.41% |
113 | 46.335808 | 2519.360107 | 0.42% |
114 | 48.045376 | 2575.26001 | 0.40% |
115 | 48.96088 | 2584.840088 | 0.40% |
116 | 46.279758 | 2673.610107 | 0.43% |
117 | 53.580002 | 2823.810059 | 0.36% |
118 | 52.119999 | 2713.830078 | 0.37% |
119 | 51 | 2640.870117 | 0.34% |
120 | 51.41 | 2656.300049 | 0.35% |
Step 1 Calculate Return of AGTHX using formula = (b4-B3)/B3 and return of S&P 500 using formula = (c4-c3)/c3
Step 2 Calculate Excess of Market Return over Risk free Return (Variable X) (=F4 - D4)
Step 2 Calcualte Excess of AGTHX Return over Risk free Return (Variable Y) (=E4-D4)
Step 4 Open data analysis in Data Tab, and select Regression
Step 5 Y variable should be excess of AGTHX return over Risk free Return & X variable should be excess of Market Return over Risk Free Return
Step 6 Click Ok and result will be displayed in a new Worksheet
Now you will get output as shown below:
Alpha is Intercept and Beta is X variable
95% confidence level is highlighted
Sice alpha is positive (0.00228) the security has outperformed the market