Draw the security market line for each of the following conditions:
JMB232
job20aid209-711.xlsxData, 9-7
| Period | Porfolio A | Portfolio B | Factor 1 | Factor 2 | Factor 3 | ||
| 1 | 0.0108 | 0 | 0.0001 | -0.0101 | -0.0167 | ||
| 2 | 0.0758 | 0.0662 | 0.0689 | 0.0029 | -0.0123 | This is the data from 9.12. | |
| 3 | 0.0503 | 0.0601 | 0.0475 | -0.0145 | 0.0192 | This has been manually typed by one of you | |
| 4 | 0.0116 | 0.0036 | 0.0066 | 0.0041 | 0.0022 | classmates and is correct. You may use this file for | |
| 5 | -0.0198 | -0.0158 | -0.0295 | -0.0362 | 0.0429 | this problem. | |
| 6 | 0.0426 | 0.0239 | 0.0286 | -0.034 | -0.0154 | ||
| 7 | -0.0075 | -0.0247 | -0.0272 | -0.0451 | -0.0179 | ||
| 8 | -0.1549 | -0.1546 | -0.1611 | -0.0592 | 0.0569 | ||
| 9 | 0.0605 | 0.0406 | 0.0595 | 0.0002 | -0.0376 | ||
| 10 | 0.077 | 0.0675 | 0.0711 | -0.0336 | -0.0285 | ||
| 11 | 0.0776 | 0.0552 | 0.0586 | 0.0136 | -0.0368 | ||
| 12 | 0.0962 | 0.0489 | 0.0594 | -0.0031 | -0.0495 | ||
| 13 | 0.0525 | 0.0273 | 0.0347 | 0.0115 | -0.0616 | ||
| 14 | -0.0319 | -0.0055 | -0.0415 | -0.0559 | 0.0166 | ||
| 15 | 0.054 | 0.0259 | 0.0332 | -0.0382 | -0.0304 | ||
| 16 | 0.0239 | 0.0726 | 0.0447 | 0.0289 | 0.028 | ||
| 17 | -0.0287 | 0.001 | -0.0239 | 0.0346 | 0.0308 | ||
| 18 | 0.0652 | 0.0366 | 0.0472 | 0.0342 | -0.0433 | ||
| 19 | -0.0337 | -0.006 | -0.0345 | 0.0201 | 0.007 | ||
| 20 | -0.0124 | -0.0406 | -0.0135 | -0.0116 | -0.0126 | ||
| 21 | -0.0148 | 0.0015 | -0.0268 | 0.0323 | -0.0318 | ||
| 22 | 0.0601 | 0.0529 | 0.058 | -0.0653 | -0.0319 | ||
| 23 | 0.0205 | 0.0228 | 0.032 | 0.0771 | -0.0809 | ||
| 24 | 0.072 | 0.0709 | 0.0783 | 0.0698 | -0.0905 | ||
| 25 | -0.0481 | -0.0279 | -0.0443 | 0.0408 | -0.0016 | ||
| 26 | 0.01 | -0.0204 | 0.0255 | 0.2149 | -0.1203 | ||
| 27 | 0.0905 | 0.0525 | 0.0513 | -0.1669 | 0.0781 | ||
| 28 | -0.0431 | -0.0296 | -0.0624 | -0.0753 | 0.0859 | ||
| 29 | -0.0336 | -0.0063 | -0.0427 | -0.0586 | 0.0538 | ||
| 30 | 0.0386 | 0.018 | 0.0467 | 0.1331 | -0.0878 |
Step One
| The first thing that you should do on your excel program is make sure that you have added the "Analysis Toolpak", and "Analysis Toolpak-VBA" |
| into your excel capabilities. This is done by left clicking "file", then all the way at the bottom left, you see "options". Left click options, and it |
| will take you to "Add-Ins". Left click "Add-Ins". |
| Once you are here, go to "manage add-ins" and left click "go" at the right. This will take you to options, and you select "Analysis Toolpak" and |
| "Analysis Toolpak-VBA", and save it and go back to your data. |
Step Two
| Now that you have the analysis tool pack, and the data, you can perform the regressions. |
| To do this, at the top of your excel screen is a command for 'DATA', left click this, and on the |
| far right, you will see 'Data Analysis', left click this and you will see a drop down box for |
| different commands. The command you want is 'regression'. Left click regression. |
| The 'Y' range is all of portfolio A, so highlight that, including the label in the front row. |
| The 'X' range is all of the factors, so highlight all of the factor columns, all three, including |
| the labels. |
| Click on "labels", then go to "ouptput options" and select where you want to put the results. |
| The hit OK. |
Regression results
| Period | Porfolio A | Portfolio B | Factor 1 | Factor 2 | Factor 3 | ||||||||||
| 1 | 0.0108 | 0 | 0.0001 | -0.0101 | -0.0167 | ||||||||||
| 2 | 0.0758 | 0.0662 | 0.0689 | 0.0029 | -0.0123 | This is the data from before. | |||||||||
| 3 | 0.0503 | 0.0601 | 0.0475 | -0.0145 | 0.0192 | Completing the regression for Portfolio A, you would get the following: | |||||||||
| 4 | 0.0116 | 0.0036 | 0.0066 | 0.0041 | 0.0022 | SUMMARY OUTPUT | |||||||||
| 5 | -0.0198 | -0.0158 | -0.0295 | -0.0362 | 0.0429 | ||||||||||
| 6 | 0.0426 | 0.0239 | 0.0286 | -0.034 | -0.0154 | Regression Statistics | |||||||||
| 7 | -0.0075 | -0.0247 | -0.0272 | -0.0451 | -0.0179 | Multiple R | 0.9852274077 | ||||||||
| 8 | -0.1549 | -0.1546 | -0.1611 | -0.0592 | 0.0569 | R Square | 0.9706730449 | ||||||||
| 9 | 0.0605 | 0.0406 | 0.0595 | 0.0002 | -0.0376 | Adjusted R Square | 0.9672891655 | ||||||||
| 10 | 0.077 | 0.0675 | 0.0711 | -0.0336 | -0.0285 | Standard Error | 0.0099228067 | ||||||||
| 11 | 0.0776 | 0.0552 | 0.0586 | 0.0136 | -0.0368 | Observations | 30 | ||||||||
| 12 | 0.0962 | 0.0489 | 0.0594 | -0.0031 | -0.0495 | ||||||||||
| 13 | 0.0525 | 0.0273 | 0.0347 | 0.0115 | -0.0616 | ANOVA | |||||||||
| 14 | -0.0319 | -0.0055 | -0.0415 | -0.0559 | 0.0166 | df | SS | MS | F | Significance F | |||||
| 15 | 0.054 | 0.0259 | 0.0332 | -0.0382 | -0.0304 | Regression | 3 | 0.0847321843 | 0.0282440614 | 286.8521363361 | 4.89911143235997E-20 | ||||
| 16 | 0.0239 | 0.0726 | 0.0447 | 0.0289 | 0.028 | Residual | 26 | 0.0025600144 | 0.0000984621 | ||||||
| 17 | -0.0287 | 0.001 | -0.0239 | 0.0346 | 0.0308 | Total | 29 | 0.0872921987 | |||||||
| 18 | 0.0652 | 0.0366 | 0.0472 | 0.0342 | -0.0433 | ||||||||||
| 19 | -0.0337 | -0.006 | -0.0345 | 0.0201 | 0.007 | Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | ||
| 20 | -0.0124 | -0.0406 | -0.0135 | -0.0116 | -0.0126 | Intercept | 0.0056839482 | 0.0019475875 | 2.9184558912 | 0.0071672274 | 0.0016806248 | 0.0096872717 | 0.0016806248 | 0.0096872717 | |
| 21 | -0.0148 | 0.0015 | -0.0268 | 0.0323 | -0.0318 | Factor 1 | 0.9906032205 | 0.0444732265 | 22.2741477901 | 1.83657576884391E-18 | 0.8991871941 | 1.0820192468 | 0.8991871941 | 1.0820192468 | |
| 22 | 0.0601 | 0.0529 | 0.058 | -0.0653 | -0.0319 | Factor 2 | -0.2010465233 | 0.0435915428 | -4.6120534065 | 0.0000935964 | -0.2906502228 | -0.1114428239 | -0.2906502228 | -0.1114428239 | |
| 23 | 0.0205 | 0.0228 | 0.032 | 0.0771 | -0.0809 | Factor 3 | -0.133496714 | 0.0703726146 | -1.8969980682 | 0.0689889279 | -0.278149695 | 0.011156267 | -0.278149695 | 0.011156267 | |
| 24 | 0.072 | 0.0709 | 0.0783 | 0.0698 | -0.0905 | ||||||||||
| 25 | -0.0481 | -0.0279 | -0.0443 | 0.0408 | -0.0016 | ||||||||||
| 26 | 0.01 | -0.0204 | 0.0255 | 0.2149 | -0.1203 | This means that the expected return can be calculated as: | |||||||||
| 27 | 0.0905 | 0.0525 | 0.0513 | -0.1669 | 0.0781 | Expected return= .00568 + .990603(Factor 1) - .20105 (factor 2) - .1335(factor 3) | |||||||||
| 28 | -0.0431 | -0.0296 | -0.0624 | -0.0753 | 0.0859 | This has an R squared of .97, which means that this is a strong equation. | |||||||||
| 29 | -0.0336 | -0.0063 | -0.0427 | -0.0586 | 0.0538 | ||||||||||
| 30 | 0.0386 | 0.018 | 0.0467 | 0.1331 | -0.0878 | ||||||||||
| Now, repeat this process for portfolio B. | |||||||||||||||
| Then answer the rest of the questions. |
