question on tracking options

[joesxm3]

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.

Thanks.

Joe

 

[pb4uski]

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.

 

[MRG]

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.

 

[NW-Bound]

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.

 

[joesxm3]

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.

 

[sengsational]

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.