Beta is a number that measures the volatility of an investment's price versus the volatility of the investment's overall market. For example, you could find the beta of Stock XYZ in relation to a measure of the overall stock market, such as the Standard & Poor's 500 Index. Asset beta, also known as unlevered beta, measures the beta of a company independently of any debt the company holds on its balance sheet. You can calculate asset beta using an Excel spreadsheet.
You use beta to find an investment's systematic risk, which is the amount of price change that you can ascribe to the overall market in which the investment trades. The other risk component, unsystematic risk, is price movement that is due to the investment alone, irrespective of its market.
Video of the Day
A beta of 1 tells you that the investment moves in lockstep with its market, on a percentage basis. A beta greater than 1 means that the investment is riskier than its market, while a beta below 1 means the investment is less risky. Negative betas mean that the investment's price moves in the opposite direction from market prices.
Preparing the Data in Excel
To calculate beta, you need a time series of prices for both the investment and the market. For example, you might set up columns showing the closing prices of Stock XYZ and the S&P 500 over a set date range. This is information you can download from sources on the internet. Next, you set up columns that calculate the daily change, in percentage terms, of the closing prices for the stock and the index. Now you're ready to calculate beta.
The two functions you need to calculate beta are:
- Covariance: This is the stock's percentage daily price change divided by the index's percentage daily price change. Available as the COVARIANCE.P function in Excel.
- Variance: This is a measure of the index's percentage daily price change relative to its mean. Available as the VARIANCE.P function in Excel.
The formula for beta is:
Beta = Covariance/Variance
You can use the two Excel functions on your two columns of percentage price change data. For COVARIANCE.P, enter the two price change columns as arguments. For VARIANCE.P, just enter the S&P 500 price change column as the single argument. Finally, divide your covariance result by the variance result to get beta.
Calculating Asset Beta
Companies can use a combination of equity (i.e., shares and retained earnings) and debt to finance their operations and investments. Beta makes no distinction between the two sources of financing, but asset beta is attuned to the company's equity alone – it is the company's equity beta. To calculate asset beta, you need to know the company's dollar amount of debt and equity, as well as its tax rate. The unlevered beta formula is:
Asset Beta = Beta / 1 + [(1 - Tax Rate) x (Debt/Equity)]
Example of Asset Beta Calculation
Imagine that Stock XYZ has a covariance with the S&P 500 of 0.9, and that the variance of the S&P 500 is 0.53. The levered beta is 0.9/0.53, or 1.7. This indicates that XYZ is substantially more volatile than the stock index.
To calculate the XYZ's asset beta, refer to the company's tax rate of 20 percent, its debt figure of $40 million and its equity figure of $100 million.
XYZ Asset Beta = 1.7 / 1 + [(1 - 0.20) x ($40M/$100M)] = 1.29
Interpreting the Result
Notice that the asset beta is smaller than the levered beta. This makes sense because debt multiplies an investment's gains and losses. That is why debt is called leverage, because it levers the volatility of an investment higher. The lower value for asset beta reflects the base volatility of the investment without considering debt's contribution to total volatility. This allows easier comparison of the relative volatility of two companies with different debt-to-equity ratios.