Corwin-Schultz High-Low Bid-Ask Spread Estimator Formula (2-Day vs 1-Day)

Separate daily volatility from bid-ask bounce using 1-day vs 2-day High/Low bars!

Daily High prices are almost always buyer-initiated at the Ask while daily Low prices are seller-initiated at the Bid. Corwin & Schultz (2012) observed that the sum of two consecutive 1-day squared log ranges beta = [ln(H_1 / L_1)]^2 + [ln(H_2 / L_2)]^2 reflects TWO days of variance plus TWICE the bid-ask spread, whereas the merged 2-day squared log range gamma = [ln(MAX(H_1, H_2) / MIN(L_1, L_2))]^2 reflects TWO days of variance plus ONLY ONE bid-ask spread!

Solving out variance yields alpha = (SQRT(2 * beta) - SQRT(beta)) / (3 - 2 * SQRT(2)) - SQRT(gamma / (3 - 2 * SQRT(2))) and percentage effective spread S = MAX(0, 2 * (EXP(alpha) - 1) / (1 + EXP(alpha))), flooring negative noisy two-day realizations at zero.

Corwin-Schultz Effective Percentage Spread (%)

Uses clean LET variables (betaVal, gammaVal, denomVal, alphaVal) to compute the floored percentage spread from Day-1 High/Low (B2, B3) and Day-2 High/Low (B4, B5).

=IF(OR(B2 <= 0, B3 <= 0, B4 <= 0, B5 <= 0, B2 < B3, B4 < B5), "Invalid High/Low", LET(betaVal, (LN(B2 / B3) ^ 2) + (LN(B4 / B5) ^ 2), gammaVal, LN(MAX(B2, B4) / MIN(B3, B5)) ^ 2, denomVal, 3 - 2 * SQRT(2), alphaVal, (SQRT(2 * betaVal) - SQRT(betaVal)) / denomVal - SQRT(gammaVal / denomVal), ROUND(MAX(0, 2 * (EXP(alphaVal) - 1) / (1 + EXP(alphaVal))) * 100, 4)))

1-Day Squared Log-Range Sum (beta) vs 2-Day Squared Log-Range (gamma)

Computes beta across the two consecutive daily bars for step-by-step audit.

=ROUND((LN(B2 / B3) ^ 2) + (LN(B4 / B5) ^ 2), 6)

Further reading

• Corwin, S. A. & Schultz, P. (2012) — A Simple Way to Estimate Bid-Ask Spreads from Daily High and Low Prices