Quantitative Finance
Roll (1984) Implied Effective Bid-Ask Spread Formula (Serial Autocovariance)
Infer the hidden effective dollar and basis-point bid-ask spread from transaction prices alone using Roll’s (1984) first-order price-change autocovariance S_Roll = 2 * SQRT(-Cov(dP_t, dP_{t-1})).
Recover effective bid-ask spreads from trade prints without Level-1 quotes!
Even when historical tape records contain only trade execution prices P_t (with no bid or ask quotes), random alternation of buyer-initiated trades at the Ask (m + s/2) and seller-initiated trades at the Bid (m - s/2) induces negative first-order serial covariance in price changes: Cov(dP_t, dP_{t-1}) = -s^2 / 4! Solving for s gives Roll’s (1984) classic implied spread estimator S_Roll = 2 * SQRT(-Cov(dP_t, dP_{t-1})) whenever serial covariance gamma_1 < 0 (and 0 in momentum-dominated samples where gamma_1 >= 0).
Dividing S_Roll by the average trade price P_mid and multiplying by 10,000 expresses transaction friction directly in basis points (bps).
| Step | Price Change dP_t ($) | Lagged Change dP_{t-1} ($) | Product (Mean = 0) |
|---|---|---|---|
| Obs 1 | +0.06 | -0.04 | -0.0024 |
| Obs 2 | -0.04 | +0.06 | -0.0024 |
| Obs 3 | +0.04 | -0.06 | -0.0024 |
| Obs 4 | -0.06 | +0.04 | -0.0024 (S_Roll = $0.0980) |
Roll Implied Effective Dollar Spread ($)
Computes first-order population autocovariance gamma_1 across B2:B5 and C2:C5; returns 2 * SQRT(-gamma_1) when gamma_1 < 0, else 0.
=IF(COUNT(B2:B5) < 2, "Need >= 2 Pairs", IF(CORREL(B2:B5, C2:C5) * STDEV.P(B2:B5) * STDEV.P(C2:C5) >= 0, 0, ROUND(2 * SQRT(-(CORREL(B2:B5, C2:C5) * STDEV.P(B2:B5) * STDEV.P(C2:C5))), 4)))Relative Effective Spread (bps) on Reference Price B6
Expresses Roll dollar spread as basis points of midpoint price B6.
=IF(OR(B6 <= 0, CORREL(B2:B5, C2:C5) >= 0), 0, ROUND(10000 * (2 * SQRT(-(CORREL(B2:B5, C2:C5) * STDEV.P(B2:B5) * STDEV.P(C2:C5)))) / B6, 2))Try the example
Use the original values or change them to test your understanding.