A free Black-Scholes and binomial option pricing model in Excel
The closed form with d1, d2 and N(d), a Cox-Ross-Rubinstein lattice from 10 to 1,000 steps without building a tree, simulation results with their standard errors, and the volatility input priced against the model choice: five sheets, no macros.
The file prices one at-the-money call: spot 100, strike 100, one year, a 4% risk-free rate and 24% implied volatility. Black-Scholes gives 11.45. The binomial lattice converges to it, from 11.22 at ten steps to within 0.0024 at a thousand, and a million simulated paths land at 11.42 with a standard error of 0.017. The three models disagree by 0.03, a quarter of a per cent of the price. Moving volatility from 20% to 28% moves the price by 3.06: 103 times the whole disagreement between the models.
Get the template
One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.
Black_Scholes_Binomial_Monte_Carlo.xlsx (16 KB)
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
The book behind this model
This file is one worked case from Quantitative Finance. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 19 paperbacks by Julian R. Sterling on markets, funds and compliance, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Five sheets. Yellow cells on Inputs are the only inputs; everything else is a formula.
Read me
What the workbook does, how the lattice is computed without a tree, and the honest limit on simulation in a spreadsheet.
Inputs
Spot, strike, maturity, risk-free rate, dividend yield, implied volatility and the number of contracts, then d1, d2, N(d1), N(d2), the density and the closed-form price.
Three Models
The closed form; the lattice at 10, 25, 50, 100, 250, 500 and 1,000 steps, each computed in closed form from the binomial distribution; simulation at 1,000 to 1,000,000 paths with standard errors; and the spread across the three.
The Input
The same option at 20%, 22%, 24%, 26% and 28% volatility, and the ratio of what volatility moves to what the model choice moves.
Checks
Every figure the book prints against the live formula: 20 of 20 pass.
What the worked case returns
Measure
Value
d1
0.2867
d2
0.0467
N(d1), N(d2)
0.6128, 0.5186
Black-Scholes price
11.45
Binomial, 10 steps
11.22
Binomial, 1,000 steps
11.45 (0.0024 below)
Monte Carlo, 1,000,000 paths
11.42 (s.e. 0.017)
Spread across the three models
0.0297 (0.26%)
Price at 20% and 28% volatility
9.93 and 12.99
Volatility move against model spread
103x
The model is not where the risk is. Every one of the three methods is correct to within a few hundredths, while the one input nobody can observe, implied volatility, moves the price by more than a quarter for eight points. A pricing review that spends its time on the model and not on the volatility surface is looking in the wrong place.
How to use it on your own option
Change the yellow cells on Inputs: spot, strike, maturity, rate, dividend yield and volatility.
Read the closed-form price and the intermediate quantities beside it.
Watch the lattice converge on Three Models as the step count rises, oscillating around the closed form.
Use The Input sheet to see what your own volatility uncertainty is worth in price.
Keep Checks as a test: change the inputs and the book's figures stop matching, which is expected.
Questions people ask about it
Is this Black-Scholes calculator really free?
Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Does it use macros or circular references?
No macros and no circular reference. The lattice is computed in closed form from the binomial distribution, so a 1,000-step tree needs no 1,000-column sheet.
Are the Monte Carlo prices simulated in the file?
No, and the file says so. A million simulated paths cannot live in a spreadsheet, so the simulation prices and standard errors are typed results from the book; the closed form and the lattice are live.
Does it price puts and American options?
This file prices a European call. The put follows by put-call parity, which the companion workbooks test; the American exercise feature is not modelled here.
The rest of the files
This template is one of the four companion workbooks for Quantitative Finance: the others compute all five Greeks with a finite-difference check, price what volatility is worth, and lay out a delta hedge step by step. Free, like this one.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.