Estimating Regression Beta Using Excel Data Analysis Function

Note: Make sure that Data Analysis Add-on is installed with Excel. If not, follow the tutorial Video on YouTube.

Step 1: Getting historical data

Retrieve historical data of scrip price values and benchmark index value for a certain period of time. More the number of observations better it is, as it reduces standard error.

For this tutorial, Monthly closing price for share of Tata Power Ltd. On BSE and Closing value of SENSEX, the BSE Benchmark Index is considered from April 2001 to June 2011, comprising of 122 observations each.

Step 2: Calculate monthly returns on scrip and Index.

Monthly returns for both scrip as well as index are to be calculated.

Formula:

Monthly Return for Month = (Closing Price of the current month – Closing Price of previous month) / Closing Price of previous month

The raw data table in Excel should look like as below,

Scrip: Tata Power Ltd.
Month	Sensex Close	Closing Price of Scrip	Monthly Market Return (X)	Monthly Scrip Return (Y)
Apr-01	3519.16	121.4
May-01	3631.91	146.3	3.20%	20.51%
Jun-01	3456.78	130.05	-4.82%	-11.11%
Jul-01	3329.28	127.4	-3.69%	-2.04%
Aug-01	3244.95	116.85	-2.53%	-8.28%
Sep-01	2811.6	100.3	-13.35%	-14.16%
Oct-01	2989.35	104.95	6.32%	4.64%
Nov-01	3287.56	122.95	9.98%	17.15%
Dec-01	3262.33	119.55	-0.77%	-2.77%
Jan-02	3311.03	115.45	1.49%	-3.43%
Feb-02	3562.31	119.3	7.59%	3.33%
Mar-02	3469.35	113.5	-2.61%	-4.86%
Apr-02	3338.16	111.9	-3.78%	-1.41%
May-02	3125.73	114.65	-6.36%	2.46%
Jun-02	3244.7	130.2	3.81%	13.56%
Jul-02	2987.65	103.85	-7.92%	-20.24%
Aug-02	3181.23	102.2	6.48%	-1.59%
Sep-02	2991.36	95.65	-5.97%	-6.41%

Step 3: Run Regression

Method 1: Using Data Analysis Tool

Go to Data Tab, Click ‘Data analysis’

Select Function ‘Regression’

Select market Return as ‘Input Y Range’ and Scrip Return as ‘Input X Range’. To obtain Regression Results on different worksheet/workbook, select appropriate option in Output Options section.

For Tata Power and Sensex Regression, Monthly Scrip Return (Green) is ‘Range Y input’ and Monthly Market Return (Blue) is ‘Range X Input’ as shown below.

Click ‘Ok’ to receive results as shown below,

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.76592712
R Square	0.586644353
Adjusted R Square	0.583199723
Standard Error	0.076757061
Observations	122
ANOVA
	df	SS	MS	F	Significance F
Regression	1	1.003388077	1.003388	170.3069	8.99945E-25
Residual	120	0.706997563	0.005892
Total	121	1.71038564
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	0.006620394	0.00710666	0.931576	0.353426	-0.007450298	0.020691086	-0.007450298	0.020691086
X Variable 1	1.188147579	0.091044613	13.05017	9E-25	1.007885583	1.368409574	1.007885583	1.368409574

X Variable 1 is the ‘Beta’ for the scrip.

For Tata Power Beta works out to be 1.188, while R-square is 58.66%.

Method 2: Using Scatter Plot

One can create a regression Beta in Excel using the chart functions. Since Beta is the slope in a regression where market returns are on the X axis and scrip returns are on the Y axis.

Select both Market return (as X) and scrip return (as Y) columns. Go to “Insert>Charts>Scatter”

The Chart should appear like this,

Select Chart, Go to ‘Layout tab’ and click ‘More Trendline options’.

Set the Checkboxes to show line equation and R-Square value on graph.

Final Output:

Slope of the line (1.1881) is ‘Beta’ of scrip.

Working Excel of the Tutorial can be downloaded from here.

Important Links

1.       YouTube - Excel Statistics 08: Install Excel 2007 Data Analysis Add-in http://www.youtube.com/watch?v=-ubtpQJ1smI

2.       Beta (Finance) (http://en.wikipedia.org/wiki/Beta_(finance))

3.       Linear Regression (http://en.wikipedia.org/wiki/Linear_regression)