# Option Pricing

See what an option is worth today, how its price may change, and where the break-even sits.

- Canonical: https://finamodel.com/templates/option-pricing
- Excel download: https://finamodel.com/templates/option-pricing.xlsx
- Category: Capital Markets
- Model type: Valuation
- Difficulty: Advanced
- Audiences: Investors & analysts, Bankers & advisors, Options traders, Derivatives analysts, Quants, Finance students
- Tags: options, black-scholes, greeks, derivatives, valuation

## Overview

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's included

- 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
- Six market inputs - spot, strike, days to expiry, risk-free rate, dividend yield, and annualised volatility - plus contracts and multiplier
- Pricing sheet with d1, d2, N(d1), N(d2), N(-d1), N(-d2), n(d1), discount factors, dividend-adjusted spot and PV of strike
- Call and put prices via the closed-form Black-Scholes formula with intrinsic and time-value decomposition
- Put-call parity check with reconciliation status against a user tolerance band
- Five first-order Greeks (Delta, Gamma, Vega, Theta, Rho) per share for both call and put, scaled to the position level
- 21-point payoff and P&L grid across terminal spot, plus break-even price for the long call and long put
- 7x7 two-way call-price sensitivity grid across spot and volatility, anchored at the current market
- Summary with call and put prices, position cost, position Delta, moneyness, intrinsic share, parity error, max loss, break-even, and traffic-light risk flag

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

## Built for the options desk

When the question is "what is this contract worth and how does it move?", a Black-Scholes price with every first-order Greek next to it is the cleanest answer. This template gives traders, derivatives analysts, and finance students an audit-ready pricing workbook that ties every quoted number - price, Delta, Gamma, Vega, Theta, Rho - to the six market inputs that drive them.

## Designed for one-edit sensitivity

Every input - spot, strike, days, rate, dividend, vol, contracts, multiplier - is a single named-range cell on Assumptions. Flex vol from 25% to 30% and the call price, every Greek, the payoff grid, the sensitivity grid, and the position cost all recompute - no formula rewrites. The 7x7 spot-vol grid lets you eyeball convexity and vega exposure at a glance.

## Honest about the assumptions

European exercise, geometric Brownian motion, constant vol, constant rate, continuous dividend yield, frictionless markets. Days are converted to years at /365 (trader convention); flip to /252 for trading-day Theta with one cell edit. Vega and Rho are scaled per 1% move; Theta is reported per calendar day. Put-call parity is enforced as an explicit reconciliation check so any input drift surfaces immediately.

## Built for the options desk

When the question is "what is this contract worth and how does it move?", a Black-Scholes price with every first-order Greek next to it is the cleanest answer. This template gives traders, derivatives analysts, and finance students an audit-ready pricing workbook that ties every quoted number - price, Delta, Gamma, Vega, Theta, Rho - to the six market inputs that drive them.

## Designed for one-edit sensitivity

Every input - spot, strike, days, rate, dividend, vol, contracts, multiplier - is a single named-range cell on Assumptions. Flex vol from 25% to 30% and the call price, every Greek, the payoff grid, the sensitivity grid, and the position cost all recompute - no formula rewrites. The 7x7 spot-vol grid lets you eyeball convexity and vega exposure at a glance.

## Honest about the assumptions

European exercise, geometric Brownian motion, constant vol, constant rate, continuous dividend yield, frictionless markets. Days are converted to years at /365 (trader convention); flip to /252 for trading-day Theta with one cell edit. Vega and Rho are scaled per 1% move; Theta is reported per calendar day. Put-call parity is enforced as an explicit reconciliation check so any input drift surfaces immediately.

## Workbook structure

### Cover

Workbook overview, sheet legend, and tab-colour key for navigation.

- Title and scope framing
- Sheet-by-sheet purpose summary
- Tab-colour legend

### Assumptions

Every driver in one sheet: contract definition, grid bounds, risk thresholds.

- Spot, strike, days to expiry, risk-free rate, dividend yield, annualised volatility
- Number of contracts and per-contract multiplier (default 100 for US equity options)
- Spot grid bounds and vol step for the Payoff and Sensitivity sheets
- Green and amber position-cost thresholds and reconciliation tolerance

### Pricing

Black-Scholes mechanics for both legs plus the put-call parity check.

- d1 = (LN(S/K) + (r - q + sigma^2 / 2) * T) / (sigma * SQRT(T)); d2 = d1 - sigma * SQRT(T)
- PV of strike = K * EXP(-r * T); dividend-adjusted spot = S * EXP(-q * T)
- Call = PV_S * N(d1) - PV_K * N(d2); Put = PV_K * N(-d2) - PV_S * N(-d1)
- Intrinsic value = MAX(S - K, 0) for call, MAX(K - S, 0) for put; time value = price - intrinsic
- Parity error = (Call - Put) - (PV_S - PV_K); flagged against tolerance

### Greeks

Five first-order Greeks at per-share and position-level scale.

- Delta_Call = EXP(-q * T) * N(d1); Delta_Put = -EXP(-q * T) * N(-d1)
- Gamma = EXP(-q * T) * n(d1) / (S * sigma * SQRT(T)); same for both legs
- Vega = S * EXP(-q * T) * n(d1) * SQRT(T) / 100 (per 1% vol move)
- Theta per calendar day = annual Theta / 365
- Rho_Call = K * T * EXP(-r * T) * N(d2) / 100; Rho_Put = -K * T * EXP(-r * T) * N(-d2) / 100
- Position-level scaler = per-share Greek * contracts * multiplier

### Payoff

21-point grid of terminal spot with payoff, P&L, and break-even flags.

