OPTIONS JOURNAL WORKFLOW
How to Build an Options Trading Journal in Excel
Log every options position by strategy, not by leg: strikes, expiration, net premium, contracts and the 100-share multiplier, then measure results as return on the risk you actually took.
START WITH THE CONTRACT
Why an options journal needs its own fields
A stock journal records a ticker, a share count and two prices. An options trade adds a strike, an expiration date, a right (call or put), a side (bought or sold), a premium quoted per share but paid per contract, and very often more than one leg. If those details are not stored as separate columns, a “winning strategy” in the report can be a mix of five different trades that only share a ticker.
One position, several legs
A vertical spread is two contracts; an iron condor is four. Journal the strategy as one row with a net premium, and keep the legs in a separate tab so nothing is counted twice.
Premium × 100
A standard US equity option covers 100 shares, so a premium of 1.35 costs 135 USD per contract. Store the multiplier in its own column—adjusted or mini contracts can differ.
Expiration is part of the trade
Days to expiration at entry, at exit and the reason the trade ended (closed, expired, exercised, assigned) explain results that price alone cannot.
Defined or undefined
A long option or a debit spread has a known maximum loss; a naked short does not. Record which one applies, because “return” only makes sense against the risk that was actually taken.
If you need the universal foundation first, start with our step-by-step Excel trading journal guide. This article adds the options-specific layer on top of that structure, the same way our forex journal guide does for currency pairs.
KEEP RAW DATA AUDITABLE
Use four focused workbook tabs
Positions
One row per strategy from open to close. Net premium, contracts, multiplier, planned max loss and the outcome live here. When you scale out in stages, record each exit with its contracts and price, or the contract-weighted average exit.
Legs
One row per contract leg: position ID, right (call/put), side (buy/sell), strike, expiration, premium, contracts. A SUMIFS on the position ID gives the net premium so it is never typed twice.
Lists & Rules
Dropdown values for account, strategy name, underlying, market condition, exit reason and rule adherence. Consistent labels are what make the report trustworthy.
Strategy Report & Review
PivotTables or SUMIFS by strategy, underlying, DTE bucket and exit reason, plus a dated review log with one question to test in the next batch of trades.
THE POSITIONS TAB
Essential columns for an options trading journal
Format the log as an Excel table so new rows inherit formulas and data validation. Use dropdowns for every repeated label; “PCS”, “put credit spread” and “Bull put” would otherwise become three strategies in the report.
| Group | Suggested columns | Review question |
|---|---|---|
| Identity | Position ID, account, underlying, strategy name, defined-risk flag | What kind of trade is this, and is the loss capped? |
| Structure | Legs count, long/short strikes, spread width, expiration, multiplier | Are strikes, width and multiplier correct for this contract? |
| Timing | Entry date, exit date, DTE at entry, DTE at exit, holding days, earnings-week flag | How much time did the position have, and when did it end? |
| Plan | Net premium planned, max loss, max profit, profit target, stop rule, planned risk | What decision existed before the outcome was known? |
| Execution | Net entry premium, net exit premium, contracts, partial exits, slippage vs. mid | Did the fill differ from the plan? |
| Costs | Commission per contract, regulatory and exchange fees, assignment or exercise fees | How much of the gross result went to costs? |
| Outcome | Gross P&L, net P&L, return on risk, exit reason (closed / expired / exercised / assigned) | What happened, in units that can be compared? |
| Process | Market condition, implied volatility note, rule followed, screenshot link, notes | Was the process repeatable regardless of the result? |
CALCULATE, THEN VERIFY
Excel formulas for the positions tab
The formulas below use structured references inside two Excel tables named Positions and Legs. Column names are examples; match them to your own headers. Premiums are per share, positive when received and negative when paid.
| Column | Formula | Notes |
|---|---|---|
| Multiplier | =XLOOKUP([@Underlying],Products[Symbol],Products[Multiplier],100) | Falls back to 100 only when the symbol is missing from the product table. |
| Net entry premium | =SUMIFS(Legs[SignedPremium],Legs[PositionID],[@PositionID],Legs[Event],"Open") | Legs store premium × (+1 sold / −1 bought), so the sum is the net credit (+) or debit (−). |
| Net exit premium | =SUMIFS(Legs[SignedPremium],Legs[PositionID],[@PositionID],Legs[Event],"Close") | An expired-worthless leg is a Close row with premium 0. An assigned leg closes at its intrinsic value. |
| Gross P&L | =([@NetEntry]+[@NetExit])*[@Multiplier]*[@Contracts] | Signs do the work: credit 1.20 opened, closed for −0.40 → (1.20 − 0.40) × 100 × N. |
| Fees | =[@Legs]*[@Contracts]*2*[@FeePerContract]+[@OtherFees] | Two sides per leg (open and close). Set the close side to zero for legs that expired. |
| Net P&L | =[@GrossPL]-[@Fees] | Compare against the statement’s realized P&L before trusting the column. |
| Max loss | =IF([@Defined]="Yes",IF([@NetEntry]<0,-[@NetEntry],[@Width]-[@NetEntry])*[@Multiplier]*[@Contracts],[@PlannedRisk]) | Debit trades risk the debit; credit spreads risk width minus credit; undefined trades use the planned risk. |
| Return on risk | =IFERROR([@NetPL]/[@MaxLoss],"") | Blank when max loss is zero or missing, instead of a misleading error. |
| DTE at entry | =[@Expiration]-[@EntryDate] | Format as a number. Bucket it in the report: 0–7, 8–21, 22–45, 46+ days. |
WORKED EXAMPLE
One put credit spread, closed early
A trader sells the 95 put and buys the 90 put on the same expiration (a 5-point width) for a net credit of 1.20, 3 contracts, 32 days to expiration. Eighteen days later the spread is bought back for 0.40. The broker charges 0.65 USD per contract per side.
Illustrative data- Credit received
- 360.00 USD 1.20 × 100 × 3
- Debit to close
- 120.00 USD 0.40 × 100 × 3
- Gross P&L
- 240.00 USD 360 − 120
- Fees
- 7.80 USD 2 legs × 3 contracts × 2 sides × 0.65
- Max loss
- 1,140.00 USD (5 − 1.20) × 100 × 3
- Return on risk
- 20.4% 232.20 ÷ 1,140
Before copying formulas down, compare several rows against the broker’s closed-positions statement. Check whether the statement’s profit column already includes commissions and fees; if it does, the workbook must not subtract them a second time.
SIZE AND JUDGE BY RISK, NOT BY PREMIUM
Calculate net P&L and return on risk
The most useful planning column in an options journal is the maximum loss, because it is the number that position size and return are measured against. The relationships are simple once the net premium is known:
Return on risk = (Gross P&L − Fees) ÷ Max loss
Example: a 5-point credit spread sold for 1.20 and closed for 0.40 on 3 contracts earns 240 USD gross, pays 7.80 USD in fees and risked 1,140 USD, so the return on risk is 232.20 ÷ 1,140 = 20.4%. The same 232.20 USD on a 30-point width would be a 2.7% return—same money, very different trade.
TRY THE WORKFLOW
Options P&L and return-on-risk calculator
Enter hypothetical values. Results update in your browser and are not stored or sent anywhere.
Example only. Verify multiplier, fees and margin requirements with your broker.
MEASURE DECISIONS, NOT JUST TICKERS
Review by strategy, DTE and exit reason
The same underlying can be traded with a long call in one week and an iron condor in the next; lumping them together tells you nothing. An options journal earns its keep when the report can slice the same positions by strategy, by time remaining and by how the trade ended.
Return on risk by strategy
Average and median return on max loss per strategy name, with the count of trades beside it. A 60% win rate on credit spreads with a −180% average loser is not an edge. How to read win rate, profit factor and expectancy together.
Result by DTE bucket
Group by days to expiration at entry (0–7, 8–21, 22–45, 46+). Many traders discover that one bucket carries all the profit and another all the rule breaks.
Exit reason mix
Closed at target, closed at stop, expired, exercised, assigned. Assignments and stops that cluster around earnings weeks deserve their own filter.
Cost share of gross
Fees divided by gross profit before costs. Define what happens when gross is zero or negative so the ratio does not mislead.
- Reconcile with the statementMatch net premiums, contracts, fees and exit reasons for every closed position. Legs that expired must appear as Close rows with a zero premium.
- Check multiplier and widthConfirm adjusted contracts, index products and mini options carry the right multiplier; confirm every spread’s width matches its strikes.
- Separate earnings and event tradesFilter positions held through earnings, dividends or index rebalances and review their slippage, assignment and outcomes separately.
- Compare planned and actual riskWhere the realized loss exceeded max loss or planned risk, note whether the cause was early assignment, a gap, or a rule that was not followed.
- Segment comparable tradesFilter by strategy, DTE bucket, underlying and market condition before drawing a conclusion. A handful of trades is not a sample.
- Write one testable questionDefine one change, the trades it applies to and the metric you will watch, without editing historical rows.
READY-MADE WORKBOOK
Prefer an options journal that is already built?
The DIY structure above keeps every assumption visible. If you would rather start from a finished workbook, review the current product page for included files, features and software requirements.
Options Trading Journal Template for Google Sheets and Excel
The current listing describes trade entry with strategy, expiration, strikes and premiums, open/closed position tracking with days to expiration, net entry price, capital used and risk/reward per trade, partial exits in up to three stages, cumulative P&L and monthly, annual and custom-date reports. Confirm the latest details on the product page.
- Strategy, strikes, expiration and premium per trade
- Open positions with days to expiration
- Editable spreadsheet format, English and Spanish versions
Product requirements, included files and compatibility are listed on the product page and may change. Desktop or laptop use is recommended for spreadsheet editing.
Compare all Trading Journal templatesFAQ
Options trading journal questions
What should an options trading journal include?
At minimum: position ID, account, underlying, strategy name, every leg’s right, side, strike and expiration, net premium at entry and exit, contracts, multiplier, fees, days to expiration at entry, exit reason, max loss or planned risk, net P&L, return on risk, and whether the written rules were followed.
How do I calculate options P&L in Excel?
Store premiums per share with a sign (received positive, paid negative), then multiply the net change by the multiplier and the number of contracts: =(NetEntry+NetExit)×Multiplier×Contracts, and subtract fees. A credit of 1.20 closed for 0.40 on 3 standard contracts is (1.20 − 0.40) × 100 × 3 = 240 USD before fees.
Should I journal each leg or the whole strategy?
Both, in two tabs. The Positions tab holds one row per strategy with the net premium and the result; the Legs tab holds one row per contract leg. Formulas join them through a position ID, so the report is by strategy and the legs stay auditable.
How do I measure return on an options trade?
Divide net P&L by the maximum loss for defined-risk trades (debit paid, or width minus credit for a credit spread, times the multiplier and contracts). For undefined-risk trades use the planned risk you wrote down at entry. Dividing by premium received overstates returns on short options.
How do I record an expired or assigned option?
An expired-worthless leg closes at a premium of 0. An exercised or assigned leg closes at its intrinsic value and creates a stock position; record the shares in a separate stock row so the journal matches the broker statement.
Can the journal tell me which strategy will work next month?
No. It summarizes trades that already happened and helps you form questions to test. Past results by strategy or DTE bucket do not guarantee future performance.
Sources and further reading
- Investor.gov: Options (glossary)
- FINRA: Options — investment product overview
- Microsoft Support: Create and format an Excel table
- Microsoft Support: Apply data validation to cells
- Microsoft Support: Create a PivotTable
Sources and product details checked September 2026. Contract multipliers, fees and assignment procedures vary by broker and product. This article provides general educational information and is not investment, tax or legal advice.
