How do I approximate delta in an Excel spreadsheet without using the closed-form formula?

The simplest Excel approach is a finite-difference approximation of the partial derivative. It works without any closed-form formula and is what option-pricing libraries used before everyone had the BSM derivation memorised.

Setup in a spreadsheet:

Cell A1: $S_0 = 100$

Cell A2: $K = 100$

Cell A3: $r = 0.05$

Cell A4: $\sigma = 0.20$

Cell A5: $T = 1$

Cell A6: $q = 0$

Cell A7: $\varepsilon = 0.01$ (small bump)

Compute $c$ at $S$ and at $S+\varepsilon$ using a BSM call function:

Cell B1: =bsm_call(A1, A2, A3, A4, A5, A6)

Cell B2: =bsm_call(A1 + A7, A2, A3, A4, A5, A6)

Where bsm_call is your BSM function (either coded in VBA, or expressed as a formula using NORM.S.DIST).

Compute delta:

Cell C1: =(B2 - B1) / A7

That is the forward-difference approximation $\Delta \approx (V(S+\varepsilon) - V(S)) / \varepsilon$.

More accurate: central difference:

Cell B3: =bsm_call(A1 - A7, ...)

Cell C2: =(B2 - B3) / (2*A7)

The central-difference $\Delta \approx (V(S+\varepsilon) - V(S-\varepsilon)) / (2\varepsilon)$ is more accurate because it cancels the second-order error term.

Accuracy benchmark:

For $S=100$, $K=100$, $r=0.05$, $\sigma=0.20$, $T=1$, $q=0$, the closed-form $\Delta = N(0.35) \approx 0.6368$.

With $\varepsilon = 0.01$:

• Forward difference: $\approx 0.6368 + 0.0017$ ($\Gamma \cdot \varepsilon / 2$ error)
• Central difference: $\approx 0.6368$ to 5 decimal places

With $\varepsilon = 1.0$ (too large): Forward difference becomes 0.65 — about $2\%$ off.

The right $\varepsilon$:

• Too small ($\varepsilon < 10^{-6}$): floating-point round-off dominates. Result is noisy.
• Too large ($\varepsilon > 1$): finite-difference approximation breaks down.
• Sweet spot: $\varepsilon \approx 0.001 \times S$ to $0.01 \times S$.

Why this matters for the exam:

Some exam questions give you a BSM-priced option and ask, "if $S$ moves from 100 to 101, what is the new option price?" The right approach is $V_{\text{new}} \approx V_{\text{old}} + \Delta \times 1$. That is literally the finite-difference formula run backwards.

Why this matters in practice:

Many production option-pricing libraries compute Greeks by finite differencing because (a) they support arbitrary payoffs where closed forms do not exist, and (b) the same code that computes the price also computes the Greek. The closed-form derivation is elegant; finite differencing is universal.