Options Pricing & Greeks Model
Price options, visualize Greeks, and analyze strategies with a professional derivatives toolkit built in Excel across 6 focused worksheets.
Click the tabs at the bottom to navigate between worksheets.
Options are among the most versatile financial instruments available, but pricing them correctly and understanding their risk characteristics requires solid quantitative tools. The interplay between underlying price, strike, time to expiry, volatility, and interest rates creates complex, non-linear behavior that single-number analysis can't capture.
This model implements the Black-Scholes-Merton framework and extends it with practical tools that traders, risk managers, and students actually need. Price calls and puts instantly, visualize how each Greek changes across different market conditions, build volatility surfaces from market data, and analyze option strategy payoffs — all in a clean, interactive Excel environment.
What's Inside
The model contains 6 integrated worksheets. Here's what each one does and why it matters.
Inputs & Pricing
Enter option parameters and instantly see Black-Scholes prices for calls and puts, along with all first and second-order Greeks. Covers spot price, strike, expiry, vol, rate inputs, call and put prices, full greeks output (delta through charm) and put-call parity verification.
Greeks Dashboard
Interactive visualizations showing how each Greek behaves across a range of underlying prices and time to expiry. Covers delta, gamma, theta, vega charts, greeks vs. spot price plots, greeks vs. time to expiry and surface plots for key greeks.
Volatility Surface
Build and visualize an implied volatility surface from market option prices across strikes and maturities. Covers strike × maturity vol grid, 3d surface visualization, volatility smile/skew analysis and term structure of volatility.
Payoff Diagrams
Visualize P&L profiles for single options and multi-leg strategies at expiration and before expiry. Covers single call/put payoffs, multi-leg strategy builder, p&l at expiry vs. current and break-even analysis.
Sensitivity Analysis
Stress-test option positions across changes in spot, volatility, time, and rates simultaneously. Covers two-way sensitivity tables, p&l impact of spot moves, vega exposure across vol changes and time decay projection.
Summary & Docs
Formula documentation, methodology notes, and quick-reference guide for all model features. Covers black-scholes formula derivation, greeks definitions and formulas, model assumptions and limitations and usage guide.
Key Formulas & Methods
The model is built on established quantitative methods used by professionals worldwide.
Black-Scholes Call Price
C = S·N(d₁) − K·e⁻ʳᵗ·N(d₂)
The theoretical price of a European call option, where N() is the cumulative normal distribution, S is spot, K is strike, r is rate, t is time.
Delta
Δ = N(d₁) for calls
The rate of change of option price with respect to the underlying price. Ranges from 0 to 1 for calls, -1 to 0 for puts.
Gamma
Γ = N'(d₁) / (S·σ·√t)
The rate of change of delta. Highest for at-the-money options near expiry. Measures convexity of the option position.
Implied Volatility
σ_imp: C_market = BSM(S,K,t,r,σ_imp)
The volatility that, when plugged into Black-Scholes, produces the observed market price. Solved numerically via iteration.
How to Build This Model
Understanding how a model is constructed helps you customize it with confidence. Here is the methodology behind this template and what matters most at each stage.
1.Understand the Black-Scholes Framework and Its Assumptions
The Black-Scholes model remains the foundation of options pricing despite being over 50 years old. It assumes that the underlying asset follows a geometric Brownian motion with constant volatility, that markets are frictionless (no transaction costs, continuous trading), and that the risk-free rate is constant. Understanding these assumptions is critical because every real-world deviation from them — volatility smiles, jumps, discrete trading — represents a source of model risk. Building a pricing model without understanding its theoretical foundations leads to dangerous overconfidence in the outputs.
2.Implement the Pricing Engine with Greeks Calculations
Build the Black-Scholes formula for both calls and puts, computing theoretical price from five inputs: spot price, strike price, time to expiration, risk-free rate, and implied volatility. Then implement the Greeks — Delta (price sensitivity to spot), Gamma (Delta sensitivity to spot), Theta (time decay per day), Vega (price sensitivity to volatility), and Rho (price sensitivity to interest rates). The Greeks are arguably more important than the price itself, because they tell you how your position will behave as market conditions change. A trader who understands Greeks can manage risk dynamically; one who only knows the price cannot.
3.Build Implied Volatility Calculation and Surface
Market prices of options embed the market's expectation of future volatility — this is implied volatility (IV). Since there is no closed-form inverse of Black-Scholes, IV must be solved numerically using Newton-Raphson iteration or bisection. Build an IV calculator that takes the market price and solves for the volatility that reproduces it. Then map IV across different strikes and expirations to construct the volatility surface. The shape of this surface — smiles, skews, term structure — contains rich information about how the market prices risk and is essential for pricing exotic options or trading volatility itself.
4.Model Multi-Leg Strategies with Combined Payoffs
Professional options trading involves strategies that combine multiple options into a single position — spreads, strangles, straddles, butterflies, iron condors, and more. Build a strategy analyzer that sums the payoff profiles and Greeks of individual legs to show the combined position's behavior. For each strategy, display the payoff at expiration, the breakeven points, maximum profit and loss, and how the position's Greeks evolve as the underlying moves. This allows traders to design positions that express specific market views while defining their risk precisely.
5.Stress-Test with Sensitivity Analysis and Scenario Modeling
Options positions can behave non-linearly — a position that looks safe in normal markets can generate large losses during volatility spikes or gap moves. Build sensitivity tables that show P&L across a matrix of spot price changes and volatility changes simultaneously. Test extreme scenarios: what happens if the underlying drops 20% and volatility doubles? What is the impact of accelerating time decay as expiration approaches? This multi-dimensional sensitivity analysis is how professional trading desks manage the risk of options portfolios, and it transforms the model from a pricing calculator into a genuine risk management tool.
Who Is This For?
This model is designed for a range of professionals and use cases.
Options Traders. Price options, assess Greeks exposure, and analyze strategies before execution.
Risk Managers. Monitor portfolio Greeks and stress-test option positions against market moves.
Derivatives Analysts. Build volatility surfaces and analyze relative value across the options surface.
Portfolio Managers. Understand the risk characteristics of option overlays and hedging strategies.
Quantitative Finance Students. Learn Black-Scholes pricing, Greeks, and volatility concepts through hands-on modeling.
CFA & FRM Candidates. Practice derivatives concepts with a working model that reinforces exam material.
Why Use This Model?
- —Instantly price any European option with Black-Scholes-Merton
- —Visualize all Greeks across spot price and time dimensions
- —Build volatility surfaces from market data for relative value analysis
- —Analyze single and multi-leg option strategy payoffs
- —Stress-test positions with two-way sensitivity tables
- —Fully documented formulas for learning and auditing
- —No Bloomberg terminal or paid software required
- —Interactive — change inputs and see results update instantly
Frequently Asked Questions
Tagged: options pricing · Black-Scholes · Greeks · delta · gamma · theta · vega · volatility surface · derivatives · payoff diagram