Text Adsense

Monday, October 3, 2016

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 CorpPCOMP Index
DateSM Closing PriceSM Daily Return (A)PCOMP Closing PricePCOMP Daily Return (B)
8/18/20166957,952.81
8/19/2016688-1.01%7,930.75-0.28%
8/22/20166951.02%7,981.210.64%
8/23/2016694-0.14%7,935.18-0.58%
8/24/2016690.5-0.50%7,866.13-0.87%
8/25/2016689-0.22%7,854.54-0.15%
8/26/20166900.15%7,845.49-0.12%
8/30/2016687-0.43%7,794.93-0.64%
8/31/2016680-1.02%7,787.37-0.10%
9/1/2016675-0.74%7,773.31-0.18%
9/2/2016689.52.15%7,807.420.44%
9/5/2016689.50.00%7,764.05-0.56%
9/6/2016678.5-1.60%7,719.18-0.58%
9/7/2016666-1.84%7,619.10-1.30%
9/8/2016668.50.38%7,667.070.63%
9/9/2016668.50.00%7,581.79-1.11%
9/13/2016667.5-0.15%7,550.27-0.42%
9/14/2016664-0.52%7,546.01-0.06%
9/15/2016685.53.24%7,708.422.15%
9/16/2016660-3.72%7,553.76-2.01%
9/19/2016655-0.76%7,575.840.29%
9/20/20166681.98%7,671.721.27%
9/21/2016656.5-1.72%7,675.420.05%
9/22/2016668.51.83%7,762.351.13%
9/23/20166690.07%7,723.60-0.50%
9/26/2016661-1.20%7,632.46-1.18%
9/27/2016658.5-0.38%7,557.34-0.98%
9/28/20166630.68%7,586.960.39%
9/29/20166701.06%7,714.861.69%
9/30/2016673.50.52%7,629.73-1.10%
10/3/20166750.22%7,677.730.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

No comments:

Post a Comment