Market Microstructure & Quantitative Finance
Roll Serial-Covariance Implicit Bid-Ask Spread Formula
Estimate effective bid-ask spread s_Roll = 2 * SQRT(-Cov(Delta_P_t, Delta_P_{t-1})) from transaction prices alone without quote data via Richard Roll’s (1984) bounce formula.
Recover hidden bid-ask spreads from trade prices alone when Level-1 quote tapes are unavailable!
How do quant researchers measure transaction costs in corporate bonds, historical equities, or OTC markets where only executed trade prices P_t are recorded (without Bid/Ask quotes)? Richard Roll (1984) proved that if midprice fundamentals follow a random walk while buy/sell market orders hit Ask (+s/2) or Bid (-s/2) with independent 50/50 probability, consecutive price changes Delta_P_t = P_t - P_{t-1} exhibit negative first-order autocovariance Cov(Delta_P_t, Delta_P_{t-1}) = -(s^2) / 4 — recovering the exact Effective Bid-Ask Spread s_Roll = 2 * SQRT(-Cov(Delta_P_t, Delta_P_{t-1})) whenever serial covariance is negative (and clamping to 0 when short-term momentum pushes covariance >= 0)!
Verify on a 6-trade print tape where hidden Bid = 100.00 and Ask = 100.20 (true spread s = 0.20): B2:B7 = [100.00, 100.20, 100.00, 100.00, 100.00, 100.20]. Price changes C3:C7 are [+0.20, -0.20, 0.00, 0.00, +0.20]; both lagged window C3:C6 and current window C4:C7 have exact mean 0.00, demeaned lag products D4:D7 = [-0.0400, 0.0000, 0.0000, 0.0000] average to Cov = -0.0100, and Roll’s formula recovers s_Roll = 2 * SQRT(0.0100) = 0.2000 (0.20% relative spread on 100.00)!
| Roll Microstructure Metric (6 Prints: 100.00 / 100.20) | Formula | Verified Output |
|---|---|---|
| First-Order Serial Covariance (Cov_1) | AVERAGE(D4:D7) | -0.0100 |
| Roll Implicit Dollar Spread (s_Roll) | IF(Cov_1 >= 0, 0, 2 * SQRT(-Cov_1)) | 0.2000 (= Ask 100.20 - Bid 100.00!) |
| Implicit Half-Spread Execution Cost | s_Roll / 2 | 0.1000 per one-way trade |
| Momentum / Trending Tape Guard (Cov_1 >= 0) | Zero-Clamp Convention (Harris 1990) | 0.0000 (Avoids #NUM! on SQRT) |
Row-Helper Demeaned First-Order Serial Covariance Formula
Compute row-level demeaned products D4 = (C4 - AVERAGE($C$4:$C$7)) * (C3 - AVERAGE($C$3:$C$6)) so every lag contribution is transparent to peer reviewers.
=ROUND(AVERAGE(D4:D7), 6)Zero-Clamped Roll Implicit Effective Spread 2 * SQRT(-Cov_1) Formula
In small daily samples, positive intraday drift can push sample Cov_1 >= 0 (~30–40% of stock-years); clamping Cov_1 >= 0 to 0 prevents SQRT(-Cov_1) from throwing #NUM!!
=LET(cv_1, AVERAGE(D4:D7), IF(MIN(B2:B7) <= 0, "Require Positive Prices", IF(cv_1 >= 0, 0, ROUND(2 * SQRT(-cv_1), 4))))Try the example
Use the original values or change them to test your understanding.