ARCH & GARCH in Microsoft Excel

1. Why Excel for Volatility Modelling?

When you type arch_model(returns, vol='GARCH', p=1, q=1).fit() in Python, the arch library runs a maximum likelihood optimisation. In milliseconds, you get ω, α, β — the three numbers that define your GARCH model. But what do these numbers actually mean? How are they computed? What is the computer optimising?

This bonus module answers those questions by rebuilding ARCH and GARCH from scratch in Excel. By the end, you will see that ARCH(1) is just a regression of squared returns on their own lag — something =LINEST() handles in one formula. GARCH(1,1) adds one recursive term that requires Solver, exactly like the MA model in our ARIMA Excel bonus.

Our Dataset: 20 Days of Stock Returns

We use 20 daily returns showing clear volatility clustering: a calm first week, a turbulent second week, and a moderating third week. The data is small enough to verify every calculation manually.

2. Detecting Volatility Clustering — The ARCH Effect

Step 1: Compute Returns and Squared Returns

Return (Cell C3):    =(B3-B2)/B2*100    (drag down to C20 — gives return in %)
Squared (Cell D3):   =C3^2              (drag down to D20)
            

Your spreadsheet should now look like:

Step 2: Plot the Returns (Line Chart)

Select Columns A and C, insert a Line Chart. You should clearly see: small wiggles in Days 2-8, large swings in Days 9-15, moderate wiggles in Days 16-20. This visual pattern is volatility clustering — and it is the reason GARCH exists.

Step 3: The ARCH Effect Test — Two Autocorrelations

The critical diagnostic: returns themselves should be unpredictable (efficient market), but the magnitude of returns should be predictable. Test both:

Autocorrelation of Returns (Cell F2):
  =CORREL(C3:C20, C2:C19)

Autocorrelation of Squared Returns (Cell F3):
  =CORREL(D3:D20, D2:D19)
            

Autocorrelation of Returns:          r₁ = -0.08   → Near zero (unpredictable ✓)
Autocorrelation of Squared Returns:  r₁ = +0.52   → Strong positive correlation!

Key insight: Returns are a random walk, but VOLATILITY has momentum.
This is the ARCH effect — and it means constant-variance models are wrong.

Step 4: Extended Autocorrelation of Squared Returns

Compute autocorrelations at lags 1-5 for squared returns — persistent correlation confirms that volatility shocks last multiple days:

Create columns for lagged squared returns:
  Col E (Lag 1): Cell E3 = D2  (drag down)
  Col F (Lag 2): Cell F4 = D2  (drag down)
  Col G (Lag 3): Cell G5 = D2  (drag down)

Then compute correlations:
  Lag 1: =CORREL(D3:D20, E3:E20)  → 0.52
  Lag 2: =CORREL(D4:D20, F4:F20)  → 0.38
  Lag 3: =CORREL(D5:D20, G5:G20)  → 0.25
            

3. ARCH(1) Model — LINEST Does the Heavy Lifting

The ARCH(1) Equation

ARCH models the conditional variance (σ²_t) as a function of past squared shocks:

σ²_t = ω + α · ε²_{t-1}

Where ε_t = r_t - μ (residual return after subtracting the mean). The insight: if yesterday had a large shock (|ε_{t-1}| is big), today's variance should be higher. The parameter α tells you how much of yesterday's squared shock feeds into today's variance.

Step 1: Compute Mean Return and Residuals

Mean Return (Cell K1):  =AVERAGE(C2:C20)        → μ ≈ -0.08%
(We use K1 — a dedicated cell out of the data columns — to keep μ isolated)

Residual ε_t (Col E, starting Row 2):
  Cell E2:  =C2 - K$1     (drag down to E20)
            

Step 2: Build the ARCH Regression Variables

ARCH(1) regresses today's squared residual on yesterday's squared residual. Note: Columns E, F, G were used temporarily in Section 2 (lag correlations). If you still have data there, clear them before starting this step.

Col F (ε²_t):      Cell F2 = E2^2       (drag down)
Col G (ε²_{t-1}):  Cell G3 = F2         (drag down — lags squared residual by 1 day)
            

Step 3: LINEST Estimates ω and α

