Realised volatility worked from twenty daily closes, in Excel and by hand, with the conventions that change the answer and how uncertain a one-month estimate is.
Take the natural log of each day's price ratio, compute the sample standard deviation of those returns, and multiply by the square root of 252 trading days. On twenty illustrative daily closes the daily standard deviation is 1.1221 per cent, so the historical volatility is 17.81 per cent a year. With only twenty returns, its standard error is about 2.89 points, which matters as much as the estimate itself.
Worked in full in Quantitative Finance by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
Historical, or realised, volatility is the one volatility you can measure. Implied volatility is what the option market charges; realised is what the stock then does. An option hedger's profit depends on the gap between them, so getting the realised figure right, and knowing how uncertain it is, comes before any view on whether an option is cheap.
Twenty-one illustrative closing prices give twenty daily returns. The stock starts at 100 and ends at 100.38.
| Day | Close | ln(Pt / Pt−1) |
|---|---|---|
| 1 | 100.80 | 0.7968% |
| 2 | 99.59 | −1.2077% |
| 3 | 101.08 | 1.4851% |
| 4 | 100.68 | −0.3965% |
| 5 | 100.98 | 0.2975% |
| 6 | 98.86 | −2.1218% |
| 7 | 99.95 | 1.0965% |
| 8 | 100.55 | 0.5985% |
| 9 | 99.65 | −0.8991% |
| 10 | 101.44 | 1.7803% |
| 11 | 100.02 | −1.4097% |
| 12 | 100.22 | 0.1998% |
| 13 | 99.52 | −0.7009% |
| 14 | 100.81 | 1.2879% |
| 15 | 100.31 | −0.4972% |
| 16 | 101.21 | 0.8932% |
| 17 | 99.59 | −1.6136% |
| 18 | 99.99 | 0.4008% |
| 19 | 100.99 | 0.9951% |
| 20 | 100.38 | −0.6059% |
rt = ln(Pt ÷ Pt−1)
Day 1: ln(100.80 ÷ 100.00) = 0.007968, or 0.7968%
Excel, closes in B2:B22: in C3 =LN(B3/B2), filled down to C22
Log returns add up across days (the twenty sum to 0.38 per cent, exactly ln(100.38 ÷ 100)), and they are the returns the Black-Scholes model assumes are normally distributed, so the volatility you measure is the same quantity the pricing formula uses.
s = √[ Σ(rt − r̄)² ÷ (n − 1) ]
Mean daily return r̄ = 0.0190%; sum of squared deviations = 0.002392; divided by n − 1 = 19 gives 0.000126
s = √0.000126 = 1.1221% a day
Excel: =STDEV.S(C3:C22)
Annual volatility = daily s × √252
= 1.1221% × 15.8745 = 17.81%
Excel, in one cell: =STDEV.S(LN(B3:B22/B2:B21))*SQRT(252)
Volatility scales with the square root of time because independent daily variances add: 252 days of variance is 252 times one day's, and the standard deviation is the square root of that. Use the number of trading days, not calendar days, since prices only move when the market is open.
Not very. The standard error of a volatility estimated from n returns is roughly σ ÷ √(2(n − 1)): 17.81 ÷ √38 = 2.89 points. A rough 95 per cent band runs from 12.1 to 23.5 per cent. Split the same twenty days in half and the first ten give 20.07 per cent, the last ten 16.08.
| Returns | Roughly | Standard error, points |
|---|---|---|
| 20 | one month | 2.89 |
| 60 | three months | 1.64 |
| 120 | six months | 1.15 |
| 252 | one year | 0.80 |
A longer window is more precise but slower to notice a change. That trade-off, not the formula, is where judgement goes.
| Method | Annual volatility |
|---|---|
| Log returns, sample sd, √252 | 17.81% |
| Simple returns instead of logs | 17.79% |
| Population sd (divide by n) | 17.36% |
| Zero mean assumed, divide by n | 17.36% |
| Annualised with √260 | 18.09% |
| Annualised with √365 | 21.44% |
Logs against simple returns barely matters for daily data. Dividing by n rather than n − 1 matters more on short windows. The annualisation factor matters most: using 365 on trading-day data inflates the answer by 3.63 points, a common error when a spreadsheet's dates include weekends but its prices do not. Many risk systems use the zero-mean version, because the daily mean is tiny and noisy.
A further variant weights recent days more. The exponentially weighted estimate updates each day as σ²t = λσ²t−1 + (1 − λ)r²t. With λ = 0.94, the latest day carries a 6 per cent weight and a day ten sessions earlier 3.23 per cent. Seeded with the first day's squared return, it gives 15.73 per cent on this data, lower than the equal-weighted 17.81 because the last ten days were calmer than the first ten.
Kestrel 100, the option that runs through the book, is priced at 24 per cent implied volatility: a typical daily move of 1.51 per cent, against 1.12 per cent realised here. If the realised level held, a seller of that option who delta-hedged would collect the gap. In the book's hedging simulator an option sold at 24 and hedged pays 1.5981 on average when realised volatility is 20 per cent, and loses 1.6022 when it is 28. But with a 2.89-point standard error on a one-month estimate, a 6.19-point gap is about two standard errors: suggestive, not conclusive. For the reverse calculation, from an option price to volatility, see how to calculate implied volatility with Newton's method.
The hedging simulator that turns the realised-implied gap into profit and loss, period by period, is in the free workbooks for this book. For what hedging less often does to the outcome, see does monthly hedging lose money.
Variances of independent daily returns add, so a year's variance is about 252 times a day's, using trading days. The standard deviation is the square root, so daily volatility is scaled by the square root of 252, 15.8745. A 1.1221 per cent daily standard deviation becomes 17.81 per cent a year.
Log returns are the convention, because they add across days and match the Black-Scholes assumption. For daily data the difference is small: on the twenty returns here, log returns give 17.81 per cent and simple returns 17.79 per cent. The choice of annualisation factor and window matters far more.
It is a trade-off between precision and responsiveness. The standard error is roughly the volatility over the square root of 2(n minus 1): at about 17.81 per cent, twenty returns give 2.89 points, sixty give 1.64 and a year of 252 returns gives 0.80. Desks often watch several windows side by side.
This article is one calculation from Quantitative Finance. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: corporate finance, valuation and markets → · All 1340 articles →
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.