Black Scholes Excel: Build a Live Option Pricing Model and Greeks Calculator (2026)

Black Scholes Excel searches usually come from one of two places. Either you are studying for an exam and you want to see the equation broken into cells so it stops being a black box, or you already understand the theory and you are tired of retyping a spot price and a volatility guess every time you want to value a contract. This guide handles both. It walks the entire Black-Scholes model as a spreadsheet, term by term, using nothing but native Excel functions so every intermediate number is visible and auditable. Then it wires the three inputs that actually change during the day, spot price, dividend yield and implied volatility, straight into live market data through MarketXLS, so the model recalculates itself instead of going stale the moment you close the file. Two complete workbooks are attached at the end, one pre-filled with static values you can read offline and one built entirely from live formulas.

This is an educational walkthrough. Every ticker below is an example of how the formulas behave, not a recommendation to trade anything.

Black Scholes Excel inputs at a glance

Black-Scholes needs six things. Five of them you can pull live. Here is the complete mapping from model term to spreadsheet formula.

The only genuinely manual inputs are the strike, the expiry date and the risk-free rate, and none of those change while you are looking at the screen. Everything else updates on recalculation. That is the difference between a spreadsheet that teaches you the model once and a spreadsheet you can actually keep open.

What Black-Scholes does, in one paragraph

Black-Scholes answers a narrow question: given a stock trading at S today, what is the fair value of the right to buy it at K on a specific future date, if the stock's returns are lognormally distributed with constant volatility sigma and you can borrow and lend at rate r? The answer has two pieces. The first piece is the expected value of receiving the stock, weighted by the probability you end up owning it. The second piece is the expected cost of paying the strike, discounted back to today and weighted by the probability you actually pay it. The call is the first minus the second. That is the entire intuition, and once you see it as two weighted terms, the formula stops looking arbitrary.

For a European call:

C = S * e^(-qT) * N(d1) - K * e^(-rT) * N(d2)

For a European put:

P = K * e^(-rT) * N(-d2) - S * e^(-qT) * N(-d1)

Where:

d1 = [ ln(S/K) + (r - q + sigma^2 / 2) * T ] / ( sigma * sqrt(T) )
d2 = d1 - sigma * sqrt(T)

N(d2) is the risk-neutral probability the call expires in the money. N(d1) is the dividend-adjusted hedge ratio, which is also the delta. Those two numbers do most of the work in the model and both of them are a single Excel function away.

Building Black Scholes in Excel, cell by cell

Here is the layout used in the attached workbook. Column A holds labels, column C holds values. Yellow cells are the only ones you type into.

The input block

Two details matter here. DividendYield and ImpliedVolatility30d both return decimals, so 0.0034 means 0.34 percent and 0.2405 means 24.05 percent. Format the cells as percentages and do not multiply by 100, or you will price the option with a volatility of 2,405 percent and wonder why the call is worth more than the stock. The override cell in C14 exists because there are legitimate reasons to price a contract at a volatility other than the current market IV, and hard-coding over a live formula is how spreadsheets rot.

The intermediate terms

NORMSDIST is the legacy name for the cumulative standard normal. Excel also offers NORM.S.DIST(x,TRUE), and both work, but the legacy name avoids the _xlfn prefix problem when a workbook is generated programmatically or opened in an older version. The density function n(d1) in C24 is not part of the price, but it drives gamma and vega, so it is worth breaking out rather than repeating inside three separate formulas.

The price

C34 is the check that catches most build errors. Put-call parity says a call minus a put must equal the dividend-adjusted stock minus the discounted strike. If that cell is not effectively zero, you have a sign error or a mismatched reference somewhere in the block above. It costs one cell and saves an afternoon.

A worked example

Using Apple as of 7 August 2026, with a 315 strike expiring 18 September 2026:

