| Calculating IBM's beta |
| Data on stock prices and dividends can be downloaded from the web and used to make betas for real |
| companies. I demonstrate the process for IBM in this section. Download stock prices and dividends |
| from http://finance.yahoo.com for IBM using its ticker symbol IBM Also download data for the S&P 500 |
| Index, whose symbol is ^GSPC to represent the market. Here are the steps we followed: |
| You can follow our steps and place your stock prices on top of the IBM ones, if your dates are the same. |
| 1. Access the Internet, then go to http://finance.yahoo.com/ |
| 2. Enter IBM in the symbol slot and then click Get quotes. |
| 3. Click on "Historical prices" to get a history of IBM prices. |
| 4. Enter as the Start Date March 10, 2009, and March 10, 2014 as the end date. Click the "monthly" button, |
| then "Get prices" to get 5 years of monthly prices for IBM. The closing prices are adjusted for dividends |
| and splits. |
| 5. Note that Yahoo's data is downloaded as a CSV file. Save the file as an excel spreadsheet. Call it IBM |
| 6. Repeat the process to get the S&P index, symbol ^GSPC . Save this file as SP500 and in excel format. |
| 7. Open the IBM file and delete the columns except for the date and closing prices. Then open the SP500 |
| file, copy the closing price data, and paste it into Column B on the IBM file. |
| 8. Now move the IBM data over to column D and then calculate the monthly returns on the market and |
| on IBM |
| 9. Now you can run the regression of IBM's returns on the market to find its beta. |
| 10. YOU CAN DUPLICATE WHAT WE JUST DID, BUT COPYING AND PASTING YOUR STOCK'S |
| PRICE AND PLACING ON TOP OF THE IBM ADJUSTED CLOSE |
| The returns will automatically be calculated |
| Date | S&P Adj Close | Market Return | IBM Adj Close | IBM's Return |
| 3/3/14 | 1877.17 | 0.0095 | 186.39 | 0.0066 |
| 2/3/14 | 1859.45 | 0.0431 | 185.17 | 0.0538 |
| 1/2/14 | 1782.59 | -0.0356 | 175.72 | -0.0581 |
| 12/2/13 | 1848.36 | 0.0236 | 186.55 | 0.0439 |
| 11/1/13 | 1805.81 | 0.0280 | 178.7 | 0.0080 |
| 10/1/13 | 1756.54 | 0.0446 | 177.28 | -0.0323 |
| 9/3/13 | 1681.55 | 0.0297 | 183.19 | 0.0160 |
| 8/1/13 | 1632.97 | -0.0313 | 180.31 | -0.0608 |
| 7/1/13 | 1685.73 | 0.0495 | 191.98 | 0.0206 |
| 6/3/13 | 1606.28 | -0.0150 | 188.11 | -0.0813 |
| 5/1/13 | 1630.74 | 0.0208 | 204.76 | 0.0319 |
| 4/1/13 | 1597.57 | 0.0181 | 198.43 | -0.0504 |
| 3/1/13 | 1569.19 | 0.0360 | 208.97 | 0.0621 |
| 2/1/13 | 1514.68 | 0.0111 | 196.76 | -0.0069 |
| 1/2/13 | 1498.11 | 0.0504 | 198.12 | 0.0601 |
| 12/3/12 | 1426.19 | 0.0071 | 186.88 | 0.0078 |
| 11/1/12 | 1416.18 | 0.0028 | 185.44 | -0.0186 |
| 10/1/12 | 1412.16 | -0.0198 | 188.96 | -0.0623 |
| 9/4/12 | 1440.67 | 0.0242 | 201.51 | 0.0647 |
| 8/1/12 | 1406.58 | 0.0198 | 189.27 | -0.0015 |
| 7/2/12 | 1379.32 | 0.0126 | 189.56 | 0.0021 |
| 6/1/12 | 1362.16 | 0.0396 | 189.17 | 0.0139 |
| 5/1/12 | 1310.33 | -0.0627 | 186.58 | -0.0646 |
| 4/2/12 | 1397.91 | -0.0075 | 199.46 | -0.0075 |
| 3/1/12 | 1408.47 | 0.0313 | 200.97 | 0.0606 |
| 2/1/12 | 1365.68 | 0.0406 | 189.49 | 0.0254 |
| 1/3/12 | 1312.41 | 0.0436 | 184.79 | 0.0474 |
| 12/1/11 | 1257.6 | 0.0085 | 176.43 | -0.0219 |
| 11/1/11 | 1246.96 | -0.0051 | 180.38 | 0.0223 |
| 10/3/11 | 1253.3 | 0.1077 | 176.44 | 0.0558 |
| 9/1/11 | 1131.42 | -0.0718 | 167.11 | 0.0172 |
| 8/1/11 | 1218.89 | -0.0568 | 164.28 | -0.0506 |
| 7/1/11 | 1292.28 | -0.0215 | 173.03 | 0.0600 |
| 6/1/11 | 1320.64 | -0.0183 | 163.23 | 0.0156 |
| 5/2/11 | 1345.2 | -0.0135 | 160.73 | -0.0053 |
| 4/1/11 | 1363.61 | 0.0285 | 161.58 | 0.0460 |
| 3/1/11 | 1325.83 | -0.0010 | 154.47 | 0.0074 |
| 2/1/11 | 1327.22 | 0.0320 | 153.34 | 0.0032 |
| 1/3/11 | 1286.12 | 0.0226 | 152.85 | 0.1038 |
| 12/1/10 | 1257.64 | 0.0653 | 138.47 | 0.0375 |
| 11/1/10 | 1180.55 | -0.0023 | 133.47 | -0.0105 |
| 10/1/10 | 1183.26 | 0.0369 | 134.89 | 0.0706 |
| 9/1/10 | 1141.2 | 0.0876 | 126 | 0.0894 |
| 8/2/10 | 1049.33 | -0.0474 | 115.66 | -0.0363 |
| 7/1/10 | 1101.6 | 0.0688 | 120.02 | 0.0399 |
| 6/1/10 | 1030.71 | -0.0539 | 115.42 | -0.0142 |
| 5/3/10 | 1089.41 | -0.0820 | 117.08 | -0.0240 |
| 4/1/10 | 1186.69 | 0.0148 | 119.96 | 0.0059 |
| 3/1/10 | 1169.43 | 0.0588 | 119.26 | 0.0085 |
| 2/1/10 | 1104.49 | 0.0285 | 118.25 | 0.0436 |
| 1/4/10 | 1073.87 | -0.0370 | 113.31 | -0.0650 |
| 12/1/09 | 1115.1 | 0.0178 | 121.19 | 0.0361 |
| 11/2/09 | 1095.63 | 0.0574 | 116.97 | 0.0523 |
| 10/1/09 | 1036.19 | -0.0198 | 111.16 | 0.0083 |
| 9/1/09 | 1057.08 | 0.0357 | 110.24 | 0.0132 |
| 8/3/09 | 1020.62 | 0.0336 | 108.8 | 0.0056 |
| 7/1/09 | 987.48 | 0.0741 | 108.19 | 0.1294 |
| 6/1/09 | 919.32 | 0.0002 | 95.79 | -0.0175 |
| 5/1/09 | 919.14 | 0.0531 | 97.5 | 0.0351 |
| 4/1/09 | 872.81 | 0.0000 | 94.19 | 0.0000 |
| 3/10/09 | 797.87 | | 88.42 | | http://www.youtube.com/watch?v=ZmlE1ktg-Wg | | | | To load add-ins in 2007 |
| REGRESSION: IBM vs. Market |
| Regression analysis is performed by following this command path: Tools => Data Analysis => Regression. |
| This will yield the Regression input box. If Data Analysis is not an option in your Tools menu, you will |
| have to load that program. Click on the Add-Ins option in the Tools menu. When the Add-Ins box |
| appears, click on Analysis ToolPak and a check mark will appear next to the Analysis ToolPak. Then, click |
| OK and you will now be able to access Data Analysis. From this point, you must designate the Y input |
| range (stock returns) and the X input range (market returns). You can have the summary output placed in |
| a new worksheet, or you can have it shown directly in the worksheet, as we did here. The filled-in |
| For our example, the Y range is: $E$24: $E$82 |
| The X range is $C$24:$C$82 |
| Place the output beginning at A100 |
| SUMMARY OUTPUT |
| Regression Statistics |
| Multiple R | 0.635349185 |
| R Square | 0.4036685869 |
| Adjusted R Square | 0.3932066322 |
| Standard Error | 0.0435319823 |
| Observations | 59 |
| ANOVA |
| | df | SS | MS | F | Significance F |
| Regression | 1 | 0.0731187925 | 0.0731187925 | 38.584432992 | 0.0000000646 |
| Residual | 57 | 0.1080169086 | 0.0018950335 |
| Total | 58 | 0.181135701 |
| | Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% |
| Intercept | 0.0141666673 | 0.0056674394 | 2.4996592493 | 0.0153343881 | 0.0028178158 | 0.0255155189 | 0.0028178158 | 0.0255155189 |
| X Variable 1 | 0.6433210043 | 0.1035670653 | 6.2116369012 | 0.0000000646 | 0.435931535 | 0.8507104736 | 0.435931535 | 0.8507104736 |
| the equation is highly significant with a level of .000000064595 |
| A significance level of .10 or lower is acceptable |
| IBM's beta is 0.643 with a signficance level of .0000000646 |
| You also have confidence intervals. These ranges can be helpful when trying to reconcile |
| the calculated beta with published betas. |
cherodjones
SampleRegressionSpreadSheetforBeta2.xls
