Home › Templates › Options Greeks spreadsheet

Options Greeks Spreadsheet

A free option Greeks calculator for Excel and Google Sheets. Type the underlying price, strike, expiry, implied volatility, rate and dividend yield, and it gives the Black-Scholes-Merton price, delta, gamma, theta per day, vega per vol point and rho for each option. It adds up the Greeks across up to 20 option and stock legs and re-prices the whole position on a grid of price and volatility moves.

Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up, no email gate.

125.9net delta: behaves like 126 shares for small moves

−4.99net gamma: delta change per $1 move

$3.62theta per calendar day, all else equal

−$5.03vega per 1 point rise in implied vol

$3.72rho per 1 point rise in rates

#	Leg	Strike	Expiry	IV	Model price	Delta	Gamma	Theta/day	Vega/pt	Rho/pt	Position delta
1	Long 100 stock	–	–	–	$100.00	1.000	0.0000	0.0000	0.0000	0.0000	100.0
2	Short 1 call	$110.00	2026-12-18	24%	$0.64	0.151	0.0275	−0.0228	0.0831	0.0182	−15.1
3	Long 1 put	$90.00	2026-12-18	28%	$0.65	−0.125	0.0207	−0.0212	0.0732	−0.0166	−12.5
4	Long 2 calls	$100.00	2027-01-15	25%	$4.77	0.543	0.0352	−0.0340	0.1782	0.1004	108.6
5	Short 2 calls	$105.00	2027-01-15	24%	$2.56	0.366	0.0348	−0.0302	0.1691	0.0690	−73.1
6	Short 1 put	$95.00	2026-11-20	27%	$0.61	−0.181	0.0440	−0.0423	0.0585	−0.0092	18.1
Net delta (shares; contracts × 100)											125.9

What each Greek measures

The Greeks are sensitivities. Each one estimates how much an option's value changes when one input moves and the others stay put. The Options Industry Council (OIC) defines them like this:

All of them are estimates from a model, not promises. The OIC's own delta example reads “all other pricing factors remaining constant”, and the Greeks themselves move as price, time and volatility move. Cboe's education team puts gamma the same way: it tracks “the changing rate of Delta” (Cboe Insights, 2021).

What's inside the spreadsheet

How to use it

1. One option: open Greeks calculator, replace the yellow cells (it opens with a textbook check case) and read the call and put columns.
2. A position: open Position. Type the underlying price (E6), today's date (E7), the risk-free rate (E8) and the dividend yield (E9). Use the same moment for all prices and implied vols.
3. From row 16, one leg per row: Call, Put or Stock; Long or Short; the number of contracts (shares for a Stock leg); strike; expiry date; implied vol as a percent. Leave the multiplier blank for 100 shares per contract (1 for Stock). Add the entry price per share if you want P/L.
4. Check that Status (J5) says OK. A leg with a problem shows the reason in column K, for example “Implied vol above 0”.
5. Read the net Greeks in J5:J14 and the scenario grid from row 39. To see the grid after time passes, type days forward in E10. Change the price step (E11) or IV step (E12) to widen or narrow the grid.

Option Greeks formulas in Excel and Google Sheets

The template uses the Black-Scholes-Merton model: the Black and Scholes formula (Journal of Political Economy, 1973) with Merton's extension to a continuous dividend yield (Bell Journal of Economics and Management Science, 1973). With S the underlying price, K the strike, T the years to expiry, σ the implied vol, r the risk-free rate and q the dividend yield:

d1 = [ln(S/K) + (r − q + σ²/2)·T] / (σ·√T)    d2 = d1 − σ·√T

Call = S·e^−qT·N(d1) − K·e^−rT·N(d2)    Put = K·e^−rT·N(−d2) − S·e^−qT·N(−d1)

N(x) is the standard normal cumulative distribution and n(x) its density. In a spreadsheet, the safest way to get both is NORMDIST(x,0,1,TRUE) and NORMDIST(x,0,1,FALSE). Why not the shorter names? Excel's NORMSDIST takes one argument (Microsoft), while Google Sheets documents NORMSDIST(x, cumulative) with two (Google). NORMDIST(x, mean, standard_deviation, cumulative) has the same four arguments in both (Google), and in LibreOffice. Excel also has NORM.S.DIST(z, cumulative) (Microsoft), which you can swap in if you only use Excel.

Exact formulas from the Greeks calculator tab (cell map: C5 = S, C6 = K, C8 = σ, C9 = r, C10 = q, C12 = T, C17 = d1, C18 = d2). In the file each one is wrapped in IF($C$13="OK", …, "") so it stays blank until the inputs are valid.

Check against a textbook example