That produces d1 = 0.027256 and d2 = -0.054326, giving N(d1) = 0.510872 and N(d2) = 0.478338. The call values at $10.02 and the put at $10.37. The stock sits slightly below the strike, so the call has zero intrinsic value and the entire $10.02 is time value. N(d2) of 0.478 says the model puts roughly a 48 percent risk-neutral probability on the call finishing in the money, which is what you would expect for a contract struck just above spot.

The Greeks, from the same cells

The Greeks are partial derivatives of the price with respect to each input. Every one of them reuses terms you have already computed, which is the practical reason to break out d1, d2 and the two discount factors instead of nesting everything into a single monster formula.

Read those numbers together and the position tells you what it is. Delta of 0.51 means the call moves about 51 cents for the first dollar the stock moves. Gamma of 0.0156 means that after a one dollar move, delta itself has shifted by about 1.6 points, so the 51 becomes roughly 52 or 49. Vega of $0.42 means a single percentage point of implied volatility is worth more than three days of time decay at the current theta of 13.6 cents per day. For a 42-day at-the-money option, volatility is the dominant risk, not direction and not time. That is a genuinely useful thing to know before putting the trade on, and it takes six cells to compute.

A note on scaling. Vega is divided by 100 and theta by 365 because that is how trading platforms quote them, as dollars per volatility point and dollars per calendar day. The raw derivatives are per unit of volatility and per year. The scaling is convention, not mathematics, so if your numbers look off by a factor of 100 against another calculator, check the convention before checking the formula.

Where the volatility number comes from

Black-Scholes takes exactly one volatility input. Choosing it is the largest modelling decision in the entire exercise, and it is the reason two people can build identical spreadsheets and get different prices.

There are two honest candidates, and MarketXLS exposes both:

=ImpliedVolatility30d("AAPL")        ' What the option market expects
=StockVolatilityThirtyDays("AAPL")   ' What the stock actually did
=ImpliedVolatilityRank1y("AAPL")     ' Where current IV sits in its own 1-year range

ImpliedVolatility30d returns an annualized decimal and is what most people want, because it is the number embedded in the prices you would actually trade against. StockVolatilityThirtyDays returns the standard deviation of daily returns over the trailing thirty sessions, so it needs annualizing before it can go into the model. The attached workbook does that with =StockVolatilityThirtyDays(A5)*SQRT(252) and puts the 252 in an input cell, so if your house convention uses a different trading-day count you change one number and every dependent column follows.

ImpliedVolatilityRank1y is the context check. An implied volatility of 40 percent means nothing on its own. An IV rank of 12 tells you that 40 percent is near the bottom of where this name has traded for a year, and an IV rank of 88 tells you the opposite.

Model versus market across twenty large caps

Here is what happens when you run the same thirty-day at-the-money call through the model for twenty liquid names, using live implied volatility for each. All values as of 7 August 2026, with a 4 percent risk-free rate.

The final column is the one worth staring at. It normalizes premium against price and shows the cost of a thirty-day at-the-money call ranging from about 2.2 percent of spot on Bank of America and Coca-Cola to over 9 percent on Intel. That entire spread comes from a single input, volatility, since strike, time and rate are held constant across every row. Black-Scholes is, in practice, a machine for converting a volatility assumption into a dollar premium, and this table is that statement made concrete.

The realized volatility column is deliberately shown next to implied rather than folded into a signal. On some names implied sits above realized and on others it sits below, and the gap is a description of the current state of two different measurements, not a trade idea. Palantir is the extreme case here, with trailing realized volatility running far above what the thirty-day options are pricing, which usually means a large recent move that the option market does not expect to repeat.

Using the MarketXLS built-in as a cross-check

Building the model by hand is how you learn it. Once it is built, MarketXLS gives you the same calculation in a single function, which is useful for checking your work and for pricing dozens of contracts at once without dragging a twelve-cell block across a sheet.

=BlackScholesOptionValueWithUserInputs(313.33, 315, 42, 0.04, 0.2405, "Call")
=BlackScholesOptionValue("AAPL", 315, 42, 0.04, 0.2405, "Call")

