1. Choose an index representative of the market (e.g. S&P 500).
2. Download monthly historical stock and index prices for the past five years into a spreadsheet.
3. Merge adjusted closing prices of the stock and the index.
4. Calculate monthly rates of return for both the stock and the index.
5. Run a linear regression to determine the beta coefficient.
1. Choice of the Market Proxy
The Capital Asset Pricing Model (CAPM) Beta is a measure of risk relative to the market. Determining an appropriate market proxy, however, is not a straightforward choice. Most texts and practitioners choose the S&P 500 index (^GSPC) since it is representative of the overall US market and simple to use. Thankfully, the choice of the index is unlikely to make a significant difference in most cases since stock indexes are market-capitalization weighted, meaning that micro-cap stocks have only a marginal impact on performance.
2. Downloading Historical Prices
Many websites are available for retrieving historical stock prices, the most common one being Yahoo Finance. These same sites usually have a way to download the data into a spreadsheet format. The two primary choices that practitioners must make when pulling historical prices are the time frame and the time interval of the returns. A five-year time frame is a popular choice since it generally represents a business cycle, though three years is also common. Betas calculated using different return intervals are generally not statistically significant from each other. However, monthly returns tend to result in higher beta scores while weekly returns commonly have higher coefficients of determination (denoted by R2).
3. Merge Stock and Index Prices
Stock and index prices are generally downloaded separately on different spreadsheets, which must then be joined on a common file. The resultant worksheet need only three columns: date, adjusted closing price of the stock, and adjusted closing price of the index. (Please note that the dates/rows of the two original spreadsheets may not agree and must therefore be manually aligned in certain cases, though the issue is far less common for weekly and monthly intervals).
4. Rates of Return
The last bit of data wrangling required before estimating beta is to calculate the monthly percentage change [(price(t) – price(t-1)) / price(t-1)] for both the stock and the index. Using the natural logarithm [ln(price(t) / price(t-1))] to calculate percentage change makes little difference.
5. Calculating the Beta Value
Historical beta is found by regressing the index’s returns and the stock’s returns. In practice, this can be done in Excel with the SLOPE function, where the first argument (known y’s) is the range of values for the monthly stock returns and the second argument (known x’s) is the range of values for the monthly index returns. The resultant output is the slope coefficient or beta (denoted by β).
There are many videos and articles available on the internet, which provide helpful walkthroughs on how to calculate beta using Excel. What they often omit, however, is clarification on what to use as a market proxy, how far back you should get historical prices for, and whether to use daily, weekly, or monthly returns. This is likely because there aren’t straightforward answers. That doesn’t mean that there aren’t good places to start. Try calculating beta using different parameters and then read up on the economic rationale behind the different choices.
It is generally helpful to see how a stock’s beta has changed over time, for example, by graphing betas on a rolling basis. Beta estimates can also be improved by using the beta for the industry (using an industry index) or the average betas of several industry competitors. This requires un-levering the raw betas of competitors [βu = βe / (1 + D/E)] and then re-levering them to reflect the company’s own leverage profile [βe = βu * (1 + D/E)].
Roger Shelor and Scott Wright. Stock Returns, Risk and Beta.