- Terminal spot from spot_low to spot_high in 20 equal steps
- Call payoff at expiry = MAX(S_T - K, 0); put payoff = MAX(K - S_T, 0)
- Call P&L = call payoff - call premium; put P&L = put payoff - put premium
- Break-even flag highlights the spot at which P&L crosses zero (K + call premium; K - put premium)

### Sensitivity

Two-way 7x7 grid of call price across spot and volatility.

- Seven spot levels centred on the current spot at user-set step
- Seven volatility levels centred on the current vol at user-set step
- Each cell reprices the call using row spot and column vol via Black-Scholes
- Centre cell ties to the Pricing sheet within tolerance

### Summary

Headline price, position cost, key ratios, and risk status.

- Call price, put price, position cost (call + put for a straddle-like view)
- Position Delta, moneyness = S / K, intrinsic value share, parity error
- Max loss for long call and long put (= premium paid * contracts * multiplier)
- Status flag: On track / Watch / Off track against position-cost thresholds
- Reconciliation row: parity OK and Greek scaling OK within tolerance

### Cover

Workbook overview, sheet legend, and tab-colour key for navigation.

- Title and scope framing
- Sheet-by-sheet purpose summary
- Tab-colour legend

### Assumptions

Every driver in one sheet: contract definition, grid bounds, risk thresholds.

- Spot, strike, days to expiry, risk-free rate, dividend yield, annualised volatility
- Number of contracts and per-contract multiplier (default 100 for US equity options)
- Spot grid bounds and vol step for the Payoff and Sensitivity sheets
- Green and amber position-cost thresholds and reconciliation tolerance

### Pricing

Black-Scholes mechanics for both legs plus the put-call parity check.

- d1 = (LN(S/K) + (r - q + sigma^2 / 2) * T) / (sigma * SQRT(T)); d2 = d1 - sigma * SQRT(T)
- PV of strike = K * EXP(-r * T); dividend-adjusted spot = S * EXP(-q * T)
- Call = PV_S * N(d1) - PV_K * N(d2); Put = PV_K * N(-d2) - PV_S * N(-d1)
- Intrinsic value = MAX(S - K, 0) for call, MAX(K - S, 0) for put; time value = price - intrinsic
- Parity error = (Call - Put) - (PV_S - PV_K); flagged against tolerance

### Greeks

Five first-order Greeks at per-share and position-level scale.

- Delta_Call = EXP(-q * T) * N(d1); Delta_Put = -EXP(-q * T) * N(-d1)
- Gamma = EXP(-q * T) * n(d1) / (S * sigma * SQRT(T)); same for both legs
- Vega = S * EXP(-q * T) * n(d1) * SQRT(T) / 100 (per 1% vol move)
- Theta per calendar day = annual Theta / 365
- Rho_Call = K * T * EXP(-r * T) * N(d2) / 100; Rho_Put = -K * T * EXP(-r * T) * N(-d2) / 100
- Position-level scaler = per-share Greek * contracts * multiplier

### Payoff

21-point grid of terminal spot with payoff, P&L, and break-even flags.

- Terminal spot from spot_low to spot_high in 20 equal steps
- Call payoff at expiry = MAX(S_T - K, 0); put payoff = MAX(K - S_T, 0)
- Call P&L = call payoff - call premium; put P&L = put payoff - put premium
- Break-even flag highlights the spot at which P&L crosses zero (K + call premium; K - put premium)

### Sensitivity

Two-way 7x7 grid of call price across spot and volatility.

- Seven spot levels centred on the current spot at user-set step
- Seven volatility levels centred on the current vol at user-set step
- Each cell reprices the call using row spot and column vol via Black-Scholes
- Centre cell ties to the Pricing sheet within tolerance

### Summary

Headline price, position cost, key ratios, and risk status.

- Call price, put price, position cost (call + put for a straddle-like view)
- Position Delta, moneyness = S / K, intrinsic value share, parity error
- Max loss for long call and long put (= premium paid * contracts * multiplier)
- Status flag: On track / Watch / Off track against position-cost thresholds
- Reconciliation row: parity OK and Greek scaling OK within tolerance

## Features

- **Closed-form Black-Scholes:** Standard European call and put pricing in a single workbook with the dividend-adjusted spot, PV of strike, and the normal CDF and PDF wired through Excel's native NORM.S.DIST - no add-ins, no VBA, no iterative solver needed.
- **Greeks per share and per position:** Delta, Gamma, Vega, Theta, Rho all show per-share next to position-level (× contracts × multiplier) so risk and exposure are visible in one glance - Vega is per 1% vol move, Rho is per 1% rate move, Theta is per calendar day.
- **Payoff and two-way sensitivity:** A 21-point payoff grid sweeps spot at expiry for both legs and a 7x7 spot-vol grid stresses the call price - anchored at the current spot and vol so the centre cell ties to the Pricing sheet.

## Use cases

- **Single-contract pricing:** Price a vanilla European option on an index or single stock, see every Greek next to the premium, and verify the result against put-call parity before quoting.
- **Strategy preparation:** Stress the call price across a spot and volatility grid before structuring a calendar, vertical, or straddle. The two-way sensitivity reveals where the position is most exposed and where break-evens sit.
- **Educational reference:** Walk through every Black-Scholes input - d1, d2, N(d1), the dividend-adjusted spot, the PV of strike - laid out cell by cell so the closed-form formula is fully transparent for finance students or new desk hires.

## Frequently asked questions

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

## Related templates

- [Convertible Bond Model](https://finamodel.com/templates/convertible-bond-model)
- [Convertible Note and SAFE Model](https://finamodel.com/templates/convertible-safe-model)
- [Dividend Discount Model](https://finamodel.com/templates/dividend-discount-model)