Home › Templates › Options profit spreadsheet

Options Profit Calculator Spreadsheet

A free options payoff spreadsheet for Excel and Google Sheets. Build a position of up to four legs (calls, puts or stock), and it shows the net debit or credit, the profit or loss at expiration across a range of prices, the maximum profit and loss (marked Unlimited where they are), every break-even price and a payoff chart, plus an optional Black-Scholes value today.

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 add-in, no live data: you type the strikes and premiums from your broker's option chain.

$115.00net credit received

$109.80max profit at expiration, after $5.20 fees

−$390.20max loss at expiration, after fees

$88.902, $111.098break-even prices

Leg	Action	Type	Strike	Premium / share	Qty	Signed units	Model value today
1	Buy	Put	$85.00	$0.25	1	100	0.2305
2	Sell	Put	$90.00	$0.75	1	−100	0.7581
3	Sell	Call	$110.00	$1.20	1	−100	1.1936
4	Buy	Call	$115.00	$0.55	1	100	0.5347

P/L at expirationP/L today, Black-Scholes model (30% vol, 4% rate, 45 days)

Want a quick answer in the browser instead? The options profit calculator covers six common one- and two-leg strategies with no download. This spreadsheet is for custom positions of up to four legs, strategies such as iron condors and straddles, a model value before expiration, and a file you can save and edit. To log trades you have already placed, use the options trading journal or the options trading tracker.

What's inside the spreadsheet

How to use it

1. Open Builder and type the underlying price now in B5. Leave Price grid on Auto (about half to one and a half times the price, widened to cover every strike), or pick Custom and set the lowest price and the step.
2. Add one row per leg: Buy or Sell, Call, Put or Stock, the strike, the premium per share and the number of contracts. For a stock leg, put the price per share in the premium column and the number of shares as the quantity.
3. Leave Multiplier blank for standard contracts (100 shares) and stock (1). Column I must say OK or Not used.
4. Type your total opening and closing fees in B9 and B10. If you plan to let options expire, the closing fee may be zero; exercise and assignment fees vary by broker.
5. Read the results from B20 down and the chart. Break-evens are listed from low to high. The grid underneath shows each leg's P/L at each price.
6. Optional: enter a default implied volatility, the risk-free rate and the days to expiration in E5:E7 to get a model value per leg and a dashed "today" line. A leg with its own implied vol in column H uses that instead.

The five presets, with fictional numbers

All five use made-up stock XYZ at $100, 45 days to expiration, 30% implied volatility and a 4% rate. The premiums are the Black-Scholes values at those inputs rounded to $0.05, and the fees are a fictional $0.65 per contract each way. The sheet's figures include fees; the last column shows the textbook formulas from the Options Industry Council and Cboe, before fees, computed on the Presets tab.

What each one is

• Long call. OIC: "The profit potential is theoretically unlimited", and "Breakeven = strike + premium". Here 105 + 2.40 = $107.40. The most you can lose is the $240 paid (OIC, Long Call).
• Bull call spread. OIC: "buying one call option and selling another at a higher strike price to help pay the cost". Max gain = "High strike - low strike - net premium paid"; max loss = "Net premium paid"; "Breakeven = long call strike + net debit paid" (OIC, Bull Call Spread). Here 10 − 3.25 = $6.75 a share, or $675 before fees.
• Iron condor. Sell a put spread and a call spread with the same expiration. OIC calls it a short condor: max gain "Net premium received", max loss the strike difference "- net premium received", "Upside breakeven = lower call strike + premiums received" and "Downside breakeven = upper put strike - premiums received" (OIC, Short Condor). Cboe's strategy sheet describes the same shape: "If SPX stays between $5,450 and $5,600, maximum profit is the net premium received" (Cboe, Common Options Trading Strategies).
• Protective put. OIC: "A long put option added to long stock insures the stock's value." Max loss = "Stock purchase price - strike price + premium paid"; "Breakeven = starting stock price + premium" (OIC, Protective Put). Here (100 − 95 + 1.90) × 100 = $690.
• Long straddle. OIC: "buying a call and buying a put, both with the same strike price and expiration"; "The maximum loss is limited to the two premiums paid"; break-evens at the strike plus and minus the premiums (OIC, Long Straddle). Here 100 ± 8.40.

The formulas, explained

Payoff at expiration

At expiration an option is worth only what it would pay if exercised: a call MAX(price − strike, 0) per share, a put MAX(strike − price, 0). Each leg's P/L is its signed units × (value at expiration − premium), where signed units = +1 for Buy or −1 for Sell × quantity × multiplier. The total subtracts opening and closing fees. Exact formulas from the Builder tab:

