Black-Scholes in Excel: Build an Options Pricing Calculator (2026)

June 20, 2026 · VeloraAI Team

Financial Modeling Formulas Excel

In 1973, Fischer Black, Myron Scholes, and Robert Merton published a formula that won a Nobel Prize and rewired global derivatives markets. Today, a single Excel cell can do what once required a trading desk's quant team. If you can type NORM.S.DIST, you can build a working Black-Scholes options pricing calculator in Excel — call and put values, all five Greeks, and an implied volatility solver — in under thirty minutes.

This guide walks through the formula, the inputs, the cell-by-cell build, and the pitfalls that quietly mis-price options when analysts copy templates without understanding them. By the end you will have a calculator you can trust for equity options, employee stock-based comp valuations, convertible bond components, and any other vanilla European option a finance analyst sees.

What Is the Black-Scholes Model?

The Black-Scholes model is a closed-form equation that prices a European call or put option based on five observable inputs: the underlying price, strike price, time to expiry, risk-free rate, and the underlying's volatility. It assumes lognormal returns, no arbitrage, and continuous hedging — and outputs a single theoretical fair value.

The original formula prices a non-dividend-paying European call as:

C = S × N(d1) − K × e^(−rT) × N(d2)

Where N(·) is the standard normal cumulative distribution function, and:

d1 = [ ln(S/K) + (r + σ²/2) × T ] / (σ × √T)
d2 = d1 − σ × √T

The put price follows from put-call parity:

P = K × e^(−rT) × N(−d2) − S × N(−d1)

For dividend-paying stocks and most real-world cases, you use the Merton extension by replacing S with S × e^(−qT), where q is the continuous dividend yield. We will build the dividend-aware version because that is what analysts actually need.

ℹ️ Note: Black-Scholes prices European options — exercisable only at expiry. For American options on dividend-paying stocks, you need a binomial tree or a finite-difference solver. American calls on non-dividend stocks are an exception: they are never optimally exercised early, so Black-Scholes still works.

What Inputs Do You Need to Price an Option?

Five core inputs plus the dividend yield are all the model requires. Four of them are observable; one is not.

Input	Symbol	Source	Notes
Underlying price	S	Live quote	Use mid-price if bid/ask is wide
Strike price	K	Option contract	Fixed at trade
Time to expiry	T	Calendar	Expressed in years, e.g. 0.25 for 3 months
Risk-free rate	r	Treasury curve	Use continuous compounding; match maturity to T
Volatility	σ	Historical or implied	The only non-observable input
Dividend yield	q	Forward dividend ÷ S	Continuous yield; use 0 for non-dividend payers

Volatility is where almost every error happens. Historical volatility is the realized standard deviation of past log returns, annualized. Implied volatility is the σ that, when plugged into Black-Scholes, returns the current market price of the option. If you are valuing a private company's stock-based comp under ASC 718, you usually pick a peer-based volatility and document the rationale.

⚠️ Warning: Time to expiry must be in years, not days. A common error is plugging in 30 for a 30-day option instead of 30/365. The result is an option price roughly 19× too high. Always divide trading or calendar days by 365 (or 252 for trading-day conventions — pick one and stay consistent).

How Do You Calculate Black-Scholes in Excel?

You calculate Black-Scholes in Excel by laying out the six inputs in named cells, computing d1 and d2 with LN, SQRT, and basic arithmetic, then passing them through NORM.S.DIST(x, TRUE) to get the cumulative normal probabilities. The call and put prices are simple algebra on those probabilities and the discounted strike.

Here is the cell-by-cell build.

Step 1 — Lay Out the Inputs

In a clean tab, set up your input block:

Cell	Label	Example Value
C4	Underlying price (S)	100
C5	Strike (K)	105
C6	Time to expiry (T, years)	0.5
C7	Risk-free rate (r)	0.045
C8	Volatility (σ)	0.28
C9	Dividend yield (q)	0.015