A standard test case is Example 15.6 in John C. Hull's Options, Futures, and Other Derivatives (9th edition): a six-month option with S = $42, K = $40, r = 10% and σ = 20%, no dividend. The textbook values are a call of about $4.76 and a put of about $0.81 (also cited in the QFRM R package documentation). The Greeks calculator tab opens with these inputs (182.5 days = 0.5 years):

These match an independent Python implementation (math.erf and scipy) to the last digit. Python also checked each Greek formula against bump-and-reprice finite differences on 400 random options. The tab includes a put-call parity line: call − put must equal S·e^−qT − K·e^−rT, here $3.9508.

Position Greeks: adding up the legs

Greeks are quoted per share. To get a position's exposure, scale each leg and add:

position Greek = (+1 long or −1 short) × quantity × multiplier × per-share Greek

The multiplier is usually 100. The SEC's investor bulletin says: “An option contract generally represents 100 shares of the underlying stock” (Investor.gov). Some contracts differ, for example mini or adjusted options, so the sheet lets you type another multiplier per leg. Short legs flip the sign, as the OIC explains: “short calls have negative Delta (technically a positive Delta times a negative number of contracts)”. A Stock leg counts as delta 1 per share and zero for the other Greeks.

From the Example tab (row 16 is the stock leg):

Net delta in share terms answers a plain question: how many shares would move the same amount for a small price change? Dollar delta multiplies that by the price. Positive net gamma means delta grows as the price rises; negative net gamma means it shrinks.

Scenario grid: beyond the Greeks

Greeks are slopes at today's price. For a $10 or $20 move they drift away from the truth, because delta changes as the price moves and vega changes with time. The grid avoids that by re-pricing every leg with the full formula at nine underlying prices (−20% to +20% by default) and five implied-vol shifts (−10 to +10 points), then subtracting today's model value. Type days forward in E10 to see the same grid after time decay. Legs that expire before that date are valued at intrinsic value.

The Example's grid (P/L in $, today's price $100, IV shifts in points):

Worked example from the sample data

The Example tab holds a fictional position on a fictional $100 stock, valued on 2026-11-02 with a 4% rate and a 1% dividend yield: 100 shares, a short $110 call, a long $90 put, a $100/$105 call spread on 2 contracts and a short $95 put.

• Per-leg Greeks: the long $100 call (74 days, IV 25%) is worth $4.77 per share with delta 0.543. Two contracts make that 200 × 0.5428 = 108.6 shares of delta.
• Net delta: 125.9 shares. The 100 shares give +100; the options add 25.9. Dollar delta: 125.91 × $100 = $12,591.
• Gamma: −4.99. Net short gamma: if XYZ rises $1, the net delta falls by about 5.0 shares.
• Theta: $3.62 per day, about $25.36 over 7 days at today's rate. Short options collect the decay here.
• Vega: −$5.03 per vol point. A 10-point IV drop at today's price shows $15 in the grid; a 10-point rise shows −$67. The grid is not exactly ±10 × vega, because each leg's vega changes as IV moves.
• Bigger moves: a 5% rise (to $105) shows $565 with IV unchanged; a 5% fall shows −$688. The delta-gamma estimate (delta × move + ½ × gamma × move²) is close here: $567 and −$692. For a 20% fall it gives −$3,516 against the grid's −$2,836. That gap is why the grid re-prices instead of extrapolating.
• Range: across the grid, P/L runs from −$2,896 to $1,555. The short calls flatten the gains above $110. On the downside the long $90 put is offset by the short $95 put, so losses keep growing below $90.

Theta, vega and rho: why your platform's numbers may differ

• Days in a year. This sheet uses calendar days: T = days / 365, theta per day = yearly theta / 365. The OIC notes that “Pricing models take into account weekends, so options will tend to decay seven days over the course of five trading days” and that “there is no industry-wide method for decaying options”.
• Vega scale. The raw formula gives the change per 1.00 of volatility (100 points). The sheet divides by 100 to match the OIC's 1-point convention. A platform that shows vega 100 times bigger is not wrong, just using the other scale.
• Rho scale. The sheet shows rho per 1 percentage point, matching the OIC example: “if interest rates suddenly went to 4%, the premium would rise by $.45” for a rho of +.45.
• Inputs. Your implied vol, rate, dividend assumption and the exact expiry time (the sheet counts whole days) all move the result.
• Model. A platform may use an American-exercise or discrete-dividend model. Expect small differences, larger for deep in-the-money options.

Common mistakes

