Get documents about "Stock_Beta_Excel
Shared by: xuyuzhu
-
Stats
- views:
- 2
- posted:
- 11/18/2012
- language:
- Latin
- pages:
- 4
Document Sample
Calculating Stock Beta in Excel
Date Adj Close Adj Close Returns (%) Method 1
IBM S&P500 (^GSPC) IBM S&P500 (^GSPC)
1/3/2011 145.61 1271.87 Beta = COVAR(range of % ch
1/4/2011 145.77 1270.2 0.110% -0.131% equity,range of % change of
1/5/2011 145.19 1276.56 -0.398% 0.501% of % change of index)
1/6/2011 146.78 1273.85 1.095% -0.212%
1/7/2011 146.05 1271.5 -0.497% -0.184%
1/10/2011 145.77 1269.75 -0.192% -0.138% Beta
1/11/2011 145.41 1274.48 -0.247% 0.373%
1/12/2011 147.21 1285.96 1.238% 0.901%
1/13/2011 146.93 1283.76 -0.190% -0.171% Method 2
1/14/2011 148.1 1293.24 0.796% 0.738%
1/18/2011 148.74 1295.02 0.432% 0.138% Beta = =SLOPE(range of % c
1/19/2011 153.72 1281.92 3.348% -1.012% range of % change of index)
1/20/2011 153.82 1280.26 0.065% -0.129%
1/21/2011 153.53 1283.35 -0.189% 0.241%
1/24/2011 157.61 1290.84 2.657% 0.584%
1/25/2011 159.39 1291.18 1.129% 0.026% Beta
1/26/2011 159 1296.63 -0.245% 0.422%
1/27/2011 159.03 1299.54 0.019% 0.224%
1/28/2011 157.19 1276.34 -1.157% -1.785% Developed By:
1/31/2011 159.95 1286.12 1.756% 0.766%
2/1/2011 161.49 1307.59 0.963% 1.669%
2/2/2011 161.23 1304.03 -0.161% -0.272%
2/3/2011 161.46 1307.1 0.143% 0.235%
2/4/2011 161.92 1310.87 0.285% 0.288%
2/7/2011 162.73 1319.05 0.500% 0.624%
2/8/2011 164.59 1324.57 1.143% 0.418%
2/9/2011 163.21 1320.88 -0.838% -0.279%
2/10/2011 162.65 1321.87 -0.343% 0.075%
2/11/2011 162.41 1329.15 -0.148% 0.551%
2/14/2011 161.79 1332.32 -0.382% 0.238%
2/15/2011 161.41 1328.01 -0.235% -0.323%
2/16/2011 161.97 1336.32 0.347% 0.626%
2/17/2011 162.8 1340.43 0.512% 0.308%
2/18/2011 163.39 1343.01 0.362% 0.192%
2/22/2011 160.53 1315.44 -1.750% -2.053%
2/23/2011 158.78 1307.4 -1.090% -0.611%
2/24/2011 159.36 1306.1 0.365% -0.099%
2/25/2011 160.86 1319.88 0.941% 1.055%
2/28/2011 160.46 1327.22 -0.249% 0.556%
3/1/2011 158.57 1306.33 -1.178% -1.574%
3/2/2011 158.76 1308.44 0.120% 0.162%
3/3/2011 162.05 1330.97 2.072% 1.722%
3/4/2011 160.41 1321.15 -1.012% -0.738%
3/7/2011 158.53 1310.13 -1.172% -0.834%
3/8/2011 160.86 1321.82 1.470% 0.892%
3/9/2011 164.41 1320.02 2.207% -0.136%
3/10/2011 160.6 1295.11 -2.317% -1.887%
3/11/2011 161.01 1304.28 0.255% 0.708%
3/14/2011 159.97 1296.39 -0.646% -0.605%
3/15/2011 157.63 1281.87 -1.463% -1.120%
3/16/2011 151.66 1256.88 -3.787% -1.949%
3/17/2011 152.83 1273.72 0.771% 1.340%
3/18/2011 154.52 1279.21 1.106% 0.431%
3/21/2011 156.3 1298.38 1.152% 1.499%
3/22/2011 156.61 1293.77 0.198% -0.355%
3/23/2011 158.13 1297.54 0.971% 0.291%
3/24/2011 158.64 1309.66 0.323% 0.934%
3/25/2011 160.76 1313.8 1.336% 0.316%
3/28/2011 159.96 1310.19 -0.498% -0.275%
3/29/2011 161.45 1319.44 0.931% 0.706%
3/30/2011 162.17 1328.26 0.446% 0.668%
3/31/2011 161.64 1325.83 -0.327% -0.183%
4/1/2011 162.83 1332.41 0.736% 0.496%
4/4/2011 162.81 1332.87 -0.012% 0.035%
4/5/2011 162.55 1332.63 -0.160% -0.018%
4/6/2011 162.6 1335.54 0.031% 0.218%
4/7/2011 162.94 1333.51 0.209% -0.152%
4/8/2011 162.61 1328.17 -0.203% -0.400%
4/11/2011 162.51 1324.46 -0.061% -0.279%
4/12/2011 161.82 1314.16 -0.425% -0.778%
4/13/2011 162.51 1314.41 0.426% 0.019%
4/14/2011 163.52 1314.52 0.622% 0.008%
4/15/2011 164.75 1319.68 0.752% 0.393%
4/18/2011 164.48 1305.14 -0.164% -1.102%
4/19/2011 163.95 1312.62 -0.322% 0.573%
4/20/2011 163.31 1330.36 -0.390% 1.351%
4/21/2011 166.8 1337.38 2.137% 0.528%
4/25/2011 166.2 1335.25 -0.360% -0.159%
4/26/2011 167.01 1347.24 0.487% 0.898%
4/27/2011 168.88 1355.66 1.120% 0.625%
4/28/2011 169.28 1360.48 0.237% 0.356%
4/29/2011 169.08 1363.61 -0.118% 0.230%
5/2/2011 170.64 1361.22 0.923% -0.175%
5/3/2011 171.35 1356.62 0.416% -0.338%
5/4/2011 169.12 1347.32 -1.301% -0.686%
5/5/2011 166.98 1335.1 -1.265% -0.907%
5/6/2011 168.16 1340.2 0.707% 0.382%
5/9/2011 168.37 1346.29 0.125% 0.454%
5/10/2011 169.64 1357.16 0.754% 0.807%
5/11/2011 168.77 1342.08 -0.513% -1.111%
5/12/2011 171.49 1348.65 1.612% 0.490%
5/13/2011 169.18 1337.77 -1.347% -0.807%
5/16/2011 168.13 1329.47 -0.621% -0.620%
5/17/2011 169.76 1328.98 0.969% -0.037%
5/18/2011 169.7 1340.68 -0.035% 0.880%
5/19/2011 169.85 1343.6 0.088% 0.218%
5/20/2011 169.42 1333.27 -0.253% -0.769%
5/23/2011 167.53 1317.37 -1.116% -1.193%
5/24/2011 167.26 1316.28 -0.161% -0.083%
5/25/2011 167.02 1320.47 -0.143% 0.318%
5/26/2011 166.46 1325.69 -0.335% 0.395%
5/27/2011 166.77 1331.1 0.186% 0.408%
5/31/2011 168.2 1345.2 0.857% 1.059%
6/1/2011 165.84 1314.55 -1.403% -2.278%
6/2/2011 165.37 1312.94 -0.283% -0.122%
6/3/2011 164.33 1300.16 -0.629% -0.973%
6/6/2011 164.04 1286.17 -0.176% -1.076%
6/7/2011 162.98 1284.94 -0.646% -0.096%
6/8/2011 163.63 1279.56 0.399% -0.419%
6/9/2011 164.13 1289 0.306% 0.738%
6/10/2011 162.47 1270.98 -1.011% -1.398%
6/13/2011 162.46 1271.83 -0.006% 0.067%
6/14/2011 163.41 1287.87 0.585% 1.261%
6/15/2011 161.63 1265.42 -1.089% -1.743%
6/16/2011 161.96 1267.64 0.204% 0.175%
6/17/2011 163.73 1271.5 1.093% 0.305%
6/20/2011 164.3 1278.36 0.348% 0.540%
6/21/2011 165.5 1295.52 0.730% 1.342%
6/22/2011 164.96 1287.14 -0.326% -0.647%
6/23/2011 165.4 1283.5 0.267% -0.283%
6/24/2011 164.35 1268.45 -0.635% -1.173%
6/27/2011 166.89 1280.1 1.545% 0.918%
6/28/2011 169.27 1296.67 1.426% 1.294%
6/29/2011 169.8 1307.41 0.313% 0.828%
6/30/2011 170.81 1320.64 0.595% 1.012%
7/1/2011 173.78 1339.67 1.739% 1.441%
7/5/2011 174.67 1337.88 0.512% -0.134%
7/6/2011 176.94 1339.22 1.300% 0.100%
7/7/2011 175.71 1353.22 -0.695% 1.045%
7/8/2011 175.72 1343.8 0.006% -0.696%
7/11/2011 174.23 1319.49 -0.848% -1.809%
7/12/2011 173.3 1313.64 -0.534% -0.443%
7/13/2011 173.56 1317.72 0.150% 0.311%
7/14/2011 173.47 1308.87 -0.052% -0.672%
7/15/2011 174.78 1316.14 0.755% 0.555%
7/18/2011 174.52 1305.44 -0.149% -0.813%
7/19/2011 184.41 1326.73 5.667% 1.631%
7/20/2011 182.85 1325.84 -0.846% -0.067%
7/21/2011 184.1 1343.8 0.684% 1.355%
7/22/2011 184.38 1345.02 0.152% 0.091%
7/25/2011 182.9 1337.43 -0.803% -0.564%
7/26/2011 182.14 1331.94 -0.416% -0.410%
7/27/2011 180.56 1304.89 -0.867% -2.031%
7/28/2011 181.01 1300.67 0.249% -0.323%
7/29/2011 181.06 1292.28 0.028% -0.645%
Beta = COVAR(range of % change of
equity,range of % change of index)/VAR(range
of % change of index)
0.8
Beta = =SLOPE(range of % change of equity,
range of % change of index)
0.8
Developed By:
http://financetrain.com
free financial learning and resources for all