The first version takes every input explicitly. The second pulls the live spot price for the ticker so you only supply the contract terms. Both take days to expiry rather than years, which is a small but easy trip hazard if you have just finished computing T in years for the manual version.

One difference is worth knowing. The built-in function carries no dividend term. On the Apple example above, the hand-built model with a 0.34 percent dividend yield returns $10.0245 for the call, while the built-in returns $10.0872. The six-cent gap is exactly the dividend adjustment. On a zero-yield name the two agree to the last decimal. The attached workbook prints both side by side and reports the difference in its own cell, so the discrepancy is documented rather than mysterious.

Cross-checking against live option quotes

A theoretical value is only interesting next to a traded price. MarketXLS lets you pull the actual contract into the same sheet:

=OptionSymbol("AAPL", DATE(2026,9,18), "Call", 315)   ' Builds the contract symbol
=OPT_Bid(C50)                                          ' Live bid
=OPT_Ask(C50)                                          ' Live ask
=OPT_Last(C50)                                         ' Last traded price
=OPT_DaysToExpiration(C50)                             ' Calendar days remaining

Then run the model backwards. Instead of feeding in a volatility and getting a price, feed in the market price and get the volatility that justifies it:

=opt_ImpliedVolatility(C6, C53, C8, "Call", C7, C11)
=opt_Delta(C6, C53, C8, "Call", C7, C11, C55)
=opt_Gamma(C6, C53, C8, "Call", C7, C11, C55)
=opt_Vega(C6, C53, C8, "Call", C7, C11, C55)
=opt_Theta(C6, C53, C8, "Call", C7, C11, C55)
=opt_Rho(C6, C53, C8, "Call", C7, C11, C55)

opt_ImpliedVolatility solves Black-Scholes iteratively for sigma. If the number it returns differs materially from ImpliedVolatility30d for the same underlying, that is not an error. The thirty-day figure is a surface-level summary across strikes, while the contract-level solve is specific to the strike and expiry you asked about. The difference between them is skew, and seeing it appear in two adjacent cells is the fastest way to understand why the constant-volatility assumption is the model's weakest joint.

Where the model stops working

Black-Scholes is a good model, which is different from a correct one. Four assumptions fail in ways that matter:

Constant volatility. The model assumes one sigma for all strikes and all expirations. Real option surfaces show skew across strikes and a term structure across expirations. This is not a rounding problem. Deep out-of-the-money puts routinely trade at implied volatilities many points above at-the-money contracts on the same name and date.

Lognormal returns with no jumps. Earnings gaps, guidance changes and macro surprises produce moves that a continuous diffusion assigns essentially zero probability. The model systematically underprices the tails.

European exercise. Listed US equity options are American. For calls on non-dividend payers early exercise is almost never optimal, so the difference is negligible. For puts, and for calls on high-yield names heading into an ex-dividend date, early exercise carries real value that Black-Scholes does not capture. MarketXLS exposes AmericanOptionValue for those cases, along with HestonOptionValue for stochastic volatility and Black76OptionValue for futures-style underlyings.

Frictionless continuous hedging. The derivation assumes you can rebalance continuously at no cost. Nobody can. Gamma is precisely the measure of how badly that assumption bites, since it tells you how fast your hedge goes stale.

None of this makes the model useless. It makes it a common language. When a desk quotes a contract in volatility terms rather than dollars, they are using Black-Scholes as a translation layer, not as a truth claim. Your spreadsheet should do the same thing.

What is inside the two workbooks

Both files carry eleven sheets and the same structure. The sample workbook holds static values as of 7 August 2026 with the exact formula behind every number stored as a cell comment, so you can read the whole model without the add-in installed. The template workbook holds live formulas throughout.

The Scenario Analysis sheet is the one most people find useful fastest. Reading across a row changes implied volatility without moving the stock. Reading down a column moves the stock without touching volatility. Seeing both axes at once makes it obvious that an option is a simultaneous bet on two variables, which is the single most common thing new options traders get wrong.

