Hello OptionBoxer community! Grab a copy of this free covered call spreadsheet by clicking the download button below.
It’s been awhile since my last post and to account for that I’ll be diving into a series of posts with free downloadable spreadsheets for you to use. I’ll do my best to describe each spreadsheet and explain any nuances. Of course, I couldn’t possibly account for every conceivable trading scenario off the top of my head. So, please reach out to me if you discover a situation in which the spreadsheet fails and I’ll update it for everyone.
For any new traders among us that may wish to employ this strategy I strongly recommend this youtube video from TastyTrade to discover all the benefits of a covered call position. Of course, feel free to download the tracker below before watching.
Download a copy of the spreadsheet here. Freely & Safely.
In the following few paragraphs I’ll discuss exactly how I use the free covered call spreadsheet to keep a close eye on my covered call positions.
Step 1 – Highlight the entire sheet. Once highlighted right click anywhere on the sheet and select copy.
Step 2 – Create a new sheet. Once created right click on cell A1 and paste the copied template.
Step 3 – Rename the the new sheet to your liking by right clicking the tab “sheet1” and select rename.
At this point you’ve created a copy of the original template included. This is important so that you’ll always have a fresh copy available if something goes wrong. That said, I’ve been careful not to make any absolute references so if you do wish to move things around it shouldn’t cause a problem with the calculations.
*The free covered call spreadsheet is designed to track one investment per sheet. Should you have more than one covered call investment just follow the steps above to create multiple new sheets.
Step 4 – Enter the # of shares purchased and their cost per share.
Step 5 – Enter the company ticker and current share price.
*Please note – current share price will need to be updated manually each time you view the spreadsheet so that profit/loss is updated accordingly. Excel has a built in feature to update the price automatically but I didn’t care for the formatting. Should you want to use it, select the the ticker box on the spreadsheet, navigate to “data” on the top ribbon and select “stocks” from the data types section.
Step 6 – Enter the short call contract details
Step 7 – Enter any additional share purchases and short contracts sold
*Please note – the spreadsheet will only calculate properly if the number of shares purchased is equal to the number of contracts sold. Should you ever chose to open fewer contracts than the number of shares purchased or possibly open more contracts than the number of shares purchased, you’ll need to wait until all positions are closed for the sheet to calculate profitability. Hope that makes sense. If this is unclear, I’ve discussed it in the video tutorial posted above.
Step 8 – Closing short contracts
*Please note – If you choose to close contracts separately you’ll need to create a line item for each contract or each contract lot. For example, if you owed 10,000 shares of XYZ company and you sold 100 calls against the position you would want to enter the lot size for each closing position. For more information please view the video tutorial above.
Step 9 – Closing the entire covered call position
You may choose to close a position voluntarily at some point. To account for that on the free covered call spreadsheet simply enter the closing debit for any open contracts. Followed by entering a closing line item for the number of shares closed. *Please note – To calculate appropriately you’ll need to enter the number of shares in contract form. ie. 1 contract = 100 shares, 50 contracts = 5000 shares, etc. Finally, select yes under the closed/assigned column to indicate the position is fully closed.
Additionally, you may choose to have the shares assigned. In this instance, you will not need to enter a separate line item to close the shares. You would simply leave the debit column blank to indicate you’ve kept the entire credit received and select yes to indicate the shares were sold at the strike price entered.
That should just about cover it, or at least I’m hoping it does. However, if you encounter an error or uncover a situation that doesn’t calculate appropriately please reach out at your convenience.
Want to track all of your options or investments in one place? Check out the OptionBoxer Analyze and Journal Spreadsheet. Not only will it track a family sized grocery list of options strategies you’ll be pleasantly surprised with the analysis features it includes.
I do hope you enjoy it and please leave any and all comments in the comment section below. Google loves engagement. Or so I’m lead to believe…hmmm…guess we will see.
excellent spread sheet i just started trading writing covered calls,
Open date and expiry date closing date be handy and the break even column etc.
terminolgy not sure credit debit is the premium u recieved which is what?
strike/closed$ amount? is that only the strike price. what about rolling down and rolling up where do we put the info?
So far I seen its a good work sheet.
Please reply back to me I’m in Australia and I joined fokas beyond as a training centre for this strategy.
I be appreciated if u can add these to the spread sheet plus a graph if it can be done.
I would be willing to contribute for your efforts.
Thank you Owen! I’m happy to discuss this further. Please feel free to reach me at Jeff@Optionboxer.com