Question

In: Finance

Use the uploaded price data to answer the questions that follow. Assume that the S&P 500...

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%

Solutions

Expert Solution

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


Related Solutions

Use the following data for questions 19 and 20 Month Nike S&P 500 Index Prices Prices...
Use the following data for questions 19 and 20 Month Nike S&P 500 Index Prices Prices Jan-13 52.90 2,279 Feb-13 57.16 2,364 Mar-13 55.73 2,363 Apr-13 55.41 2,384 May-13 52.99 2,412 Jun-13 59.00 2,423 Jul-13 59.05 2,470 Aug-13 52.81 2,472 Sep-13 51.85 2,519 Oct-13 54.99 2,575 Nov-13 60.42 2,648 Dec-13 62.55 2,674 Jan-14 68.22 2,824 What is the standard deviation of monthly percentage returns for the S&P 500? a. 1.64% b. 6.7% c. 1.81% d. 2.53% For the prices presented...
​​​​​​ For the following questions the price of an S&P 500 Index Futures contract equals 250...
​​​​​​ For the following questions the price of an S&P 500 Index Futures contract equals 250 ∙ Current S&P 500 Index Value. The margin requirement on each contract is $20,000. Please answer the following questions: At Yahoo! Finance identify the last traded value for the S&P 500 Index and the date it was last retrieved: Last Traded S&P 500 Index Value Date At the CME Group site obtain the next four quarterly S&P 500 Index Futures quotes: Month/Year Last Price...
Use the data below to answer the three questions that follow it. $/£ (USD/GBP) - Sport...
Use the data below to answer the three questions that follow it. $/£ (USD/GBP) - Sport Rate is 1.2360 $/£ (USD/GBP) - 180-day forward rate is 1.2300 $ interest rates are 0.25% (one quarter of a percent or 25 basis points) £ (GBP) interest rates are 1.00% If you could borrow either £ 10,000,000 or $ 10,000,000 how much profit could you make from a covered interest arbitrage? Be sure to state your answer in terms of USD or GPB.
Assume the average stock price for companies making up the S&P 500 at certain time period...
Assume the average stock price for companies making up the S&P 500 at certain time period is $40, and the standard deviation is $10. Assume the stock prices are normally distributed. Assume the stock prices are normally distributed. From Table 1: Cumulative Probabilities for the Standard Normal Distribution, what is the probability company will have a stock price of at least $50? 0.9332 0.8413 0.1583 0.0068 None of the above 2. Let Z be the standard normal random variable. What...
1)    Use the below table of share price for TSLA and index level for the S&P 500...
1)    Use the below table of share price for TSLA and index level for the S&P 500 for the CAPM questions that follow related to TSLA. You can copy this table and paste it completely into Excel to avoid typing it out. Calculate the beta of TSLA.   Date TSLA S&P 500 10/1/2018 67.464 2711.74 11/1/2018 70.096 2760.17 12/1/2018 66.56 2506.85 1/1/2019 61.404 2704.1 2/1/2019 63.976 2784.49 3/1/2019 55.972 2834.4 4/1/2019 47.738 2945.83 5/1/2019 37.032 2752.06 6/1/2019 44.692 2941.76 7/1/2019 48.322 2980.38...
6. Answer the following questions based on the following quotation. On October 1, 2007, S&P 500...
6. Answer the following questions based on the following quotation. On October 1, 2007, S&P 500 closed at 1547 where the quotation of CALL options on S&P 500 was as follows. Those contracts expire in October 2007. Strike Price Open High Low Last Sett 1540 -- -- -- -- 31.70 1600 1.70 3.60 1.70 3.00 3.35 (a) Which option is in the money? (b) Decompose the value of 1540 call, $31.7, into intrinsic value and time value. (c) Again using...
The following historic data are available for the S&P 500 and AAPL:
The following historic data are available for the S&P 500 and AAPL:      30 year Arithmetic Average Return for S&P 500 =10%      30 year Arithmetic Average Return for AAPL = 12%      30 year Arithmetic Average Return on treasury bonds = 5%On an ex post basis, what is the Risk Premium on the Market?On an ex post basis, what is the Risk Premium for AAPL?On an ex post basis, what is the Risk Premium for treasury bonds?
the spot price on the S&P 500 is $20,000 and the 1-year futures price is $20,609.09....
the spot price on the S&P 500 is $20,000 and the 1-year futures price is $20,609.09. The 1-year risk-free rate is 3% per annum with continuous compounding. Draw the profit and payoff function for the long future. (Provide labels for the axes and label a point on the functions above, below and at the futures price) Note an S&P 500 futures contract is for 250 times the S&P 500. Calculate what the payoff and profit at expiration is if the...
Use the following table to complete questions 1-3. S&P 500 returns and the risk-free rate of...
Use the following table to complete questions 1-3. S&P 500 returns and the risk-free rate of return can take on the following possible values with the given probabilities. State Pi S&P 500 Returns Risk-Free Rate Recession 0.25 -10% 8% Low Pullback 0.25 -5% 8% No Growth 0.25 0% 8% Low Growth 0.25 5% 8% 1. What is the expected value of S&P 500 returns? What is the expected value of risk-free returns? 2. What is the variance and standard deviation...
3) Use the data below to answer the questions that follow X 7 9 11 15...
3) Use the data below to answer the questions that follow X 7 9 11 15 15 17 21 21 21 29 33 34 35 35 Y 22 20 17 17 16 12 10 10 7    4    4    3 1 0 a) Calculate the correlation coefficient “r” b) Explain what your value for r means for this data c) Determine the equation for the linear regression line of this data d) What does your equation predict for...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT