Although there are several ways to measure the volatility of a given security, analysts typically look at historical volatility. Historical volatility is a measure of past performance. Because it allows for a more long-term assessment of risk, historical volatility is widely used by analysts and traders in the creation of investing strategies.
To calculate the volatility of a given security in Microsoft Excel, first determine the time frame for which the metric will be computed. A 10-day period is used for this example. Next, enter all the closing stock prices for that period into cells B2 through B12 in sequential order, with the newest price at the bottom. Note that you will need the data for 11 days to compute the returns for a 10-day period.
In column C, calculate the interday returns by dividing each price by the closing price of the day before and subtracting one. For example, if McDonald's (MCD) closed at $147.82 on the first day and at $149.50 on the second day, the return of the second day would be (149.50/147.82) - 1, or .011, indicating that the price on day two was 1.1% higher than the price on day one.
Volatility is inherently related to standard deviation, or the degree to which prices differ from their mean. In cell C13, enter the formula "=STDEV.S(C3:C12)" to compute the standard deviation for the period.
As mentioned above, volatility and deviation are closely linked. This is evident in the types of technical indicators that investors use to chart a stock's volatility, such as Bollinger Bands, which are based on a stock's standard deviation and the simple moving average (SMA). However, historical volatility is an annualized figure, so to convert the daily standard deviation calculated above into a usable metric, it must be multiplied by an annualization factor based on the period used. The annualization factor is the square root of however many periods exist in a year.
The table below shows the volatility for McDonald's within a 10-day period:
The example above used daily closing prices, and there are 252 trading days per year, on average. Therefore, in cell C14, enter the formula "=SQRT(252)*C13" to convert the standard deviation for this 10-day period to annualized historical volatility.
Why Volatility Is Important to Investors
Volatility in a stock has a bad connotation, but many traders and investors seek out higher volatility investments in order to make higher profits. After all, if a stock or other security does not move, it has low volatility, but it also has a low potential to make capital gains. On the other hand, a stock or other security with a very high volatility level can have tremendous profit potential, but the risk of loss is quite high. Timing of any trades must be perfect, and even a correct market call could end up losing money if the security's wide price swings trigger a stop-loss or margin call.