What is Beta?
Beta is a risk management tool, widely used in financial modeling. It demonstrates the volatility (riskiness) of an asset or a portfolio in correlation to the market. In reality, most professionals use some benchmark index, for example, S&P 500.
The “textbook” formula for beta is:
It is assumed that the market has a beta of 1. If beta of a security is >1, the security is more volatile (more risky) than the market, however, in case it is <1, the stock is less volatile (less risky). Betas are useful for calculating yields and returns for securities.
Beta in Excel
Here are the steps to calculate Beta in Excel:
1) Retrieve the historical price of a security and the benchmark index in 2 separate columns. You can either export it from online sources or use the =STOCKHISTORY function.
2) Calculate the price change for the security in percentage with the use of this formula:
3) Calculate Beta using the SLOPE function. It works the following way: SLOPE (known_ys; known_xs). Known_ys stand for % of equity change range, and known_xs mean % range of change of index. The returned value is the beta.
Assuming there is a security with a daily change in price calculated in cells L7:L52 and the daily change of an index calculated in cells Q7:Q52, the formula in Excel should look like =SLOPE (L7:L52; Q7:Q52). The returned value is the beta. In this case, the result is 0.36, implying that this particular stock is less volatile than the market.