Beta
Simply put the beta of a stock measures how many times the historical volatility of a stock is greater than that of a comparable index. Beta is calculated by using regression analysis.

Click Here to Download the Spreadsheet


I have described the mathematical relationships by using the conventional symbols for beta rather than describing the general notations for market returns. To apply the below equations to finance simply replace x with stock return and y with general market index return.

With respect to correlation and variance, the beta of a stock is mathematically described as

Other wise defined as

Equation [7] is the beta calculated when applying a regression between the stock price return and the index return. After plotting the returns on a chart a best fit line is drawn. The best fit line is drawn so that the distance of the scatter points to the best fit line is minimized. The best fit line describes a linear relationship between the amount of times the stock rises relative to the index in percentage terms. Most regression charts also have an alpha associated with them. The alpha is the Y intercept. In financial terms this means that even if the underlying index will not return anything, the stock will still return an amount equal to the Y intercept. Hedge funds which have a large alpha are more desirable than ones with a lower alpha but higher beta. This is because the alpha suggests that the hedge fund will return an amount more than an index irrespective of the indexes return level. The conventional formula for a simple linear regression is

Here y represents the return on the dependent variable, in our case SPN’s return, x is the return of the independent variable, the S&P 500 index, β is the slope of the best fit line and ε and error term. The best fit line is plotted in a way that the sum of the distance to all the error terms is minimized.

To plot the best fit line in Excel click on Tools, Add ins, check “Analysis ToolPak”, and then hit OK. Click back into Tool and then choose Data Analysis. Scroll down and choose regression and highlight the return data for SPN ret (column AA) as the Input Y range and S&P ret (column Z) as the Input X range. Make sure you check off Line Fit Plots and then click on OK. This will produce a scatter chart of the returns as well as the best fit line, which is the beta of the stock, relative to the S&P 500.

Volatility
Volatility is a metric used to describe the variation in price movements in assets. There are two types of volatilities; Historical Volatility and implied volatility. Historical volatility uses historical prices of assets and calculates using a modified form of standard deviation the volatility of prices. Implied volatility uses the actual prices of derivatives, such as options and works backwards to calculate the volatility that would imply the market price of tradable options. 

Calculating Historical Volatility
To calculate historical volatility we will walk through an example using our Excel spreadsheet. In our example we will begin by calculating the volatility of the price ratio of SPN and CAM. The price ratio is downloaded in column AE. (Although the price ratio is already downloaded, in column E we re-inputted the data so that it will be easier for the reader to reference cells close to each other). Next we calculate the natural logarithm of the daily returns using the following formula

This data is contained in column AF. Next we calculated the mean of the returns by using the =average(..) function. Now we calculate the difference between the mean and the log of returns (column AH) and square each of the values (column AI). Next we sum the data in column AJ. To arrive at the standard deviations of the returns we simply take column AJ , divide this value by the number of days -1 and square root the whole result . This gives us a standard deviation of 0.0102 which is displayed in cell AL6. The volatility is the standard deviation multiplied by the square root of the number of days in a year as presented in cell AM6. To summarize:

Where freq = frequency of data in a year.

In most cases this figure is equal to 252. As is shown in cell AK6 the annualized volatility of SPN/CAM is 16%. This simply means that if the past were to repeat itself in the future, we should expect the price ratio to vary by 16% in a whole year. This calculation does however make the assumption that the returns of stock prices are log normal. This means that if we plot a chart of the returns and the number of times the prices experienced returns within a certain range, we should expect the histogram to look like a bell curve. Qualitatively this would mean that there should be an equal chance for the price to jump and to drop by a certain percentage.

August 21, 2007