Join Early Retirement Today
Thread Tools Search this Thread Display Modes
question on tracking options
Old 09-26-2021, 08:15 PM   #1
Recycles dryer sheets
Join Date: Apr 2007
Posts: 357
question on tracking options

I am thinking about selling some covered calls or cash-covered puts. Currently I have a spreadsheet with all of my assets. I would have a line such as 100 shares of XYZ at $30 per share total $3000.

I am trying to figure out how to integrate these upcoming option contracts into the spreadsheet.

For the covered call, I would still have the line for the underlying equity and I guess I can just add the premium from the sale to the line for my cash position in that account. The same would seem to apply to the put sale, except that the reserved cash would just stay in the cash position.

What I am trying to wrap my head around is whether I should try to list the open option contract as an asset.

As I understand things, as the price of the underlying moves one way or the other and the time value decays, the cost to close the position will change leaving me with a profit or loss if I were to buy back the contract on a given day.

When I do my weekly update of my spreadsheet, would it make sense to calculate the profit or loss on that day and have a line in my spreadsheet for the value of the contract at that point in time. Or should I just record the premiums and wait until I actually buy back the contract or it is exercised and then just make the adjustment to the line for the underlying assets?

Pardon my limited understanding of options. I am early in my learning phase.


joesxm3 is online now   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 09-26-2021, 08:25 PM   #2
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
pb4uski's Avatar
Join Date: Nov 2010
Location: Sarasota, FL & Vermont
Posts: 30,344
While I don't have any written calls, I have bought calls and I treat it the same as a stock... my basis is what I paid and I mark-to-market. I would think a written call would just be the inverse... a liability at fair value... and the fair value might ultimately be nil if the option expires worthless in which case you have a gain for the premium received.

I would treat the long position and the covered call separately, one as an asset and the other as a liability.
If something cannot endure laughter.... it cannot endure.
Patience is the art of concealing your impatience.
Slow and steady wins the race.

Retired Jan 2012 at age 56
pb4uski is offline   Reply With Quote
Old 09-26-2021, 08:47 PM   #3
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
MRG's Avatar
Join Date: Apr 2013
Posts: 10,204
Look at how your brokerage accounts for the assets. As far as the level of detail goes how many calls at what frequency is the question to answer. I have currently 29 option positions open with 20 of them expiry this Friday. On average I'm rolling 25 contracts weekly I wouldn't persist any of that data especially manually.

I guess I'm struggling with my reporting needs as well. A couple of pivot tables has helped me see some of my behavior I need to correct. I still have a nagging feeling I'm missing something.
MRG is online now   Reply With Quote
Old 09-26-2021, 09:55 PM   #4
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
NW-Bound's Avatar
Join Date: Jul 2008
Posts: 30,117
The option that you sell is a liability, and should be shown on its own line, and has a negative value.

Let's start out by saying I have 100 shares of XYZ worth $50/share. My brokerage screen will show:
  • XYZ 100 shares $5000
  • Total $5000

I now sell 1 call contract on XYZ for a premium of $1/share. The sold contract is a liability, and my spreadsheet should show:
  • XYZ 100 shares $5000
  • XYZ -100 options -$100
  • Cash $100
  • Total $5000

Note that immediately after selling the option, the account total does not change. The cash you receive cancels out the liability carried by the options.

The next day, XYZ goes up $1/share, and the option also increases by $0.3/share to a value of $1.3/share. My spreadsheet should show:
  • XYZ 100 shares $5100
  • XYZ -100 options -$130
  • Cash $100
  • Total $5070

My total has gone up by $70, less than the increase in the stock value, because the option liability limits my gain.

On the other hand, if XYZ drops by $1, and the option drops by $0.3 down to $0.70/share, then my spreadsheet should be as follows.
  • XYZ 100 shares $4900
  • XYZ -100 options -$70
  • Cash $100
  • Total $4930

Note how selling a call option reduces my unrealized gain on the upside, but also reduces my unrealized loss on the downside.

What happens at expiration is another matter, but the above is how I do accounting of my investable assets to keep a daily balance.

I actually do not keep a spreadsheet, but use Quicken to download all transactions and price quotes. Quicken does not get the price of options however, and I have to enter that manually each day. The price of the options is not as simple as quotes for stocks. More on this later.
"Those Who Can Make You Believe Absurdities Can Make You Commit Atrocities" - Voltaire (1694-1778)
NW-Bound is offline   Reply With Quote
Old 09-27-2021, 12:00 PM   #5
Recycles dryer sheets
Join Date: Apr 2007
Posts: 357
Thanks for the detailed explanations. It now makes sense to me. I had suspected that the brokerage account would list the positions somehow, but since I have yet to create a position I could not see what would happen.

I realize that options could be a dangerous business for the uninformed, so I plan to keep studying until I am confident that I understand what is going on.

I appreciate the help. I will probably make a few more threads to ask questions to make sure I am understanding various topics, but I will try to research them myself first.

The next topic for me to investigate is how intrinsic value and time value interact to determine the option premium.

Thanks again.
joesxm3 is online now   Reply With Quote
Old 09-28-2021, 08:16 AM   #6
sengsational's Avatar
Join Date: Oct 2010
Posts: 8,648
Back in the days when the CBOE was starting up, my dad gave me a journal article With a formula that he wanted me to help him get into a spreadsheet. I think it was Black-Scholes or something like that. He and my uncle did great the first few years, but they said it got much harder to make money as more awareness came around, and they quit trying. But I don't think they were using options to buffer the gyrations...they were making big bets that sometimes paid handsomely, and sometimes hurt bad.
sengsational is offline   Reply With Quote

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question about Mint and tracking CC cards Debinnov a FIRE and Money 16 10-22-2015 07:30 AM
There's No Tracking Like UPS Tracking easysurfer Other topics 60 05-04-2013 01:28 PM
Tracking your money nuisance FIRE and Money 18 08-05-2005 09:15 AM
Federal TSP tracking site Michael Moore FIRE and Money 11 07-14-2005 01:00 PM
Vanguard tracking error? wabmester FIRE and Money 3 12-23-2004 06:30 PM

» Quick Links

All times are GMT -6. The time now is 02:23 PM.
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2021, vBulletin Solutions, Inc.