A:

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. (Want to improve your Excel skills? Take InvestopediaÂ Academy's Excel Course.)

To calculate 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 "=STDV(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.

RELATED FAQS
1. ### What is the best measure of a stock's volatility?

Understand what metrics are most commonly used to assess a security's volatility compared to its own price history and that ... Read Answer >>
2. ### How is standard deviation used to determine risk?

Understand the basics of calculation and interpretation of standard deviation, and how it is used to measure and determine ... Read Answer >>
3. ### What is the difference between standard deviation and z score?

Understand the basics of standard deviation and Z-score; learn how each is calculated and used in the assessment of market ... Read Answer >>
4. ### How is risk aversion measured in Modern Portfolio Theory (MPT)?

Find out how risk aversion is measured in modern portfolio theory (MPT), how it is reflected in the market and how MPT treats ... Read Answer >>
5. ### Which market indicators reflect volatility in the stock market?

Learn the most commonly used technical indicators of stock market volatility that are watched by stock market traders and ... Read Answer >>
Related Articles

### Improve your investing with Excel

Excel is a useful tool to assist with investment organization and evaluation. Find out how to use it.
2. Investing

### Why Standard Deviation Should Matter to Investors

Think of standard deviation as a thermometer for risk, or better yet, anxiety.
3. Investing

### The Uses And Limits Of Volatility

Check out how the assumptions of theoretical risk models compare to actual market performance.
4. Investing

### Roller coaster 2016 for Stocks? Exploring Global Stock Volatility

Find out how much volatility global equity investors are in for during 2016 by seeing how much they've experienced over the past five years.

### Implied vs. Historical Volatility: The Main Differences

Discover the differences between historical and implied volatility, and how the two metrics can determine whether options sellers or buyers have the advantage.
6. Investing

### Tips for investors in volatile markets

Market volatility is inevitable, trying to time the market is extremely difficult. One solution is to invest long term. Find out the best investment strategy to handle the market volatility.

8. Investing

### Calculating volatility: A simplified approach

Though most investors use standard deviation to determine volatility, there's an easier and more accurate way of doing it: the historical method.
9. Investing

### How to Take Advantage of Volatility as an Investor

Everyone talks about the downside of volatility, but it has its benefits too, including opportunities to investment entry points at lower prices.
RELATED TERMS
1. ### Historical Volatility - HV

Historical volatility is a statistical measure of the dispersion ...
2. ### Volatility

Volatility measures how much the price of a security, derivative, ...
3. ### Downside Risk

An estimation of a security's potential to suffer a decline in ...
4. ### Implied Volatility - IV

The estimated volatility of a security's price derived from an ...

A risk-adjusted return takes into account the amount of risk ...
6. ### Annualized Total Return

Annualized total return gives the yearly return of a fund calculated ...
Hot Definitions
1. ### Financial Risk

Financial risk is the possibility that shareholders will lose money when investing in a company if its cash flow fails to ...
2. ### Enterprise Value (EV)

Enterprise Value (EV) is a measure of a company's total value, often used as a more comprehensive alternative to equity market ...
3. ### Relative Strength Index - RSI

Relative Strength Indicator (RSI) is a technical momentum indicator that compares the magnitude of recent gains to recent ...
4. ### Dividend

A dividend is a distribution of a portion of a company's earnings, decided by the board of directors, to a class of its shareholders.
5. ### Inventory Turnover

Inventory turnover is a ratio showing how many times a company has sold and replaces inventory over a period.
6. ### Watchlist

A watchlist is list of securities being monitored for potential trading or investing opportunities.