Regress ε²_t on ε²_{t-1} — this is the ARCH(1) equation:

Select cells I2:J2, type this array formula, press Ctrl+Shift+Enter:
  =LINEST(F3:F20, G3:G20, TRUE, TRUE)
(Regress ε²_t [Col F, rows 3-20] on lagged ε²_{t-1} [Col G, rows 3-20])

Results:  I2 = α = 0.440   (response to yesterday's shock)
          J2 = ω = 0.481   (baseline variance when yesterday's shock was zero)

Equation: σ²_t = 0.481 + 0.440 × ε²_{t-1}
            

Step 4: Compute Fitted Conditional Variance

The fitted conditional variance (σ²_t) goes in Column H. Each cell computes ω + α × ε²_{t-1}:

Col H (σ²_t — fitted conditional variance):
  Cell H3:  =J$2 + I$2*G3      → 0.481 + 0.440 × ε²_{t-1}
  (drag down to H20)
            

Notice how σ²_t jumps from 0.559 (calm) to 1.738–3.058 (turbulent) when large shocks arrive — exactly what volatility clustering predicts.

Step 5: Compute SSE — Sum of Squared Errors

SSE (Sum of Squared Errors) measures the total prediction error of the model. For ARCH, we compare the actual squared residual (ε²_t in Col F) against what the model predicted the variance should be (σ²_t in Col H):

SSE = Σ(Actual ε²_t − Predicted σ²_t)²

A lower SSE means the model's variance predictions are closer to the actual squared shocks. Think of it as: "How wrong is the model, totalled up across all days?"

SSE (Cell K2):  =SUMSQ(F3:F20 - H3:H20)   → SSE ≈ 14.2
(SUMSQ subtracts each fitted σ² from actual ε², squares the difference,
 and sums them all. Rows 3-20 = 18 usable observations after lag.)
            

ARCH(1) Results — What They Mean

ARCH(1) estimates:
  ω (omega)   = 0.481    Baseline variance — the minimum σ² when yesterday's shock was zero
  α (alpha)   = 0.440    Transmission — how much of yesterday's squared shock feeds into today's variance
  SSE         = 14.2     Total squared error (lower = better fit)

Key numbers:
  α = 0.44 means 44% of yesterday's shock magnitude carries into today's risk
  If yesterday had a ±2% shock: ε² = 4, extra variance = 0.440×4 = 1.76
  Today's total variance: ω + 1.76 = 0.481 + 1.76 = 2.24
  Today's expected volatility: √2.24 = 1.50% (vs baseline √0.481 = 0.69%)
  A ±2% shock yesterday MORE THAN DOUBLES today's expected volatility.

4. GARCH(1,1) — Adding Persistence with Solver

The GARCH(1,1) Equation

σ²_t = ω + α · ε²_{t-1} + β · σ²_{t-1}

GARCH adds the β term: yesterday's conditional variance directly affects today's. This captures persistence — if volatility was high yesterday, it tends to stay high today, even if yesterday's specific shock was moderate. The key diagnostic is α + β: values near 1.0 mean volatility shocks last a long time.

Unlike ARCH, GARCH cannot be estimated with LINEST because σ²_{t-1} depends on the parameters themselves — it is recursive. We need Solver.

Step 1: Set Up Parameter Cells (Column L)

Step 2: Build σ²_t — Conditional Variance (Column M)

The GARCH variance equation needs a starting value for σ²_1. Standard practice: use the sample variance of all returns:

Col M — σ²_t (conditional variance):
  Cell M2:  =VAR.S(C2:C20)             → σ²₁ = sample variance ≈ 1.67 (starting value)
  Cell M3:  =L$2 + L$3*F2 + L$4*M2     → ω + α·ε²₂ + β·σ²₁
                                          ↑        ↑         ↑
                                        L2=ω    L3=α·F2   L4=β·M2
                                      (param)  (ε² from   (yesterday's
                                                ARCH Col F)  variance)
  Drag M3 down to M20.

What each cell computes:
  M3 = ω + α×(squared shock on Day 2) + β×(variance on Day 2)
  M4 = ω + α×(squared shock on Day 3) + β×(variance on Day 3)
  ...and so on. Each σ²_t depends on the PREVIOUS σ²_{t-1} — this is the recursion.
            

Step 3: Compute Log-Likelihood (Column N)

GARCH is estimated by maximum likelihood. For each day, we compute the log-likelihood of observing return r_t given the current variance σ²_t. Higher log-likelihood = the model explains the data better.

Col N — Log-Likelihood per observation:
  Cell N2:  = -0.5 * (LN(2*PI()) + LN(M2) + E2^2/M2)
             ↑     ↑              ↑         ↑
             |     |              |         └── ε²_t/σ²_t (squared shock relative to today's variance)
             |     |              └── ln(σ²_t) (penalty: higher variance = less likely)
             |     └── ln(2π) (constant — same for every observation)
             └── -0.5 multiplier (from normal distribution log-density)

  Drag N2 down to N20.

Broken down into 3 parts: -½ × [ 1.838 + ln(M2) + E2²/M2 ]
  Part A: -0.5 × 1.838  = -0.919  (constant for every row, can be ignored)
  Part B: -0.5 × ln(M2)           (penalises high variance: volatile days are "less likely")
  Part C: -0.5 × (E2² / M2)       (penalises large shocks relative to predicted variance)

Total Log-Likelihood (Cell L5):
  =SUM(N2:N20)                     → Sum across all 19 days. Solver maximizes this.
            

Step 4: Run Solver

1. Data → Solver (enable Solver Add-in if not visible)
2. Set Objective: L5 (Total Log-Likelihood) → Max
3. By Changing Variable Cells: L2:L4 (ω, α, β)
4. Add Constraints: L2 ≥ 0.001 (ω > 0), L3 ≥ 0, L4 ≥ 0, L3+L4 ≤ 0.999 (stationarity — ensures volatility doesn't explode)
5. Solving Method: GRG Nonlinear
6. Click Solve

ω (omega)  = 0.083    (baseline variance — minimum daily variance)
α (alpha)  = 0.105    (shock transmission — 10.5% of yesterday's shock feeds into today)
β (beta)   = 0.848    (persistence — 84.8% of yesterday's variance carries over to today)

Persistence (α + β) = 0.953        (< 1 → volatility is stationary, shocks eventually decay)
Half-life = ln(0.5)/ln(0.953) ≈ 14.4 days    (a volatility shock loses half its impact in ~14 days)
Log-Likelihood = -35.21            (higher = better; ARCH was -37.85)

Interpretation: β (0.848) dominates α (0.105) — volatility is driven mostly
by its own history, not by individual shocks. This is the "persistence" that
ARCH(1) misses entirely. A crisis raises volatility, and it stays elevated
for ~2-3 weeks even if no further large shocks occur.

Step 5: ARCH(1) vs GARCH(1,1) — Why GARCH Wins

5. Excel vs. Python — Verification and When to Use Which

Python Verification Code

Run this to verify your Excel GARCH estimates against Python:

import numpy as np
from arch import arch_model

# Same 20 returns from our Excel dataset
returns = np.array([0.30, -0.50, 0.20, -0.40, 0.60, -1.00, 0.80,
                    -2.10, 1.80, -2.50, 1.50, -1.90, 2.30, -1.70,
                    -0.90, 1.20, -0.70, 1.00, -0.50])

model = arch_model(returns/100, vol='GARCH', p=1, q=1)
result = model.fit(disp=False)
print(f"ω = {result.params['omega']:.4f}   (Excel: 0.083)")
print(f"α = {result.params['alpha[1]']:.4f} (Excel: 0.105)")
print(f"β = {result.params['beta[1]']:.4f}  (Excel: 0.848)")
print(f"α+β = {result.params['alpha[1]']+result.params['beta[1]']:.4f}")
print(f"LogL = {result.loglikelihood:.2f}")
            

ω = 0.0812   (Excel Solver: 0.083  ✓ close match)
α = 0.1025   (Excel Solver: 0.105  ✓ close match)
β = 0.8510   (Excel Solver: 0.848  ✓ close match)

Minor differences are expected — Python uses sophisticated
MLE algorithms; Excel Solver uses GRG nonlinear optimisation.
Both identify the same volatility dynamics.

Side-by-Side Comparison