Although there are several ways to measure the volatility of a given security, analysts typically look athistorical volatility. Historical volatility is a measure of past performance; it is a statistical measure of thedispersionof returns for a given security over a given period of time.
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. For a given security, in general, the higher the historical volatility value, the riskier the security is. However, some traders and investors actually seek out higher volatility investments in order to find profit opportunities. You can calculate the historical volatility of a given security using Microsoft Excel.
Key Takeaways
- Analysts and traders can calculate the historical volatility of a stock using the Microsoft Excel spreadsheet tool.
- Historical volatility is a measure of past performance.
- It is a statistical measure of thedispersionof returns for a given security over a given period of time.
- For a given security, in general, the higher the historical volatility value, the riskier the security is.
- However, some traders and investors actually seek out higher volatility investments.
:max_bytes(150000):strip_icc()/GettyImages-1597729432-457d2a5ed5ec4e1a8df44aa9e934f820.jpg "Learn How to Calculate Volatility in Excel (1)")
Calculating Historical Volatility in Excel
To calculate the volatility of a given security in a Microsoft Excel spreadsheet, first determine the time frame for which the metric will be computed.
Step 1: Timeframe
Volatility is a time-bound measurement, meaning that it measures the price swings of an asset or security over a particular period. Depending on the type of trader you are, different time periods would be more appropriate. A day trader, for instance, may only care about weekly volatility while a swing trader monthly. For the purposes of this article, a 10-day time period will be used in the example.
Step 2: Enter Price Information
After determining your timeframe, the next step is to enter all the closing stock prices for that timeframe into cells B2 through B12 in sequential order, with the newest price at the bottom. (Keep in mind that if you are doing a 10-day timeframe, you will need the data for 11 days to compute the returns for a 10-day period.)
Step 3: Compute Returns
In column C, calculate the inter-day 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.
Step 4: Calculate Standard Deviations
Volatility is inherently related to variance, and by extension, 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.
The link between standard deviation and volatility 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).
Step 5: Annualize the Period Volatility
Historical volatility is usually converted into 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 timeframe:
:max_bytes(150000):strip_icc()/volatility-5bfd885b46e0fb002640af5a "Learn How to Calculate Volatility in Excel (2)")
The example above used daily closing prices, and there are 250-252 trading days per year. 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 for Investors
While volatility in a stock can sometimes have a bad connotation, many traders and investors actually seek out higher volatility investments. They do this in the hopes of eventually making higher profits. If a stock or other security does not move, it has low volatility. However, it also has a low potential to makecapital gains.
On the other hand, a stock or other security with a very high volatility level can have tremendous profit potential. But by the same token, the risk of loss is quite high.
In order to be a trader or investor that capitalizes on volatility, the timing of any trades must be perfect. Even a correct market call could end up losing money if the security's wide price swings trigger either a stop-loss order ora margin call.
Why Is Historical Volatility Important?
The volatility of a particular asset or security is thought to exhibit mean reversion over time. This means that if a security is uncharacteristically volatile, it should return eventually to its long-run average. Likewise, if it is subdued, its volatility should increase. Calculating historical volatility is how to arrive at this average or mean level.
What Does the Volatility of a Stock Mean?
Volatility describes the speed and magnitude of price swings over a given period of time (often on an annualized basis). Highly volatile stocks experience large and swift price swings, and they are often considered to be riskier than less volatile stocks.
Is High or Low Historical Volatility Better?
For day traders and options traders, high volatility can provide more opportunities to move and out of positions or profit from volatility spikes. For most long-term buy-and-hold investors, however, lower volatility is often preferred.