Download the templates:

• - Pre-filled with data as of 7 August 2026, every cell commented with the formula behind it
• - Live-updating formulas throughout

Frequently asked questions

Does Excel have a built-in Black-Scholes function?

No. Excel ships NORMSDIST, LN, EXP and SQRT, which are the four building blocks, but there is no native option pricing function. You either assemble the equation yourself, which is what the calculator sheet in the attached workbook does, or you use an add-in. MarketXLS provides BlackScholesOptionValue and BlackScholesOptionValueWithUserInputs as single-cell calls.

Should I use NORMSDIST or NORM.S.DIST?

Either works and both return identical results. NORMSDIST(x) is the legacy single-argument form. NORM.S.DIST(x,TRUE) is the modern equivalent and needs the second argument set to TRUE for the cumulative distribution. The legacy name is more portable across Excel versions and avoids _xlfn prefix issues in programmatically generated files, which is why the attached workbook uses it.

Why does my calculated value not match the market price of the option?

Almost always the volatility input. If you priced with a thirty-day at-the-money implied volatility but your contract is a long-dated or far out-of-the-money strike, skew and term structure mean the market is using a different sigma than you are. The fastest diagnostic is to run the model backwards with opt_ImpliedVolatility on the market mid and compare the volatility that comes back to the one you fed in. Second most common cause is a time convention mismatch, calendar days over 365 against trading days over 252.

What risk-free rate should I use?

Use the Treasury yield whose maturity is closest to your option's expiry. For a forty-day option that is the one-month or three-month bill, not the ten-year. Rho is the smallest of the Greeks for short-dated equity options, so a reasonable approximation costs you very little, but the further out the expiry the more the choice matters.

Can I use this for American options?

Black-Scholes prices European exercise, so treat it as an approximation for American contracts. For calls on stocks that pay no dividend, the approximation is excellent because early exercise is not optimal. For puts, and for calls approaching an ex-dividend date on a high-yield name, early exercise has real value the model ignores. MarketXLS offers AmericanOptionValue, which uses a binomial tree, for those situations.

Do I need historical or implied volatility as the input?

Implied, if your goal is to match or compare against traded prices, because implied volatility is the number already embedded in those prices. Historical or realized volatility answers a different question, which is what the stock actually did rather than what the market expects. The attached workbook shows both columns side by side on the Vol Comparison sheet precisely so you can see how far apart they can drift.

How do I get the Greeks without rebuilding the formulas?

Pass the contract's market price to the dedicated functions: opt_Delta, opt_Gamma, opt_Vega, opt_Theta and opt_Rho. Each takes the current stock price, the option market price, the expiry, the option type, the strike, and optionally the risk-free rate and implied volatility. They return the Greek implied by the traded price rather than by your volatility assumption, which is usually what you want once you are past the learning stage.

The bottom line

Black Scholes Excel is not hard to build. It is roughly twenty cells for the price and eight more for the Greeks, all of them using functions Excel has shipped for decades. What makes a Black-Scholes spreadsheet genuinely useful rather than an exercise is the plumbing around it: spot price that refreshes, dividend yield that is correct rather than assumed, an implied volatility that reflects what the option market is actually charging today, and a live quote sitting next to the theoretical value so you can see the gap instead of guessing at it. That plumbing is what MarketXLS supplies, and it is the reason the same workbook is still accurate a month from now.

Build the model once by hand so you understand every term. Then let the data maintain itself.

Explore the full function library at MarketXLS, or book a demo to see the options analytics functions working live in a spreadsheet. For related reading, see our guides on the fundamentals of option pricing, the binomial option pricing model in Excel, and options data in Excel including Greeks and analytics.

This article is for educational purposes only and does not constitute investment advice, a recommendation, or a solicitation to buy or sell any security or option. Options involve substantial risk and are not suitable for all investors. Theoretical model values are not price forecasts. Verify all data independently and consult a licensed financial professional before trading.