Beta is a statistical measure of volatility and is calculated as the Covariance of daily return on stock market indices and the return on daily share prices of a particular company divided by the Variance of the return on daily Stock Market indices. While considering market index a broad based index like S & P 500 should be considered. For the companies, which are not listed in stock exchanges, beta of the similar industry may be considered after transforming it to un-geared beta and then re-gearing it according to the debt equity ratio of the company. The formula for un-gearing and gearing beta is shown below. Ungeared Beta = Industry Beta / [1 + (1–Tax Rate) (Industry Debt Equity Ratio)] Geared Beta = Ungeared Beta/[1 + (1 – tax rate) (Debt Equity Ratio)]
What is Beta?
Beta is another popular measure of the risk of a stock or a stock portfolio. For StockTrak’s purposes, we will only calculate Beta of the stocks (US and some intl) in the open positions. The Beta’s of individual stocks in the portfolio add up according to their weights to create the portfolio beta.
To calculate beta in Excel:
Download historical security prices for the asset whose beta you want to measure.Download historical security prices for the comparison benchmark.Calculate the percent change period to period for both the asset and the benchmark. If using daily data, it’s each day; weekly data, each week, etc.Find the Variance of the asset using =VAR.S(all the percent changes of the asset).Find the Covariance of asset to the benchmark using =COVARIANCE.S(all the percent changes of the asset, all the percent changes of the benchmark).
