Finance
Parkinson High-Low & Garman-Klass OHLC Volatility Formulas
Calculate Parkinson and Garman-Klass range-based volatility estimates from a valid OHLC bar, then apply an explicit periods-per-year assumption without rounding intermediate results.
What the estimates measure
These range-based estimators use high and low prices, with Garman-Klass also using the open and close. Their diffusion-model motivation assumes continuous price paths and negligible drift; jumps and overnight gaps are not captured adequately by these simple intraday formulas. A claimed efficiency improvement under a model is not a universal accuracy guarantee.
All prices must be positive, numeric and from the same interval and adjustment basis. The high must be at least both open and close, and the low no greater than either. This workbook checks the entire OHLC record for both estimators even though Parkinson uses only the high and low.
The sample bar is O=100, H=106, L=98, C=103. A single-bar estimate is useful for demonstrating arithmetic; it is not a stable forecast of future volatility or a recommendation to trade.
Parkinson estimate per observation interval
The squared log range divided by 4*LN(2) is a variance estimate. Take its square root to obtain volatility. For the sample bar this is approximately 0.0471, or 4.71%.
=IF(COUNT(B2:B5)<>4,"Enter numeric OHLC",IF(OR(MIN(B2:B5)<=0,B3<B4,B2<B4,B2>B3,B5<B4,B5>B3),"Invalid OHLC",SQRT((LN(B3/B4)^2)/(4*LN(2)))))Garman-Klass estimate per observation interval
Combine the squared log range with the squared open-to-close log return. The sample result is approximately 0.0524. Retain full precision when using it in other calculations.
=IF(COUNT(B2:B5)<>4,"Enter numeric OHLC",IF(OR(MIN(B2:B5)<=0,B3<B4,B2<B4,B2>B3,B5<B4,B5>B3),"Invalid OHLC",SQRT(0.5*LN(B3/B4)^2-(2*LN(2)-1)*LN(B5/B2)^2)))Annualize Parkinson with a declared frequency
B7 is the number of comparable observation intervals per year. It defaults to 252 solely for this daily-market example; hourly, weekly or continuously traded data require a matching assumption. Square-root-of-time scaling assumes variance is additive across comparable intervals. It is not a guaranteed future annual volatility.
=IF(COUNT(B2:B5)<>4,"Enter numeric OHLC",IF(OR(MIN(B2:B5)<=0,B3<B4,B2<B4,B2>B3,B5<B4,B5>B3),"Invalid OHLC",IF(NOT(ISNUMBER(B7)),"Invalid periods per year",IF(OR(B7<1,B7<>INT(B7)),"Invalid periods per year",SQRT(((LN(B3/B4)^2)/(4*LN(2)))*B7)))))Annualize Garman-Klass
Use the same frequency and unrounded variance. Multiplying the already rounded value 0.0524 can produce a different displayed result.
=IF(COUNT(B2:B5)<>4,"Enter numeric OHLC",IF(OR(MIN(B2:B5)<=0,B3<B4,B2<B4,B2>B3,B5<B4,B5>B3),"Invalid OHLC",IF(NOT(ISNUMBER(B7)),"Invalid periods per year",IF(OR(B7<1,B7<>INT(B7)),"Invalid periods per year",SQRT((0.5*LN(B3/B4)^2-(2*LN(2)-1)*LN(B5/B2)^2)*B7)))))Combine multiple bars at the variance level
For multiple complete bars of the same duration, calculate each bar’s variance first, average those variances, then take the square root. Annualization is SQRT(periods_per_year*AVERAGE(variance_range)); do not average the individual square roots. Validate every bar before aggregation, since AVERAGE can ignore text error messages.
These formulas contain no previous-close input, so they do not measure overnight close-to-open jumps. Do not mix bars with inconsistent corporate-action adjustments.
Try the example
Use the original values or change them to test your understanding.