Rolling options is one of the most useful tools in the wheel strategy — until it becomes the thing that breaks your tracking. Most traders don't realize that a single roll creates two transactions with different accounting implications, and recording it wrong can throw off every ACB and P&L calculation in your spreadsheet from that point forward.
Here's how to do it right, whether you're committed to keeping a spreadsheet or just want to understand the math before switching to a dedicated tracker.
Why Does a Roll Break Your Spreadsheet?
A roll is fundamentally two separate transactions that happen to be executed together:
- Buy to Close — you pay a debit to close the existing option
- Sell to Open — you collect a credit by opening the new option
Your broker usually executes these as a single spread order and gives you one confirmation with one net price. This is where the spreadsheet problem begins. Do you record one row or two?
Option A: Record as Two Rows (Correct, but Complex)
| Date | Action | Strike | Premium | Net |
|---|---|---|---|---|
| Mar 15 | Buy to Close CSP | $170 | −$3.20 | −$320 |
| Mar 15 | Sell to Open CSP | $165 | +$3.80 | +$380 |
Your running premium total comes out right — net credit of $60 — but now any formula that counts "open positions" or "total contracts sold" potentially sees both rows and double-counts. You'll need filters to exclude BTC rows from those counts.
Option B: Record as One Row (Simple, but Incomplete)
| Date | Action | From → To | Net Credit |
|---|---|---|---|
| Mar 15 | Roll CSP | $170 → $165 | +$60 |
Clean and readable, but the individual leg data is gone. If you later want to calculate the total premium collected at a specific strike, or analyze how often your rolls were net credit vs net debit, you can't. You're also more likely to forget to record the BTC cost, which inflates your apparent premium income.
What Is the Best Way to Track Option Rolls?
Use a hybrid approach: record both legs as separate rows, but tag each pair with a matching Roll Group ID in a dedicated column. This gives you the best of both worlds — complete individual transaction data and the ability to group roll pairs for net credit calculations.
Your roll group column might look like: ROLL-001, ROLL-001 for both legs of the same roll. Add a second column called Leg Type with values like BTC or STO, and you can filter either leg independently.
The Formula for Net Roll Credit
=SUMPRODUCT((roll_group=A2)*(premium))This sums both legs of a roll group — the debit and the credit — giving you the true net credit or debit for that roll. Use this value in your ACB calculation instead of trying to sum individual row premiums and remembering to exclude BTC rows.
What About Tracking Multiple Rolls on the Same Position?
Things get significantly more complex when you roll a position two or three times. Each roll generates a new Roll Group ID, and your ACB calculation needs to aggregate across all of them correctly. At this point, most traders find that maintaining spreadsheet integrity through multiple rolls on a single campaign becomes more work than the campaign is worth. OptionWheelTracker handles rolls as a first-class trade type — one entry and your campaign ACB updates instantly, no formulas required.