Name them: select C4:C9, then Formulas → Create from Selection → Left column. Now you can reference S, K, T, r, sigma, and q by name and your formulas read like the textbook.

Step 2 — Compute d1 and d2

In C11:

=(LN(S/K) + (r - q + sigma^2/2) * T) / (sigma * SQRT(T))

In C12:

=C11 - sigma * SQRT(T)

Name C11 d1 and C12 d2.

Step 3 — Price the Call and Put

Call in C14:

=S * EXP(-q*T) * NORM.S.DIST(d1, TRUE) - K * EXP(-r*T) * NORM.S.DIST(d2, TRUE)

Put in C15:

=K * EXP(-r*T) * NORM.S.DIST(-d2, TRUE) - S * EXP(-q*T) * NORM.S.DIST(-d1, TRUE)

That is it. With S=100, K=105, T=0.5, r=4.5%, σ=28%, q=1.5%, you should see a call price of about $5.93 and a put of about $8.99.

Example: A 6-month call on a $100 stock struck at $105, with 28% volatility, 4.5% rates, and a 1.5% dividend yield is worth $5.93 — roughly 5.9% of spot. The same put is worth $8.99 because it is already in-the-money by $5.

Building a Reusable Black-Scholes LAMBDA

If you find yourself rebuilding this calculator across workbooks, wrap it in a LAMBDA. This turns Black-Scholes into a custom Excel function that you reuse like XLOOKUP.

Define this in Name Manager (Formulas → Name Manager → New):

Name: BS.CALL

Refers to:

=LAMBDA(S, K, T, r, sigma, q,
  LET(
    d1, (LN(S/K) + (r - q + sigma^2/2) * T) / (sigma * SQRT(T)),
    d2, d1 - sigma * SQRT(T),
    S * EXP(-q*T) * NORM.S.DIST(d1, TRUE) - K * EXP(-r*T) * NORM.S.DIST(d2, TRUE)
  )
)

Now anywhere in the workbook:

=BS.CALL(100, 105, 0.5, 0.045, 0.28, 0.015)

returns the call price directly. Build a matching BS.PUT the same way. For a full primer on this pattern see our LAMBDA functions for finance guide and the LET function deep dive.

💡 Pro Tip: Combine BS.CALL with DATA.TABLE (or a 2-variable data table) to instantly produce a volatility-vs-strike grid — the foundation of a volatility surface. One LAMBDA replaces hundreds of duplicated formulas.

