Step-by-Step Beta Estimation
in Excel
Introduction
This exercise is used to compute the beta for a stock relative to the S&P 500 over the most recent ten year period. Beta is estimated using two Excel functions; regression and ‘=slope( )’.
Data Input
1. Select a stock for your analysis.
Steps:
– Go to http://finance.yahoo.com/
– Enter your stock ticker and dates – select monthly.
– Download 10 years of the most recent monthly adjusted closing prices
– Scroll to bottom of the output and select ‘Download to Excel Spreadsheet’
– Keep the date and adjusted-close columns.
– Repeat this process for the S&P 500 with your security for the same ten year period, yahoo ticker ^GSPC.
– Import this data (Dates, Adj. Closing Prices) into the spreadsheet – cells {A12:C132}.
with the most recent monthly data in row A12.
Note that there is an error in the calculation of the monthly returns – please fix it.
Beta Estimation using the =slope ( ) function
Steps:
– Go to cell B8 and type =slope(stock returns, S&P returns)
The beta is automatically estimated by dividing the covariance of the stock with the market by the variance of the S&P – the answer will be very close to the value in cell B7.
Beta Estimation using the Regression function
Steps:
– Go to cell I3
– Select the ‘Data’ tab then choose ‘Data Analysis’
– Choose ‘Regression’ and hit ‘OK’
– Enter the stock’s monthly returns in the ‘Input Y Range’ field
– Enter the S&P’s monthly returns in the ‘Input X Range’ field
– Select ‘Output Range’ and enter I3 in the corresponding box.
Note: The X Variable is the stock’s beta.
Beta Estimation using Excel Chart/Data Plot
Steps:
– Highlight both columns of return data: Both S&P and stock (including label cells)
– Choose ‘Insert’ tab, ‘Scatter Chart’: An X-Y scatter plot is created
– Place the cursor over any X-Y data point on the chart, right-mouse button click
– From mini-option window, select “Add Trendline”
– Select option: “Linear”, “Display Equation”, “Display R-square”
Note: Make sure the Y axis is stock and X axis is S&P. The slope is the stock’s beta. The R-square is empirically low.
Questions
What is the 10-year beta for your stock?
What is the 5-year beta for your stock?
If they are different, why?
Is your stock’s 10-year beta statistically significant?
What is the beta of the risk-free asset?
What is the beta of the market?
Which beta, the 5 or 10 year is more informative or more useful for investing and why?
How does this beta differ from the CAPM beta we have discussed? Does this matter for the purposes of the calculation? (Think about the risk premium)
