Option Pricing
Capital Markets Financial Model (Free Excel Download)
Estimate option values with market, volatility, term, and exercise assumptions to support equity compensation decisions, valuation work, and dilution planning.
professionals from Deloitte
Used by professionals from
About this model
Use this model to price a call or put option using the market inputs that matter most: the share price, strike price, time to expiry, interest rates, dividends, and volatility. It turns those assumptions into an option value, payoff view, and the key risk measures used to understand how the position behaves.
The sensitivity table makes it easy to test different share prices and volatility assumptions before making an investment decision. It is useful for analysts, investors, and students who want to understand both an option's value and its downside.
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the Option Pricing
- Six market inputs: spot, strike, days to expiry, risk-free rate, continuous dividend yield, annualised volatility
- Contract sizing inputs (number of contracts, multiplier) so per-share Greeks roll up to position-level dollars
- Pricing sheet: d1, d2, N(d1), N(d2), N(-d1), N(-d2), PV of strike, dividend-adjusted spot, call and put prices, intrinsic value, time value, put-call parity check
- Greeks sheet: Delta, Gamma, Vega, Theta, Rho for the call and the put at per-share scale, plus a position-level scaler (per-share × contracts × multiplier)
- Payoff sheet: 21-point grid of terminal spot from low to high, call and put payoff at expiry, call and put P&L vs premium, break-even flags
- Sensitivity sheet: 7x7 grid of call price across spot and volatility levels, anchored at the current (spot, vol) pair so the centre cell ties to the Pricing sheet
- Summary with call price, put price, position cost, position Delta, moneyness ratio, intrinsic value share, parity error, max loss for each leg
- Green / amber position-cost thresholds with a traffic-light risk flag and a reconciliation status row covering parity and Greek scaling
How the Option Pricing Model Works: Drivers, Calculations and Outputs
This option pricing model uses the Black-Scholes framework to value a single European call or put on an equity. It takes six market inputs and produces theoretical prices, first-order Greeks, payoff and P&L tables, a two-way sensitivity grid, and a summary with consistency and risk checks.
The public download is a values-only preview.
Operating drivers and model assumptions
The model is driven by six inputs: spot price, strike price, days to expiry, risk-free rate, continuous dividend yield, and annualised volatility. Days to expiry are converted to years using a 365-day divisor, with a documented option to switch to 252 trading days, which materially affects Theta.
- Volatility is entered as a decimal (for example, 0.25 for 25%), and the Assumptions sheet uses percentage formatting so the stored value remains correct. The framework assumes European exercise, geometric Brownian motion with constant volatility and dividend yield, a constant risk-free rate, and frictionless markets.
- It values a call and put on the same underlying, so the single set of inputs drives every downstream sheet.
Calculation flow for prices and Greeks
Prices follow the standard Black-Scholes structure. The model computes d1 and d2, then uses the normal cumulative distribution to combine a dividend-adjusted spot value with the present value of the strike.
- The call price equals the dividend-adjusted spot times N(d1) minus the present value of the strike times N(d2); the put price uses the mirrored terms. Five first-order Greeks—Delta, Gamma, Vega, Theta, and Rho—are calculated for both option types.
- Vega and Rho are scaled per one percentage-point move, and Theta is expressed per calendar day. A position-level scaler multiplies each per-share Greek by contracts and the contract multiplier to show dollar exposure.
Outputs: parity, payoff, sensitivity and summary
The Pricing sheet reports both premiums, intrinsic and time value, and a put-call parity error that should be zero within a tolerance.
- The Payoff sheet shows a 21-point grid of terminal spot prices with call and put payoffs at expiry, P&L after premium, and break-even flags.
- The Sensitivity sheet presents a 7-by-7 grid of call prices across spot and volatility levels, anchored at the current spot and volatility so the centre cell ties back to the Pricing sheet.
- The Summary consolidates call and put prices, position cost, position-level Delta, moneyness ratio, intrinsic value share, parity error, maximum loss for each leg, and a traffic-light risk flag based on user-set thresholds.
Practical use and documented checks
This model is built for pricing a single contract, reviewing each Greek next to the price, inspecting the payoff in data form, and stress-testing the call price against spot and volatility. It includes two reconciliation checks: put-call parity and the relationship between per-share Delta, contracts, and multiplier.
- The Summary status reports whether both checks pass. The risk flag separately marks position cost against user thresholds.
- Users should enter volatility as a decimal, days as calendar days, and remember that long calls and puts both carry negative Theta. Put-call parity is a consistency check for European options and is documented as not holding exactly for American options.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
What is the Black-Scholes model?+
Black-Scholes is the standard closed-form pricing model for a European option on an underlying that follows geometric Brownian motion with constant volatility and constant continuous dividend yield. It takes six inputs - spot, strike, time, risk-free rate, dividend yield, volatility - and returns a fair value plus all five first-order Greeks. It is the foundation of every modern derivatives risk system and is taught in every quant finance course.
Does this work for American options?+
Partially. For American calls on a non-dividend-paying stock the European and American prices are identical (it is never optimal to exercise early). For American puts and for dividend-paying underlyings the American option is worth at least the European value, sometimes meaningfully more. For exact American pricing use a binomial or trinomial tree (PDE solver), not Black-Scholes. This workbook is the right tool for European options, index options, and an upper-bound proxy for American calls without dividends.
How is volatility entered?+
As an annualised decimal - 0.25 for 25% vol, not 25. The Assumptions sheet uses percent formatting so the visible value (25.00%) and stored value (0.25) are correct. If you mis-enter 25 the model produces nonsense - d1 explodes and the price blows out. Volatility is the single most important input for at-the-money options; Vega is largest near the money.
Why is Theta negative?+
For a long option position (long call or long put) time decay erodes value, so Theta is negative. The model reports Theta per calendar day (annual Theta / 365) at the trader convention. Short option positions have positive Theta - sellers earn time decay every day the option stays out-of-the-money or near-the-money.
What is the put-call parity check for?+
Put-call parity is the no-arbitrage relationship: Call - Put = PV_S - PV_K = S * EXP(-q * T) - K * EXP(-r * T). It must hold exactly for European options. The Pricing sheet computes the parity error every recalc; if you mis-edit a cell or break a formula, the parity error will jump and the Summary status flag will flip to Off - a built-in audit trip wire.
Have more financial modelling questions? Contact us
Related templates
Convertible Bond Model
Model convertible bond economics with conversion scenarios, dilution, and bond value.
Convertible Note and SAFE Model
Model SAFE and convertible note terms with conversion triggers and equity rounds.
Dividend Discount Model
Valuation of dividend-paying stocks using multi-stage dividend growth and terminal yield.
DCF Model
Discounted cash flow valuation model with explicit forecast, terminal value, and WACC.
