Question

Date	INTEl Adj Close	IBM Adj Close	NYSE Comp Adj Close
6/1/08	15.589097	90.438087	8660.480469
7/1/08	16.104382	97.64843	8438.639648
8/1/08	16.597893	92.879662	8382.080078
9/1/08	13.678322	89.587776	7532.799805
10/1/08	11.706542	71.212181	6061.089844
11/1/08	10.077993	62.503098	5599.299805
12/1/08	10.799025	64.824348	5757.049805
1/1/09	9.502553	70.593544	5195.790039
2/1/09	9.384688	70.886238	4617.029785
3/1/09	11.185633	75.035652	4978.97998
4/1/09	11.743799	79.930115	5513.359863
5/1/09	11.699148	82.307648	6004.069824
6/1/09	12.421227	81.289558	5905.149902
7/1/09	14.447655	91.806938	6424.279785
8/1/09	15.250722	91.900337	6643.240234
9/1/09	14.795033	93.549095	6910.879883
10/1/09	14.447267	94.3312	6739.450195
11/1/09	14.515311	98.820564	7092.359863
12/1/09	15.54012	102.838661	7184.959961
1/1/10	14.778345	96.152985	6883.779785
2/1/10	15.639149	99.900406	7035.040039
3/1/10	17.115253	101.207405	7447.799805
4/1/10	17.537563	101.799255	7474.399902
5/1/10	16.447235	98.847893	6791.569824
6/1/10	15.039574	97.942688	6469.649902
7/1/10	15.928804	101.845139	6998.990234
8/1/10	13.663203	97.66507	6704.149902
9/1/10	14.959158	106.925247	7281.069824
10/1/10	15.621412	114.465996	7513.350098
11/1/10	16.486237	112.76017	7430.939941
12/1/10	16.512817	117.504761	7964.02002
1/1/11	16.850449	129.706772	8139.160156
2/1/11	16.858303	129.610672	8438.549805
3/1/11	15.979691	131.080399	8404.980469
4/1/11	18.331514	137.117188	8671.410156
5/1/11	17.82472	135.790848	8477.280273
6/1/11	17.685526	138.51358	8319.099609
7/1/11	17.821199	146.830017	8079.439941
8/1/11	16.065409	138.80423	7528.390137
9/1/11	17.197369	141.809082	6791.649902
10/1/11	19.776167	149.723816	7563.379883
11/1/11	20.074348	152.456711	7484.5
12/1/11	19.716068	149.715088	7477.029785
1/1/12	21.480347	156.814896	7838.47998
2/1/12	21.854345	160.177521	8113.240234
3/1/12	23.045198	170.544327	8206.929688
4/1/12	23.27467	169.261063	8119.060059
5/1/12	21.176668	157.6707	7463.959961
6/1/12	21.99881	160.530975	7801.839844
7/1/12	21.214611	160.859329	7863.930176
8/1/12	20.496452	159.931824	8014.930176
9/1/12	18.869036	171.000839	8251
10/1/12	18.011353	160.350861	8221.400391
11/1/12	16.295984	156.674515	8260.429688
12/1/12	17.347254	158.58551	8443.509766
1/1/13	17.700596	168.122955	8894.709961
2/1/13	17.565989	166.268494	8868.719727
3/1/13	18.571089	177.33577	9107.040039
4/1/13	20.365269	168.389999	9276.879883
5/1/13	20.645885	172.946045	9302.269531
6/1/13	20.797457	159.631943	9112.69043
7/1/13	20.033535	162.914627	9558.830078
8/1/13	18.866196	152.247971	9270.660156
9/1/13	19.865528	155.451874	9621.240234
10/1/13	21.208967	150.440308	10009.63965
11/1/13	20.662924	150.834839	10183.23047
12/1/13	22.711031	158.303802	10400.32031
1/1/14	21.468752	149.112961	9967.650391
2/1/14	21.661217	156.278259	10425.86035
3/1/14	22.795128	163.346741	10527.76953
4/1/14	23.57233	166.724167	10627.16992
5/1/14	24.128744	156.447678	10756.30957
6/1/14	27.525063	154.721115	10979.41992
7/1/14	30.188488	163.5979	10726.42969
8/1/14	31.105988	164.135635	11046.29004
9/1/14	31.223236	162.985626	10702.92969
10/1/14	30.496902	141.151749	10845
11/1/14	33.402225	139.237122	10955.41016
12/1/14	32.754765	138.69455	10839.24023
1/1/15	29.821362	132.530914	10537.21973
2/1/15	30.01091	139.991272	11062.79004
3/1/15	28.426231	139.719696	10899.19043
4/1/15	29.589828	149.112686	11049.74023
5/1/15	31.326124	147.685028	11056.29981
6/1/15	27.853502	143.770752	10805.2002
7/1/15	26.507521	143.178543	10882.28027
8/1/15	26.132116	130.715927	10176.5
9/1/15	27.826382	129.198746	9799.69043
10/1/15	31.260826	124.840721	10460.95996
11/1/15	32.100979	124.252518	10409.58984
12/1/15	32.029583	123.798798	10143.41992
1/1/16	28.840574	112.257324	9632.700195
2/1/16	27.511044	117.870628	9559.530273
3/1/16	30.341854	137.631485	10207.37012
4/1/16	28.400352	132.624237	10436.91992
5/1/16	29.629032	139.712555	10441
6/1/16	31.029655	140.598862	10489.75977
7/1/16	32.978466	148.787659	10785.50977
8/1/16	33.952873	147.175842	10764.75
9/1/16	35.983185	148.418915	10721.74023
10/1/16	33.23798	143.597733	10481.88965
11/1/16	33.075935	151.567627	10838.45996
12/1/16	34.834225	156.49704	11056.90039
1/1/17	35.36245	164.539215	11222.95996
2/1/17	34.766991	169.536102	11512.38965
3/1/17	34.889442	165.479111	11492.84961
4/1/17	34.966831	152.317963	11536.07031
5/1/17	34.928135	145.03894	11598.03027
6/1/17	32.878044	147.607239	11761.7002
7/1/17	34.563847	138.817764	11967.66992
8/1/17	34.174065	137.24411	11875.69043
9/1/17	37.385208	140.682037	12209.16016
10/1/17	44.660011	149.389816	12341.00977
11/1/17	44.02187	144.599564	12627.79981
12/1/17	45.585854	150.25621	12808.83984
1/1/18	47.541225	160.324219	13367.95996
2/1/18	48.676922	152.616516	12652.54981
3/1/18	51.781151	151.745483	12452.05957
4/1/18	51.323788	143.368484	12515.36035
5/1/18	54.883247	139.75856	12527.13965
6/1/18	55.049999	146.139999	12832.07031

Calculate the monthly return of the stocks of IBM and INTEL, as well as NYSE Composite, each, from June 1, 2008 to June 1, 2018. (Yahoo Finance provides monthly returns! Go to Historical Data provided in the middle bar menu of Yahoo Finance page for each company and use the prices given under “adjusted close”, which adjusts for splits and dividends!)

1. Calculate the beta of IBM and the beta of INTEL.

2. How do you interpret the beta of each stock?

Answer 1

Step 1: Monthly Return
Return_n = (V_n - V_(n-1)) / V_(n-1) Refer Column E, F & G

Step 2: Calculate Beta

Method 1 : Use Excel Formula, "=SLOPE(Stock Return, NYSE Return)"

Beta of Intel: "=SLOPE(E3:E122,G3:G122)
Beta of IBM: "=SLOPE(F3:F122,G3:G122)

Method 2 : Use Excel Formula, "=COVARIANCE.P(Stock Return, NYSE Return)/VAR.P(NYSE Return)"

Beta of Intel: "=COVARIANCE.S(E3:E122,G3:G122)/VAR.S(G3:G122)"
Beta of IBM: "=COVARIANCE.S(F3:F122,G3:G122)/VAR.S(G3:G122)"

Formulas