Free Excel templates

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.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

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).

Get the Library, $79 →

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

MeasureValue
d10.2867
d20.0467
N(d1), N(d2)0.6128, 0.5186
Black-Scholes price11.45
Binomial, 10 steps11.22
Binomial, 1,000 steps11.45 (0.0024 below)
Monte Carlo, 1,000,000 paths11.42 (s.e. 0.017)
Spread across the three models0.0297 (0.26%)
Price at 20% and 28% volatility9.93 and 12.99
Volatility move against model spread103x

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

  1. Change the yellow cells on Inputs: spot, strike, maturity, rate, dividend yield and volatility.
  2. Read the closed-form price and the intermediate quantities beside it.
  3. Watch the lattice converge on Three Models as the step count rises, oscillating around the closed form.
  4. Use The Input sheet to see what your own volatility uncertainty is worth in price.
  5. 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.

Open the companion files →

Other free templates

This template comes from Quantitative Finance. The book is on Amazon.

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.