Why the break-evens are exact. The payoff only bends at strikes. The grid holds 201 evenly spaced prices plus every strike, sorted with SMALL, so between two neighbouring grid prices the P/L is a straight line. Where it changes sign, linear interpolation (price₁ − P/L₁ × (price₂ − price₁) / (P/L₂ − P/L₁)) gives the exact crossing. Two extra cells catch a break-even below the grid (between $0 and the lowest price) or above it (using the slope beyond the highest strike).

Unlimited, or not. Above the highest strike the P/L changes by the number of long calls and shares minus short calls and shares (each × multiplier) for every $1. If that slope is positive, max profit is "Unlimited"; if negative, max loss is "Unlimited" (a short call with nothing covering it, short stock, or a 1×2 ratio spread). Otherwise the extremes are the grid's maximum and minimum or the P/L at a price of $0, whichever is further out.

Black-Scholes value today (optional)

For each option leg: d1 = (LN(S/K) + (r + σ²/2)·T) / (σ·√T), d2 = d1 − σ·√T, call = S·N(d1) − K·e^−rT·N(d2), put = K·e^−rT·N(−d2) − S·N(−d1), with N = NORM.S.DIST(x, TRUE) and T = days ÷ 365. The full cell for leg 1's value today:

=IF(OR(I14<>"OK",$E$9<>"OK"),"",IF(C14="Stock",$B$5,IF($B$5<=0,IF(C14="Call",0,K14*EXP(-$E$6*$E$8)),IF($E$8=0,IF(C14="Call",MAX($B$5-K14,0),MAX(K14-$B$5,0)),IF(C14="Call",$B$5*NORM.S.DIST(((LN($B$5/K14)+($E$6+P14^2/2)*$E$8)/(P14*SQRT($E$8))),TRUE())-K14*EXP(-$E$6*$E$8)*NORM.S.DIST(((LN($B$5/K14)+($E$6+P14^2/2)*$E$8)/(P14*SQRT($E$8)))-P14*SQRT($E$8),TRUE()),K14*EXP(-$E$6*$E$8)*NORM.S.DIST(P14*SQRT($E$8)-((LN($B$5/K14)+($E$6+P14^2/2)*$E$8)/(P14*SQRT($E$8))),TRUE())-$B$5*NORM.S.DIST(-((LN($B$5/K14)+($E$6+P14^2/2)*$E$8)/(P14*SQRT($E$8))),TRUE()))))))

Checked against Hull. John C. Hull's Options, Futures, and Other Derivatives works a Black-Scholes-Merton example with S₀ = 42, K = 40, r = 10%, σ = 20% and six months to expiry: d1 = 0.7693, d2 = 0.6278, call 4.76, put 0.81. With 182.5 days the Builder gives d1 0.7693, d2 0.6278, call 4.7594 and put 0.8086. A separate Python model (math.erf) and scipy.stats.norm give the same values to 9 decimal places, and the call minus the put equals 42 − 40·e^−0.05, as put-call parity requires.

A model, not a price. Black-Scholes assumes European exercise, no dividends, and one constant volatility. Most US stock options are American-style and can be exercised early, and real premiums move with supply, demand and each strike's own implied volatility. Use the dashed line to see the shape of time decay, and your broker's bid and ask for actual prices.

Worked example: the iron condor

On the Example tab the iron condor buys the 85 put at $0.25, sells the 90 put at $0.75, sells the 110 call at $1.20 and buys the 115 call at $0.55, one contract each. The net credit is 0.75 + 1.20 − 0.25 − 0.55 = $1.15 a share, $115.00 in total. Fees are $5.20 ($2.60 to open, $2.60 to close).

• Between $90 and $110 every option expires worthless. P/L = $115.00 − $5.20 = $109.80, the maximum profit.
• Above $115 or below $85 one spread is fully in the money: −$500 + $115.00 − $5.20 = −$390.20, the maximum loss. The slope above $115 is zero (one long call, one short), so it is not unlimited.
• Break-evens: 90 − 1.15 + 0.052 = $88.902 and 110 + 1.15 − 0.052 = $111.098. The fees ($5.20 ÷ 100 shares = $0.052 a share) pull both break-evens about 5 cents inward from the textbook $88.85 and $111.15.
• Today, at $100, the model values the four options so that closing now would give −$8.85 after fees. $5.20 of that is fees; the other −$3.65 comes from rounding the premiums to $0.05.

Switch B3 to another preset and the whole tab, including the chart, recalculates. The long call shows max profit Unlimited and a break-even of $107.413 after fees; the protective put shows a max loss of −$691.30; the straddle shows two break-evens, $91.574, $108.426.

Common mistakes

