Question
The following stock data have been collected. Use the data to conduct a test of the Capital Asset Pricing Model. Specifically: Perform first-pass tests for
The following stock data have been collected. Use the data to conduct a test of the Capital Asset Pricing Model. Specifically:
Perform first-pass tests for a single-index model and tabulate the summary statistics.
Specify the hypotheses for second-pass regression used to test the SML of the CAPM
Perform second pass SML regression of average excess returns against betas
Group the stock into 3 portfolios based on their betas.
Repeat tests and explain results
Plot the CML, the 9 stocks, and the 3 portfolios. Compare mean-variance efficiency of the stocks, the portfolios, and the index. What do the comparisons indicate about CAPM?
please help on excel
| Stock Excess Returns | ||||||||||
| Year | Excess Market Index | A | B | C | D | E | F | G | H | I |
| 1 | 30.00 | 32.00 | -25.20 | 30.00 | 42.89 | -42.00 | 39.67 | 72.00 | 40.22 | 60.00 |
| 2 | -5.00 | -22.00 | 14.00 | -25.11 | -1.50 | 10.20 | -33.00 | -55.00 | -43.00 | -11.00 |
| 3 | 22.00 | 45.00 | -25.04 | 36.00 | -39.86 | -6.00 | -5.69 | 26.73 | 14.49 | 12.00 |
| 4 | 15.00 | 14.46 | -22.00 | 12.00 | 6.00 | -3.21 | 22.00 | -3.82 | 13.74 | 0.09 |
| 5 | 5.00 | 15.67 | 61.93 | 55.00 | -32.82 | 40.00 | -42.96 | 44.00 | 24.24 | 8.98 |
| 6 | 24.00 | -36.00 | 16.00 | -19.56 | 23.00 | 90.43 | 76.72 | 1.72 | 77.22 | 35.00 |
| 7 | 11.00 | -31.55 | -65.00 | 44.00 | 67.00 | 12.00 | 18.00 | -12.00 | -13.40 | 28.95 |
| 8 | -1.00 | -5.00 | 47.02 | -42.28 | 30.00 | -17.64 | 28.83 | 43.00 | 28.12 | 12.00 |
| 9 | 16.00 | 5.50 | 28.69 | 4.00 | 2.32 | 37.00 | 18.93 | -2.45 | 37.65 | 22.00 |
| 10 | 18.00 | -8.03 | 48.61 | 23.65 | 28.00 | -3.65 | 23.31 | 15.36 | 45.00 | 52.51 |
| 11 | -50.00 | -25.00 | -55.00 | -12.00 | -42.00 | -50.00 | -35.00 | -5.00 | -72.47 | -80.26 |
| 12 | -5.00 | 4.75 | 42.95 | -18.60 | 24.00 | 13.24 | -15.33 | -25.53 | -1.50 | -24.46 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started