Spreadsheet: 82612d1282667342-form-with-range-change-based-on-dates-historical-mpt-stats-for-actual-asf-quarterly-performance-history
Sheet: Beta Calculation

You can use this spreadsheet to calculate stock beta values from price histories that are easily downloaded over the Web.  The data in this worksheet should be sorted in ascending order - with the oldest price information located in Month 1.  This spreadsheet can accommodate up to 100 values when it calculates the beta of a stock.  The S&P 500 Index ($INX) information for the same time period must also be entered into the worksheet.  All of the blue colored values are inputs.  For example, you can enter the stock ticker in cell C22.  The prices of the security are entered in J4:J103, while the S&P values for the same time period are entered in cells I4:I103.  This workbook also contains two charts.  The Stock Chart shows the security characteristic line (SCL) and the S&P chart is shown on the second chart tab.  The stock beta is the slope of the SCL while the stock's alpha is the intercept.
 |  |  |  |  |  |  | Monthly Stock Prices
 |  |  |  |  |  |  | Month | S&P 500 | MSFT |  | Returns
 |  |  |  |  |  |  | 1 | 1147.39 | 30.155 |  | S&P 500 | MSFT
 |  |  |  |  |  |  | 2 | 1076.92 | 26.13 |  | -0.06141765223681575 | -0.1334770353175262
 |  |  |  |  |  |  | 3 | 1067.14 | 25.455 |  | -0.009081454518441456 | -0.02583237657864526
 |  |  |  |  |  |  | 4 | 989.82 | 27.35 |  | -0.07245534793935195 | 0.07444509919465736
 |  |  |  |  |  |  | 5 | 911.62 | 23.99 |  | -0.07900426340142656 | -0.1228519195612433
 |  |  |  |  |  |  | 6 | 916.07 | 24.54 |  | 0.004881419889866442 | 0.02292621925802421
 |  |  |  |  |  |  | 7 | 815.28 | 21.87 |  | -0.1100243431178841 | -0.10880195599022
 |  |  |  |  |  |  | 8 | 885.76 | 26.735 |  | 0.08644882739672262 | 0.2224508459076359
 |  |  |  |  |  |  | 9 | 936.31 | 28.84 |  | 0.05706963511560689 | 0.07873573966710307
 |  |  |  |  |  |  | 10 | 879.82 | 25.85 |  | -0.06033258215761863 | -0.1036754507628293
 |  |  |  |  |  |  | 11 | 855.7 | 23.73 |  | -0.02741469846104885 | -0.08201160541586076
 |  |  |  |  |  |  | 12 | 841.15 | 23.7 |  | -0.01700362276498781 | -0.001264222503160604
 |  |  |  |  |  |  | 13 | 848.18 | 24.21 |  | 0.008357605658919305 | 0.02151898734177222
 |  |  |  |  |  |  | 14 | 916.92 | 25.57 |  | 0.08104411799382208 | 0.05617513424204872
 |  |  |  |  |  |  | 15 | 963.59 | 24.61 |  | 0.05089866073376093 | -0.03754399687133363
 |  |  |  |  |  |  | 16 | 974.5 | 25.64 |  | 0.01132224286262826 | 0.04185290532303946
 |  |  |  |  |  |  | 17 | 990.31 | 26.41 |  | 0.01622370446382755 | 0.0300312012480499
 |  |  |  |  |  |  | 18 | 1008.01 | 26.52 |  | 0.01787319122295044 | 0.004165088981446401
Stock Ticker: |  | MSFT |  |  |  |  | 19 | 995.97 | 27.8 |  | -0.01194432594914729 | 0.04826546003016596
 |  |  |  |  |  |  | 20 | 1050.71 | 26.14 |  | 0.0549614948241413 | -0.05971223021582734
 |  |  |  |  |  |  | 21 | 1058.2 | 25.71 |  | 0.007128513100665273 | -0.01644988523335883
 |  |  |  |  |  |  | 22 | 1111.92 | 27.37 |  | 0.05076545076545079 | 0.06456631660832361
Beta |  |  |  | Alpha |  |  | 23 | 1131.13 | 27.65 |  | 0.01727642276422767 | 0.0102301790281329
S&P 500 | MSFT |  |  | S&P 500 | MSFT |  | 24 | 1144.94 | 26.53 |  | 0.0122090299081449 | -0.04050632911392396
1.000000000000001 | 0.9939425558838428 |  |  | 0 | -0.003450433187660459 |  | 25 | 1126.21 | 24.93 |  | -0.01635893583943265 | -0.06030908405578595
 |  |  |  |  |  |  | 26 | 1107.3 | 26.13 |  | -0.01679082941902494 | 0.04813477737665461