• Forgetting the multiplier. A premium of $2.40 is per share. One standard contract costs $240.
• Typing the premium as a total. Enter the quoted per-share premium; the sheet multiplies by quantity and multiplier.
• Mixing expirations. A payoff at expiration only makes sense when every leg expires on the same day. Calendars and diagonals need a model of the long leg's remaining value.
• Ignoring fees on small credits. On the example condor, fees take $5.20 of a $115 credit and move both break-evens.
• Reading "max loss" as the cash needed. Margin rules for short options can require more cash than the payoff's maximum loss. Ask your broker.
• Treating the model line as a forecast. It shows a theoretical value with today's inputs held fixed, not where prices will go.

Limits

• Up to 4 legs with one expiration date. One underlying, one currency.
• At expiration, options are valued at intrinsic value. No early exercise, assignment, dividends, margin or taxes.
• The Black-Scholes value is European-style with no dividends, one rate and T = calendar days ÷ 365.
• The grid has 201 evenly spaced prices plus the strikes. Max profit, max loss and break-evens are exact for the payoff at expiration; the chart only shows the grid range.
• Recalculation was tested in LibreOffice. The file uses only functions that exist in Excel 2016+ and Google Sheets.

Options carry real risks. OCC: "Prior to buying or selling an option, investors must read a copy of the Characteristics and Risks of Standardized Options", the options disclosure document (OCC, Options Disclosure Document). The SEC's investor bulletin adds that "Option holders risk the entire amount of the premium paid to purchase the option" and that some contracts "can expose writers to unlimited potential losses" (Investor.gov, An Introduction to Options). The Cboe Options Institute has free courses on how calls and puts work.

FAQ

How do you calculate options profit in Excel?

Work out each leg's value at expiration, subtract the premium, and scale it. A call is worth MAX(price − strike, 0) per share and a put MAX(strike − price, 0). Multiply by +1 for a bought leg or −1 for a sold leg, by the number of contracts and by the multiplier (usually 100), then add the legs and subtract fees. This spreadsheet does that for up to four legs at about 200 prices and draws the result as a line chart.

How do you find the break-even of an options strategy?

A break-even is a price at expiration where the total P/L is zero. For one long call it is the strike plus the premium; the Investor.gov bulletin's example call needs the stock "to rise to $72.20" to break even. For multi-leg positions there can be two or more. The sheet finds every point where the P/L changes sign and interpolates between the two prices around it. Because every strike is a grid price, the payoff is a straight line in between, so the answer is exact.

What is the maximum loss on an iron condor?

The difference between the strikes of the calls or the puts, minus the net premium received, times the multiplier. That is the OIC and Cboe formula. In the example, 5.00 − 1.15 = 3.85 a share, or $385 for one contract before fees. The maximum gain is the credit, $115, kept if the price ends between the short strikes.

Why does one options contract cost 100 times the quoted price?

Because a standard equity option covers 100 shares. Investor.gov: "An option contract generally represents 100 shares of the underlying stock." A premium quoted at $2.40 costs $240 per contract. The multiplier column defaults to 100; change it for adjusted or mini contracts, and use 1 for a stock leg, where the quantity is the number of shares.

Can the spreadsheet show the value before expiration?

Yes, as an estimate. Enter an implied volatility, the risk-free rate and the days left, and each leg gets a Black-Scholes value today, with a dashed "today" line on the chart. The model assumes European-style options and no dividends, while US stock options are usually American-style, so treat it as a rough guide, not a quote. The formula reproduces Hull's textbook example: a call worth 4.76 and a put worth 0.81.

Does this options payoff spreadsheet work in Google Sheets?

Yes. In Google Sheets choose File → Import → Upload and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. The file uses only ABS, AND, COUNT, COUNTIF, EXP, IF, IFERROR, INDEX, ISNUMBER, LN, MATCH, MAX, MIN, NORM.S.DIST, NOT, OR, ROUND, SMALL, SQRT, SUM, SUMPRODUCT, which exist in Excel 2016+, Google Sheets and LibreOffice. No macros, no add-in, no market data connection.

Related tools and templates

• Options profit calculatorSix common strategies, in the browser
• Options trading journalLog trades, reasons and results
• Options trading trackerOpen and closed positions, P/L
• Covered call trackerPremiums, assignments, yield
• Wheel strategy trackerCash-secured puts and covered calls
• All templatesFree spreadsheets

Educational template only, not investment, tax or legal advice. Example data is fictional: stock XYZ, its price, the premiums, volatility and fees are made up to show the formulas and are not a recommendation. Strategy formulas are quoted from The Options Industry Council (optionseducation.org) and Cboe; risk statements from the OCC and the SEC's Investor.gov; all retrieved on 4 November 2026. The Black-Scholes check uses the worked example in John C. Hull's Options, Futures, and Other Derivatives.