Home › Templates › Options trading tracker
Options Trading Tracker Spreadsheet
A free options trading tracker spreadsheet for single-leg trades: log long calls, long puts, covered calls and cash-secured puts, and it calculates net premium, break-even at expiry, days held, days to expiry, realized P/L, return on capital at risk and a simple annualized return, with a summary by strategy and a monthly P/L chart.
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-on, no email needed. The formulas are plain IF/SUMIFS/COUNTIFS, and there is no live price feed to break.
| Open | Ticker | Strategy | Strike | Expiry | Contracts | Premium/sh | Status | Net premium | Break-even | Realized P/L | Return on capital |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 6 Jan | ORVX | Cash-secured put | $50.00 | 20 Feb | 2 | $1.40 | Expired | $280.00 | $48.60 | $277.40 | 2.85% |
| 12 Jan | KLMT | Long call | $120.00 | 20 Mar | 1 | $4.20 | Closed | -$420.00 | $124.20 | $268.70 | 63.98% |
| 3 Feb | BNDQ | Covered call | $35.00 | 20 Mar | 3 | $0.85 | Assigned | $255.00 | $32.35 | $791.10 | 8.15% |
| 23 Feb | ORVX | Cash-secured put | $48.00 | 17 Apr | 2 | $1.10 | Closed | $220.00 | $46.90 | $157.40 | 1.68% |
| 10 Mar | ZEPH | Long put | $80.00 | 15 May | 2 | $3.50 | Closed | -$700.00 | $76.50 | -$462.60 | -66.09% |
| 14 Apr | ORVX | Cash-secured put | $47.00 | 15 May | 2 | $1.25 | Assigned | $250.00 | $45.75 | $247.40 | 2.70% |
| 19 May | ORVX | Covered call | $47.00 | 18 Jun | 2 | $0.95 | Expired | $190.00 | $46.05 | $187.40 | 2.03% |
| 22 Jun | KLMT | Long call | $130.00 | 21 Aug | 1 | $3.80 | Expired | -$380.00 | $133.80 | -$381.30 | -100.34% |
| 7 Jul | ORVX | Covered call | $49.00 | 21 Aug | 2 | $1.05 | Assigned | $210.00 | $45.95 | $607.40 | 6.61% |
| 10 Aug | TRLA | Cash-secured put | $25.00 | 18 Sep | 4 | $0.62 | Closed | $248.00 | $24.38 | $182.80 | 1.87% |
| 15 Sep | ZEPH | Long put | $75.00 | 18 Dec | 1 | $4.10 | Open | -$410.00 | $70.90 | (open) | |
| 1 Oct | BNDQ | Cash-secured put | $34.00 | 20 Nov | 3 | $0.90 | Open | $270.00 | $33.10 | (open) |
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, definitions of every calculated column, colour key, limits, sources, disclaimer |
| Trades | As of date and contract multiplier (top). Per trade: open date, ticker, strategy (dropdown: Long call, Long put, Covered call, Cash-secured put), strike, expiration, contracts, premium per share, stock price at open (covered call: your share cost), total fees, status (dropdown: Open, Closed, Expired, Assigned), close date, close premium per share. 200 rows. | Call/put and buy/sell to open (from the strategy), net premium, break-even at expiry, days held, days to expiry, closing cash flow, option P/L after fees, share P/L on covered-call assignment, realized P/L, capital at risk, return on capital at risk, simple annualized return |
| Summary | A year (one cell) | Per strategy: trades, open, realized, realized with profit, realized P/L, fees, capital at risk, return on capital, average days held. Open positions: count, net premium, capital at risk, trades expiring within 7 days. Realized P/L by month with a column chart. |
| Example Trades / Example Summary | Already filled | A worked example with 12 fictional trades: 2 open, 10 realized |
How to use it
- On Trades, type today's date in the As of date cell (B2), or type
=TODAY()there if you want it to update itself. It drives days held and days to expiry for open trades. - Add one row per position. Pick the strategy from the dropdown; the call/put and buy/sell to open columns fill in by themselves.
- Type the premium per share, as your broker quotes it. The sheet multiplies by 100 and by the number of contracts.
- For a covered call, type what you paid per share for the stock you own in Stock price at open. For the other strategies, type the stock price when you opened the trade (for your records).
- Put all commissions and fees for the round trip in one Fees cell.
- When a trade ends, change Status: Closed (add the close date and close premium per share), Expired (worthless; close premium not needed), or Assigned (shares delivered or bought at the strike).
- Open Summary for totals by strategy, open risk and the monthly chart. Change the year cell to see another year.
Exercised a long call or put? Enter its intrinsic value as the close premium, mark it Closed, and record the shares in a stock tracker such as the stock portfolio tracker.
The 100-share multiplier and what each strategy risks
The dollar math rests on the contract size. FINRA's Options page says: "A standard-size options contract is equal to 100 shares of the underlying security." Investor.gov's An Introduction to Options – Investor Bulletin (updated July 16, 2026) puts it as "An option contract generally represents 100 shares of the underlying stock." The word "generally" matters: contracts can be adjusted after corporate actions, so the multiplier sits in its own input cell.
- Buyers (long call, long put): "For the purchaser of an option, the premium paid is your maximum loss." (FINRA). Investor.gov: "Option holders risk the entire amount of the premium paid to purchase the option."
- Sellers (covered call, cash-secured put): "For the seller of an option, the premium you receive at the time of the sale is your maximum profit." (FINRA). The loss side is set by the stock, which is why the template measures capital at risk from the strike or your share cost.
- Uncovered calls are left out on purpose. FINRA: "In the case of an uncovered, or naked, call, where an investor sells a call option without owning the underlying stock, the maximum loss is theoretically unlimited." There is no finite capital-at-risk number to divide by.
Assignment can come before expiration. FINRA's Trading Options: Understanding Assignment (December 14, 2020) says "American-style options allow the buyer of a contract to exercise at any time during the life of the contract", and "the seller may be subject to 'assignment' on any day equity markets are open." For a short call, "the seller of the option must deliver stock at the strike price and in return receives cash"; for a short put, "the seller of the option is required to purchase the stock at the strike price." That is what the Assigned status records.
Before trading options, FINRA says to "read the Characteristics and Risks of Standardized Options disclosure provided by your firm." The current version is published by the OCC as the Options Disclosure Document (June 2024 version, effective June 3, 2024).
The formulas, explained
Exact formulas from the file, row 5 of Trades. B2 = As of date, E2 = multiplier (100). Each formula is wrapped in IF($C5="","",…) so empty rows stay blank; AA is a helper that reads "Open" or "Realized".
| Field | Formula |
|---|---|
| Buy / sell to open (E) | =IF(OR($C5="Long call",$C5="Long put"),"Buy to open","Sell to open") |
| Net premium at open (O) | =IF($E5="Sell to open",1,-1)*$I5*$E$2*$H5 (+ received, − paid) |
| Break-even at expiry (P) | =IF($C5="Long call",$F5+$I5,IF($C5="Covered call",$J5-$I5,$F5-$I5)) (long put and cash-secured put: strike − premium) |
| Days held (Q) | =IF($AA5="Open",$B$2,IF($M5<>"",$M5,$G5))-$A5 |
| Days to expiry (R) | =IF($AA5="Open",MAX($G5-$B$2,0),"") |
| Closing cash flow (S) | =IF($L5="Closed",-IF($E5="Sell to open",1,-1)*$N5*$E$2*$H5,0) (0 when expired or assigned) |
| Option P/L (T) | =$O5+$S5-$K5 |
| Share P/L on assignment (U) | =IF(AND($C5="Covered call",$L5="Assigned"),($F5-$J5)*$E$2*$H5,0) |
| Realized P/L (V) | =$T5+$U5 (blank while open) |
| Capital at risk (W) | =IF(OR($C5="Long call",$C5="Long put"),$I5*$E$2*$H5,(IF($C5="Covered call",$J5,$F5)-$I5)*$E$2*$H5) |
| Return on capital (X) | =IF($W5>0,$V5/$W5,"") |
| Annualized, simple (Y) | =$X5*365/MAX($Q5,1) |
| Realized P/L by strategy (Summary) | =SUMIFS(Trades!$V$5:$V$204,Trades!$C$5:$C$204,$A5) |
| Realized P/L by month (Summary) | =SUMIFS(Trades!$V$5:$V$204,Trades!$Z$5:$Z$204,">="&$A21,Trades!$Z$5:$Z$204,"<"&DATE($B$19,2,1)) (Z = close date, or expiration if blank) |
Capital at risk is the most the position can lose before fees: the premium paid for a long option, or the cash or share value left exposed after the premium for a cash-secured put or covered call (the stock falling to zero). Break-even ignores fees; they move it by a few cents per share.
Worked example from the sample data
Cash-secured put that expired: ORVX
Sold 2 × $50 puts for $1.40 on 6 Jan 2026. Net premium: $1.40 × 100 × 2 = $280.00. Break-even: $50 − $1.40 = $48.60. Capital at risk: ($50 − $1.40) × 100 × 2 = $9,720.00. It expired on 20 Feb 2026, so realized P/L = $280.00 − $2.60 fees = $277.40. Return on capital: 2.85% over 45 days, or 23.15% simple annualized.
Long call closed early: KLMT
Bought 1 × $120 call for $4.20 ($420.00 paid). Break-even at expiry: $124.20. Sold it on 17 Feb 2026 for $6.90 ($690.00 received). P/L after $1.30 fees: $268.70, which is 63.98% of the $420.00 at risk. The annualized figure, 648.65%, shows why annualizing a 36-day trade is only a comparison tool.
Covered call assigned: BNDQ
Sold 3 × $35 calls for $0.85 against 300 shares bought at $33.20. Premium kept: $255.00 − $3.90 fees = $251.10. Shares called away at $35: ($35 − $33.20) × 100 × 3 = $540.00. Realized P/L $791.10 on $9,705.00 at risk = 8.15%.
A losing trade: ZEPH long put
Paid $700.00 for 2 × $80 puts, sold them back for $1.20 ($240.00 received). Realized P/L: -$462.60, -66.09% of the premium at risk.
Summary by strategy
| Strategy | Trades | Open | Realized | Realized P/L | Capital at risk | Return on capital | Avg days |
|---|---|---|---|---|---|---|---|
| Long call | 2 | 0 | 2 | -$112.60 | $800.00 | -14.07% | 48.0 |
| Long put | 2 | 1 | 1 | -$462.60 | $700.00 | -66.09% | 29.0 |
| Covered call | 3 | 0 | 3 | $1,585.90 | $28,105.00 | 5.64% | 40.0 |
| Cash-secured put | 5 | 1 | 4 | $865.00 | $38,002.00 | 2.28% | 32.0 |
| Total | 12 | 2 | 10 | $1,875.70 | $67,607.00 | 2.77% | 37.3 |
Across the 10 realized example trades, realized P/L is $1,875.70 on $67,607.00 of capital at risk, 2.77%. That total adds up capital trade by trade, so cash reused for the next put is counted again. It is not an account return. The two open trades hold $10,340.00 of capital at risk.
Common mistakes
- Typing the dollar total as the premium. Enter premium per share ($1.40), not per contract ($140). The sheet applies the 100 multiplier.
- Forgetting the As of date. If B2 is stale, days held and days to expiry for open trades are wrong. Type the date or use
=TODAY(). - Counting an assigned put as finished. The option leg ends, but you now own shares at the strike. Their P/L is not in this sheet until you sell them; track them separately.
- Leaving fees out. On small premiums, fees can be a large share of the profit.
- Reading the annualized column as a forecast. A good 5-day trade annualizes to a huge number. It does not mean the same result can be repeated.
- Judging a strategy by how often it wins. Short premium trades often win small and lose big. Compare realized P/L and return on capital, not just the count of winners. See why win rate alone doesn't matter.
FAQ
How do I track options trades in Excel?
Use one row per position with open date, ticker, strategy, strike, expiration, contracts, premium per share, fees and status. Multiply premium by 100 and by contracts for the dollar amount, and use SUMIFS to total realized P/L by strategy and by month. The free template on this page does this with plain formulas that also work in Google Sheets.
How many shares does one options contract cover?
FINRA says a standard-size options contract is equal to 100 shares of the underlying security. So a premium quoted at $1.40 costs or pays $140 per contract before fees. Adjusted contracts after a corporate action can differ, which is why the template keeps the multiplier in its own cell.
How do you calculate the break-even of an option?
At expiry, ignoring fees: long call = strike + premium; long put = strike - premium; cash-secured put = strike - premium; covered call = your share cost - premium. A $50 put sold for $1.40 breaks even at $48.60.
How do you calculate the return on a cash-secured put?
Divide the realized P/L by the capital at risk, which the template defines as (strike - premium) x 100 x contracts. In the example, a $50 put sold twice for $1.40 kept $277.40 after fees on $9,720.00 at risk, a 2.85% return over 45 days.
What happens when a covered call is assigned?
Your shares are sold at the strike price. FINRA explains that for a short equity call, the seller of the option must deliver stock at the strike price and in return receives cash. You keep the premium. The template adds the share gain or loss (strike minus your share cost) to the option P/L.
How do you annualize an options return?
A simple method is return x 365 / days held. It makes short trades look very large, so treat it as a way to compare trades of different lengths, not as a forecast. The template uses the simple, non-compounded version and at least 1 day.
Related tools and templates
- Options breakeven calculatorBreak-even for one option trade
- Trading journalReview each trade
- Trading planRules before you trade
- Stock portfolio trackerShares from assignment, cost basis
- Position size calculatorSize by risk per trade
- Risk/reward calculatorCompare target and stop
- Capital gains tax calculatorEstimate tax on a gain
- All templatesFree spreadsheets
Educational template only, not financial, tax or investment advice. Options involve risk and are not suitable for all investors; read the Characteristics and Risks of Standardized Options before trading. Example data is fictional: tickers ORVX, KLMT, BNDQ, ZEPH and TRLA, prices, premiums and dates are made up. The template does not model taxes, margin requirements or live prices. Quotes from FINRA and Investor.gov were retrieved on 25 October 2026.