What is beta and how to calculate it in Excel?

Peering through Yahoo (YHOO) Finance, Google (GOOG) Finance or other financial data feeders, one may see a variable called beta in the midst of other financial data such as stock price or market value.

In finance, the beta of a firm refers to the sensitivity of its share price with respect to an index or benchmark. For example, the hypothetical firm US CORP (USCS): Google Finance provides a beta for this company of 5.48, which means that with respect to the historical variations of the stock compared to the Standard & Poor's 500, US CORP increased on average by 5.48% if the S&P 500 rose by 1%. Conversely, when the S&P 500 is down 1%, US CORP Stock would tend to average a decline of 5.48%.   

Generally, the index of one is selected for the market index, and if the stock has been behaving with more volatility than the market, its beta value will be greater than one. If the opposite is the case, its beta will be a value less than one. A company with a beta of greater than one will tend to amplify market movements (for instance the case for the banking sector), and a business with a beta of less than one will tend to ease market movements.

Beta can be seen as a measure of risk: the higher the beta of a company, the higher the expected return should be in order to compensate for the excess risk caused by volatility. (For related reading, see also: What Volatility Really Means.)

Therefore, from a portfolio management or investment perspective, one wants to analyze any measures of risk associated with a company to gain a better estimation of its expected return.

Incidentally, it is important to differentiate the reasons why the beta value that is provided on Google Finance may be different from the beta on Yahoo Finance or Reuters.

This is because there are several ways to estimate beta. Multiple factors, such as duration of the period taken into account, are included in the computation of the beta, which create various results that could portray a different picture. For example, some calculations base their data on a three-year span, while others may use a five-year time horizon. Those two extra years may be the cause for two vastly different results. Therefore, the idea is to select the same beta methodology when comparing different stocks.  

It's simple to calculate the beta coefficient. The beta coefficient needs a historical series of share prices for the company that you are analyzing. In our example we will use Apple (AAPL) as the stock under analysis and the S&P 500 as our historical index. To get this data, go to:

• Yahoo! Finance –> Historical prices, and download the time series "Adj Close" for the S&P 500 and the firm Apple.

We only provide a small snippet of the data over 750 rows as it is extensive:

Once we have the Excel table, we can reduce the table data to three columns: the first is the date, the second is the Apple stock, and the third is the price of the S&P 500.

There are then two ways to determine beta. The first is to use the formula for beta, which is calculated as the covariance between the return (r_a) of the stock and the return (r_b) of the index divided by the variance of the index (over a period of three years).

To do so, we first add two columns to our spreadsheet; one with the index return r (daily in our case), (column D in Excel), and with the performance of Apple stock (column E in Excel).

At first, we only consider the values ​​of the last three years (about 750 days of trading) and a formula in Excel, to calculate beta.

BETA FORMULA = COVAR (D1: D749; E1: E749) / VAR (E1: E749)                     

The second method is to perform a linear regression, with the dependent variable performance of Apple stock over the last three years as an explanatory variable and the performance of the index over the same period.

Now that we have the results of our regression, the coefficient of the explanatory variable is our beta (the covariance divided by variance).

With Excel, we can pick a cell and enter the formula: "SLOPE" which represents the linear regression applied between the two variables; the first for the series of daily returns of Apple (here: 750 periods), and the second for the daily performance series of the index, which follows the formula:

BETA FORMULA = SLOPE (E1: E749; D1:D749)

Here, we have just computed a beta value for Apple's stock (0.77 in our example, taking daily data and an estimated period of three years, from April 9, 2012 to April 9, 2015).

Many investors found themselves with heavy losing positions as part of the Global Financial Crisis that began in 2007. As part of those collapses, low beta stocks dove down much less than higher beta stocks during periods of market turbulence. This is because their market correlation was much lower, and thus the swings orchestrated through the index were not felt as acutely for those low beta stocks. (For related reading, see: The 2007-08 Financial Crisis in Review.) 

However, there are always exceptions given the industry or sectors of low beta stocks, and so they might have a low beta with the index but a high beta within their sector or industry.

Therefore, incorporating low beta stocks versus higher beta stocks could serve as a form of downside protection in times of adverse market conditions. Low beta stocks are much less volatile; however, another analysis must be done with intra-industry factors in mind.

On the other hand, higher beta stocks are selected by investors that are keen and focused on short-term market swings. They wish to turn this volatility into profit, albeit with higher risks. Such investors would select stocks with higher beta, which offer more ups and downs and entry points for trades than stocks with lower beta and lower volatility.

It is important to follow strict trading strategies and rules and to apply a long-term money management discipline in all beta cases. Employing beta strategies can be useful as part of a broader investment plan to limit downside risk or realize short-term gains, but it's important to remember that it is also subject to the same levels of market volatility as any other trading strategy.