Copyright © 2007 Money-zine.com |  |  |  |  |  |  | 27 | 1120.68 | 26.23 |  | 0.01208344622053654 | 0.003827018752391941
 |  |  |  |  |  |  | 28 | 1140.84 | 28.56 |  | 0.01798907805974931 | 0.08882958444529158
 |  |  |  |  |  |  | 29 | 1101.72 | 28.49 |  | -0.03429052277269371 | -0.002450980392156873
 |  |  |  |  |  |  | 30 | 1104.24 | 27.3 |  | 0.002287332534582273 | -0.04176904176904169
 |  |  |  |  |  |  | 31 | 1114.58 | 27.65 |  | 0.009363906397159963 | 0.01282051282051274
 |  |  |  |  |  |  | 32 | 1130.2 | 27.97 |  | 0.01401424751924502 | 0.0115732368896926
 |  |  |  |  |  |  | 33 | 1173.82 | 26.81 |  | 0.03859493894885851 | -0.0414730067929925
 |  |  |  |  |  |  | 34 | 1211.92 | 26.72 |  | 0.03245812816275079 | -0.00335695635956732
 |  |  |  |  |  |  | 35 | 1181.27 | 26.28 |  | -0.02529044821440367 | -0.01646706586826339
 |  |  |  |  |  |  | 36 | 1203.6 | 25.16 |  | 0.01890338364641439 | -0.04261796042617964
 |  |  |  |  |  |  | 37 | 1180.59 | 24.17 |  | -0.01911764705882352 | -0.03934817170111281
 |  |  |  |  |  |  | 38 | 1156.85 | 25.3 |  | -0.02010858977291016 | 0.0467521721141911
 |  |  |  |  |  |  | 39 | 1191.5 | 25.8 |  | 0.0299520248951896 | 0.01976284584980237
 |  |  |  |  |  |  | 40 | 1191.33 | 24.84 |  | -0.0001426772975241903 | -0.03720930232558143
 |  |  |  |  |  |  | 41 | 1234.18 | 25.61 |  | 0.03596820360437506 | 0.03099838969404185
 |  |  |  |  |  |  | 42 | 1220.33 | 27.38 |  | -0.01122202596055692 | 0.06911362748926199
 |  |  |  |  |  |  | 43 | 1228.81 | 25.73 |  | 0.006948940040808649 | -0.06026296566837103
 |  |  |  |  |  |  | 44 | 1207.01 | 25.7 |  | -0.01774074104214643 | -0.001165954139137238
 |  |  |  |  |  |  | 45 | 1249.48 | 27.68 |  | 0.03518612107604745 | 0.07704280155642025
 |  |  |  |  |  |  | 46 | 1248.29 | 26.15 |  | -0.0009523961968179199 | -0.05527456647398848
 |  |  |  |  |  |  | 47 | 1280.08 | 28.15 |  | 0.025466838635253 | 0.07648183556405354
 |  |  |  |  |  |  | 48 | 1280.66 | 26.87 |  | 0.0004530966814575297 | -0.04547069271758428
 |  |  |  |  |  |  | 49 | 1294.87 | 27.21 |  | 0.01109584120687755 | 0.01265351693338295
 |  |  |  |  |  |  | 50 | 1310.61 | 24.15 |  | 0.01215566041378672 | -0.1124586549062845
 |  |  |  |  |  |  | 51 | 1270.09 | 22.65 |  | -0.03091690129023889 | -0.06211180124223603
 |  |  |  |  |  |  | 52 | 1270.2 | 23.3 |  | 8.660803565111712e-05 | 0.02869757174392945
 |  |  |  |  |  |  | 53 | 1276.66 | 24.06 |  | 0.005085813257754712 | 0.03261802575107287
 |  |  |  |  |  |  | 54 | 1303.82 | 25.7 |  | 0.02127426252878594 | 0.06816292601828765
 |  |  |  |  |  |  | 55 | 1335.85 | 27.35 |  | 0.02456627448574188 | 0.06420233463035029
 |  |  |  |  |  |  | 56 | 1377.94 | 28.71 |  | 0.03150802859602511 | 0.04972577696526506
 |  |  |  |  |  |  | 57 | 1400.63 | 29.36 |  | 0.01646660957661441 | 0.02264019505398811
 |  |  |  |  |  |  | 58 | 1418.3 | 29.86 |  | 0.01261575148326099 | 0.0170299727520436
 |  |  |  |  |  |  | 59 | 1438.24 | 30.86 |  | 0.01405908481985479 | 0.03348961821835231
 |  |  |  |  |  |  | 60 | 1406.82 | 28.17 |  | -0.02184614528868622 | -0.08716785482825658
 |  |  |  |  |  |  | 61 | 1386.95 | 27.33 |  | -0.0141240528283646 | -0.02981895633652834
 |  |  |  |  |  |  | 62
 |  |  |  |  |  |  | 63
 |  |  |  |  |  |  | 64
 |  |  |  |  |  |  | 65
 |  |  |  |  |  |  | 66
 |  |  |  |  |  |  | 67
 |  |  |  |  |  |  | 68
 |  |  |  |  |  |  | 69
 |  |  |  |  |  |  | 70
 |  |  |  |  |  |  | 71
 |  |  |  |  |  |  | 72
 |  |  |  |  |  |  | 73
 |  |  |  |  |  |  | 74
 |  |  |  |  |  |  | 75
 |  |  |  |  |  |  | 76
 |  |  |  |  |  |  | 77
 |  |  |  |  |  |  | 78
 |  |  |  |  |  |  | 79
 |  |  |  |  |  |  | 80
 |  |  |  |  |  |  | 81
 |  |  |  |  |  |  | 82
 |  |  |  |  |  |  | 83
 |  |  |  |  |  |  | 84
 |  |  |  |  |  |  | 85
 |  |  |  |  |  |  | 86
 |  |  |  |  |  |  | 87
 |  |  |  |  |  |  | 88
 |  |  |  |  |  |  | 89
 |  |  |  |  |  |  | 90
 |  |  |  |  |  |  | 91
 |  |  |  |  |  |  | 92
 |  |  |  |  |  |  | 93
 |  |  |  |  |  |  | 94
 |  |  |  |  |  |  | 95
 |  |  |  |  |  |  | 96
 |  |  |  |  |  |  | 97
 |  |  |  |  |  |  | 98
 |  |  |  |  |  |  | 99
 |  |  |  |  |  |  | 100