• Typing 25 instead of 25%. Implied vol, rate and dividend yield are percents. 25 means 2,500%.
• Forgetting the sign of short legs. A short call has negative delta in the position. The Side column does this for you; do not also type a negative quantity.
• Adding per-share Greeks across contracts. A delta of 0.50 on 3 contracts is 150 share-equivalents, not 1.5.
• Mixing time stamps. Price, implied vols and the valuation date should come from the same moment. A stale IV makes every Greek stale.
• Reading theta as a forecast. Theta assumes nothing else changes. Over a week, price and IV almost always change too, and decay speeds up close to expiry.
• Trusting Greeks for big moves. Use the scenario grid for moves beyond a few percent.

Limits

• European exercise. For American options the values are an approximation, mainly for deep in-the-money puts and for calls just before an ex-dividend date.
• Dividends are a continuous yield, not cash dividends on dates.
• One implied vol per leg. The scenario grid shifts all legs' IV by the same points (floored at 1%); real volatility skews do not move in parallel.
• Time is whole calendar days / 365. Same-day expiry is valued at intrinsic value.
• No live data, no implied-vol solver: you type the IV from your platform or the option chain.
• Recalculation was tested in LibreOffice. The file uses only functions that exist in Excel 2016+ and Google Sheets.

Options involve risk and are not suitable for all investors. Before buying or selling an option, US investors must receive the Options Clearing Corporation's Characteristics and Risks of Standardized Options.

FAQ

How do you calculate option Greeks in Excel?

Use the Black-Scholes-Merton formulas. With S, K, T (years), sigma, r and q in cells: d1 = (LN(S/K)+(r−q+sigma^2/2)*T)/(sigma*SQRT(T)), d2 = d1−sigma*SQRT(T). Call delta = EXP(−q*T)*NORMDIST(d1,0,1,TRUE). Gamma = EXP(−q*T)*NORMDIST(d1,0,1,FALSE)/(S*sigma*SQRT(T)). Vega per vol point = S*EXP(−q*T)*NORMDIST(d1,0,1,FALSE)*SQRT(T)/100. Theta and rho are on this page. The template has all of them ready, for a call and a put.

Does this options Greeks spreadsheet work in Google Sheets?

Yes. Import the .xlsx (File → Import → Upload) or open it from Google Drive. It uses NORMDIST(x,0,1,TRUE) and NORMDIST(x,0,1,FALSE), which take the same four arguments in Excel and Google Sheets. All functions: IF, AND, OR, NOT, ISNUMBER, N, LN, EXP, SQRT, NORMDIST, MAX, MIN, SUM, SUMPRODUCT, COUNT, ROUND. No macros, add-ins or live data feeds.

Why is my theta different from my broker's?

Theta depends on how a model counts time. This sheet divides the yearly theta by 365 calendar days. Some platforms use trading days or other conventions; the OIC notes that “there is no industry-wide method for decaying options”. Implied vol, rates, dividends and the exact time to expiry also differ between platforms.

What is vega per 1%?

Vega here is the change in the option's value if implied volatility rises by 1 percentage point, for example from 25% to 26%. That is the convention the OIC uses: “a 1% (100 basis points) change in the implied volatility assumption”. The raw formula gives vega per 1.00 (100 points), so the sheet divides by 100.

How do you calculate the Greeks of a whole options position?

Multiply each leg's per-share Greek by +1 (long) or −1 (short), by the number of contracts and by the multiplier (usually 100 shares per contract), then add the legs up. Shares count as delta 1 each and 0 for the other Greeks. The Position tab does this for up to 20 legs.

Are Black-Scholes Greeks accurate for American options?

They are an approximation. Black-Scholes-Merton prices European options, which can only be exercised at expiry. American-style options can be exercised any time before expiry, so the model can understate their value. The gap is usually small for options that are not deep in the money, and larger for deep in-the-money puts and for calls just before an ex-dividend date.

Related tools and templates

• Black-Scholes calculatorPrice one option in the browser
• Options profit spreadsheetP/L at expiry for a strategy
• Options trading trackerLog and review option trades
• Covered call trackerPremium, yield and assignment
• Wheel strategy trackerPuts, assignment and calls in one log
• Options profit calculatorPayoff of a single option
• Options breakeven calculatorWhere a trade breaks even
• All templatesFree spreadsheets

Educational template only, not investment advice. Example data is fictional: XYZ, its prices, the legs, implied vols and entry prices are made up to show the formulas and say nothing about any real security, strategy or future result. Model values are estimates and can differ from market prices. Sources: F. Black and M. Scholes, “The Pricing of Options and Corporate Liabilities”, Journal of Political Economy 81(3), 637–654, 1973 (doi); R. C. Merton, “Theory of Rational Option Pricing”, Bell Journal of Economics and Management Science 4(1), 141–183, 1973 (doi); The Options Industry Council, Delta, Gamma, Theta, Vega, Rho; SEC, Investor Bulletin: An Introduction to Options; OCC, Options Disclosure Document; Cboe, Learning the Greeks. Retrieved 8 November 2026.