Mathematical Proof that a Stock's Beta is Equal to the Slope of the Linear Regression using Excel

For this exercise let's say the stock we are trying to study is SM Investments Corp (PSE: SM) and the benchmark is Philippine Composite PSEi Index (PCOMP)

From our readings we've learned that SM's Beta is equal to the Covariance of SM's daily returns vs PCOMP's daily returns divided by the PCOMPs stadard deviation squared.

In addition we've also learned that SM's beta can be derived by getting the slope of the linear regression of the daily returns between a SM and the PCOMP Index.

Let's find out if that is indeed true.

Below is the 30-day time series data between SM and PCOMP from 18-Aug-16 to 3-Oct-16.

Data:

Stock:	SM Investments Corp		PCOMP Index
Date	SM Closing Price	SM Daily Return (A)	PCOMP Closing Price	PCOMP Daily Return (B)
8/18/2016	695		7,952.81
8/19/2016	688	-1.01%	7,930.75	-0.28%
8/22/2016	695	1.02%	7,981.21	0.64%
8/23/2016	694	-0.14%	7,935.18	-0.58%
8/24/2016	690.5	-0.50%	7,866.13	-0.87%
8/25/2016	689	-0.22%	7,854.54	-0.15%
8/26/2016	690	0.15%	7,845.49	-0.12%
8/30/2016	687	-0.43%	7,794.93	-0.64%
8/31/2016	680	-1.02%	7,787.37	-0.10%
9/1/2016	675	-0.74%	7,773.31	-0.18%
9/2/2016	689.5	2.15%	7,807.42	0.44%
9/5/2016	689.5	0.00%	7,764.05	-0.56%
9/6/2016	678.5	-1.60%	7,719.18	-0.58%
9/7/2016	666	-1.84%	7,619.10	-1.30%
9/8/2016	668.5	0.38%	7,667.07	0.63%
9/9/2016	668.5	0.00%	7,581.79	-1.11%
9/13/2016	667.5	-0.15%	7,550.27	-0.42%
9/14/2016	664	-0.52%	7,546.01	-0.06%
9/15/2016	685.5	3.24%	7,708.42	2.15%
9/16/2016	660	-3.72%	7,553.76	-2.01%
9/19/2016	655	-0.76%	7,575.84	0.29%
9/20/2016	668	1.98%	7,671.72	1.27%
9/21/2016	656.5	-1.72%	7,675.42	0.05%
9/22/2016	668.5	1.83%	7,762.35	1.13%
9/23/2016	669	0.07%	7,723.60	-0.50%
9/26/2016	661	-1.20%	7,632.46	-1.18%
9/27/2016	658.5	-0.38%	7,557.34	-0.98%
9/28/2016	663	0.68%	7,586.96	0.39%
9/29/2016	670	1.06%	7,714.86	1.69%
9/30/2016	673.5	0.52%	7,629.73	-1.10%
10/3/2016	675	0.22%	7,677.73	0.63%

Computation:

(you can just copy paste the data above to excel and compute for the numbers below using excel functions)

Let:

SM Daily Return = A

PCOMP Daily Return = B

EXCEL FUNCTIONS:

CovAB = COVARIANCE.S(ArrayA,ArrayB)

SigmaB = STDEV.S(ArrayB)

SlopeAB = SLOPE(ArrayA,ArrayB)

NUMBERS:

CovAB = 0.0000976707587198856

SigmaB = 0.009257773521476

SigmaB^2 = 0.0000857063705749421

Beta of SM = CovAB / SigmaB^2

Beta of SM = 1.13959741924297

SlopeAB = 1.13959741924297

CONCLUSION:

Therefore SM's Beta computed using the formula CovAB/SigmaB^2 is equal to the slope of the linear regression between A and B.

Visual representation of the regression:

Ain't numbers pretty sweet? hehe

Cheers,
Filipino Investor