graph LR
    A[S, K, T] --> D[d1, d2]
    B[r, q] --> D
    C[sigma] --> D
    D --> N1[N&#40;d1&#41;, N&#40;d2&#41;]
    N1 --> P[Call &amp; Put Price]
    N1 --> G[Delta, Gamma, Vega, Theta, Rho]
    P --> V[Validation: Put-Call Parity]
    G --> H[Hedging &amp; Risk]

How Do You Calculate the Option Greeks in Excel?

The Greeks are partial derivatives of the Black-Scholes price with respect to each input. Each one quantifies a different risk — and each is a one-line formula in Excel once you have d1 and d2.

The Five Core Greeks

Greek	Measures	Call Formula (Excel-ready)	Put Formula
Delta (Δ)	∂Price/∂S	`EXP(-qT) NORM.S.DIST(d1, TRUE)`	`EXP(-qT) (NORM.S.DIST(d1, TRUE) - 1)`
Gamma (Γ)	∂²Price/∂S²	`EXP(-qT) NORM.S.DIST(d1, FALSE) / (S * sigma * SQRT(T))`	Same as call
Vega (ν)	∂Price/∂σ	`S * EXP(-qT) NORM.S.DIST(d1, FALSE) * SQRT(T) / 100`	Same as call
Theta (Θ)	∂Price/∂T (per day)	See below	See below
Rho (ρ)	∂Price/∂r	`K * T * EXP(-rT) NORM.S.DIST(d2, TRUE) / 100`	`-K * T * EXP(-rT) NORM.S.DIST(-d2, TRUE) / 100`

Vega and rho are divided by 100 so the output is expressed per 1% (1 vol point or 1 rate point) — that is the trader convention. Without the scale, the numbers are per 100% move and nearly useless.

Theta — The Time Decay Formula

Theta is the messiest one because it has three terms. The per-calendar-day formula for a call is:

=(-S * NORM.S.DIST(d1, FALSE) * sigma * EXP(-q*T) / (2 * SQRT(T))
  - r * K * EXP(-r*T) * NORM.S.DIST(d2, TRUE)
  + q * S * EXP(-q*T) * NORM.S.DIST(d1, TRUE)) / 365

For the put, flip the sign on the last two terms and replace NORM.S.DIST(d2, TRUE) with NORM.S.DIST(-d2, TRUE) and NORM.S.DIST(d1, TRUE) with NORM.S.DIST(-d1, TRUE).

ℹ️ Note: NORM.S.DIST(x, FALSE) returns the probability density function (PDF), used in Gamma, Vega, and Theta. NORM.S.DIST(x, TRUE) returns the cumulative distribution function (CDF), used everywhere else. Mixing them up is the single most common Black-Scholes bug in Excel.

How Do You Solve for Implied Volatility in Excel?

You solve for implied volatility by using Goal Seek (or Solver) to find the σ that makes your Black-Scholes call price equal the observed market price. Set the cell to the difference between your model price and the market price, set it to zero, and let Goal Seek vary your sigma cell.

Goal Seek Walkthrough

Put the market price in a cell, e.g. D14 = 6.25.
In a helper cell D17, type =C14 - D14 (your model call minus market call).
Data → What-If Analysis → Goal Seek.
Set cell: D17. To value: 0. By changing cell: C8 (your sigma).
Click OK — C8 now holds the implied volatility, e.g. 0.295 or 29.5%.

For a cleaner, repeatable solution, build an IV LAMBDA that uses Newton's method (Vega is the derivative you need — you already have it). Or use Solver with constraints σ > 0 and σ < 5.

💡 Pro Tip: When solving for implied vol on deep out-of-the-money options, Goal Seek can return absurd values because Vega goes to zero. Constrain σ between 1% and 300% and start your initial guess at 25% — that solves 95% of convergence failures.

Validating Your Model With Put-Call Parity

A Black-Scholes calculator that does not check put-call parity is a calculator you cannot trust. Parity is a no-arbitrage relationship that holds for any options pricing model:

C − P = S × e^(−qT) − K × e^(−rT)

In Excel, drop this in a check cell:

=ROUND((C14 - C15) - (S * EXP(-q*T) - K * EXP(-r*T)), 6)

It should always return 0 (or a tiny rounding error). If it doesn't, your call or put formula has a typo — almost certainly a sign error or a swapped d1/d2. Build this check into every Black-Scholes workbook you ship.

⚠️ Warning: Pulling option prices from a vendor and computing implied vols without enforcing put-call parity is a classic source of garbage data. Bid-ask spreads, stale quotes, and mismatched timestamps cause parity to break — exclude those rows before training any volatility model or running a relative-value screen.

A Step-by-Step Recap

Here is the full build order so you can put it together from scratch:

Input block: name S, K, T, r, sigma, q in C4:C9.
d1 and d2: compute in C11 and C12, name them.
Call and put prices: C14 and C15 using NORM.S.DIST CDF.
Greeks: a row each for delta, gamma, vega, theta, rho (call and put columns).
Parity check: a single cell that should equal zero.
IV solver: market price input and a Goal Seek macro, or an IV LAMBDA.
LAMBDA wrappers: BS.CALL, BS.PUT, BS.IV for reuse across the workbook.
Sensitivity grid: a 2-variable data table over volatility and underlying price — see our sensitivity analysis in Excel guide for the two-way data table mechanics. Each Greek in the table is itself a sensitivity measure, so the entire sheet is effectively a multi-dimensional sensitivity model.

graph TD
    A[Inputs: S, K, T, r, sigma, q] --> B[Compute d1 and d2]
    B --> C[Call &amp; Put prices via NORM.S.DIST]
    B --> D[Greeks: Delta, Gamma, Vega, Theta, Rho]
    C --> E[Put-Call Parity Check = 0?]
    C --> F[Goal Seek for Implied Vol]
    D --> G[Risk Dashboard / Hedge Ratios]
    E -.fail.-> H[Debug formula signs]

When Black-Scholes Breaks — and What to Use Instead

Black-Scholes is a brilliant approximation, not a description of reality. The assumptions that matter most for finance teams:

Constant volatility — markets show clear volatility smiles and term structures. Use Heston or a local-vol model for exotic books, or just price each strike with its own implied vol.
Lognormal returns — equity returns have fat tails and jumps. For deep tail risk, layer a Merton jump-diffusion or Monte Carlo. (Our Monte Carlo in Excel guide covers the basics.)
European exercise only — for American options on dividend-payers, switch to a binomial tree (Cox-Ross-Rubinstein) or a trinomial.
No transaction costs, perfect hedging — fine for pricing, dangerous for delta-hedge P&L attribution.

For most equity-linked compensation, convertible bond decomposition, and corporate finance use cases, Black-Scholes is the right tool. For exotic derivatives or hedge fund risk, it is your starting point, not your ending point.

Frequently Asked Questions

Why does my Black-Scholes price not match the market?

Three usual suspects: time to expiry is in days instead of years; the volatility is in percent form (28 vs 0.28); or you are using the non-dividend version on a dividend-paying stock. Check those three first. Real bid/ask spreads also create a 1–5% gap on liquid options and far more on illiquid ones, which is normal.

Can Excel handle American options?

Not with the closed-form Black-Scholes formula — that only prices European options. You can build a Cox-Ross-Rubinstein binomial tree in Excel using a triangular grid and back-induction; 100 steps gives sub-penny accuracy for most equity options. Excel handles 50–200 step trees easily; beyond that, move to Python or a dedicated library.

What volatility should I use for stock-based compensation valuations?

For ASC 718 grants in private companies, use a peer group historical volatility matched to the expected term, typically 4–6 years. Public companies usually use their own historical volatility over a window equal to expected term, blended with implied vol from longer-dated listed options if available. Auditors expect documented methodology, not a single source.

Does Black-Scholes work for FX options?

Yes, with one change — the Garman-Kohlhagen extension. Replace the dividend yield q with the foreign risk-free rate. The Excel formulas are identical; you just rename q and source it from the foreign currency's swap or deposit curve. The math is the same because holding a foreign currency earns interest like a continuous dividend.

How accurate is NORM.S.DIST for tail probabilities?

NORM.S.DIST in Excel 365 is accurate to roughly 15 decimal places in the body of the distribution and the near tails (|x| < 6). For extreme tails (|x| > 7), accuracy degrades but those values are already so close to 0 or 1 that it does not affect option prices materially. For risk applications that care about tails, switch to Python's scipy.stats.norm for full double-precision accuracy.

Where to Go From Here

A working Black-Scholes calculator is the foundation for most of the option valuation work a corporate finance team actually does: stock-based comp under ASC 718, convertible bond bifurcation, employee stock option fair value disclosures, and structured equity-linked instruments. Build the LAMBDA version once and reuse it across deals. For portfolio-level positions where you need to optimize exposure across instruments with different Greeks, portfolio optimization in Excel with Solver covers the constraint-based techniques that minimize aggregate delta and vega exposure.

If you would rather skip the manual build, VeloraAI lets you describe what you want — "price a 6-month call on a $100 stock with 28% vol" — and generates the full Black-Scholes block, including Greeks and parity checks, inside your worksheet. Either way, the principle is the same: understand the formula, validate with parity, and never trust an options price you cannot derive yourself.

Sources: