A stock trader will generally have access to daily, weekly, monthly, or quarterly price data for a stock or a stock portfolio. Using this data he can calculate corresponding returns from the stock (daily, weekly, monthly, quarterly returns). He can also use this data to calculate the standard deviation of the stock returns. The standard deviation so calculated will also be the standard deviation for that period. For example, using daily returns, we will calculate the standard deviation of daily returns. However, when we talk about volatility, we are most likely talking about annual standard deviation. Therefore, we will have to annualize the standard deviation calculated using the periodic data.
The annualized standard deviation of daily returns is calculated as follows:
Annualized Standard Deviation = Standard Deviation of Daily Returns * Square Root (250)
Here, we assumed that there were 250 trading days in the year. Depending on weekends and public holidays, this number will vary between 250 and 260.
So, if standard deviation of daily returns were 2%, the annualized volatility will be = 2%*Sqrt(250) = 31.6%
Similarly, we can calculate the annualized standard deviation using any periodic data.
- For weekly returns, Annualized Standard Deviation = Standard Deviation of Weekly Returns * Sqrt(52).
- For monthly returns, Annualized Standard Deviation = Standard Deviation of Monthly Returns * Sqrt(12).
- For quarterly returns, Annualized Standard Deviation = Standard Deviation of Quarterly Returns * Sqrt(4).
| Data you have | Periods in a year | Multiplier | 2% becomes |
|---|---|---|---|
| Daily | 250 | √250 = 15.81 | 31.62% |
| Daily | 252 | √252 = 15.87 | 31.75% |
| Weekly | 52 | √52 = 7.21 | 14.42% |
| Monthly | 12 | √12 = 3.46 | 6.93% |
| Quarterly | 4 | √4 = 2.00 | 4.00% |
Doing it in Excel
With daily returns in B2:B251:
=STDEV.S(B2:B251)*SQRT(250)Use STDEV.S for a sample, which is almost always what a price history is.
STDEV.P treats your data as the entire population and will give a slightly
smaller number.
250 versus 252
We have used 250 trading days here. You will see 252 and sometimes 260 being used, and the difference is small: 2% daily returns give 31.62% with 250 days and 31.75% with 252.
Also read this article about how to calculate volatility in excel.