It currently works for selling covered calls, selling cash secured puts, and selling naked puts. This is what it looks like: As with my dividend tracker spreadsheet , the orange cells are ones that you manually edit and the green cells are calculated automatically.
Stock Symbol The ticker symbol for the stock underlying the option contract. I use a Google Finance call to look up the stock price so you must use the ticker symbol as recognized by Google Finance. Exp Date Exp Date stands for expiration date and is the date that the contracted is scheduled to end. If you decide to close a contract early, then make sure to update the Close Date see below. This field is used to calculate the annualized rate of return for a margin account and is used in the calculation for determining margin cash reserve.
DTE Days to expiration. This shows the days left on the option contract. If the option has already expired, then 0 will be displayed rather than a negative number. Current Stock Price This field shows the current stock price of the underlying stock. Break Even Price This field shows the break even price for the option exclusive of any fees.
Premium Premium is the money collected for selling a put or call. It is also what you pay if buying a put or call. C C stands for contracts and indicates how many contracts you either sold or bought. Put Cash Reserve This field show the amount of money needed on hand in order to sell the option.
This will be x the strike price. For a non-margin account, that total amount needs to be in the account before your broker will allow the trade to go through. This is why this is called a cash-secured put. That cash is ear marked for that option trade in case it gets put to you.
If you sold a call, then this field is not used. Put Margin Reserve This field calculates the amount of money needed in the account for a naked put sold in an account with margin. If your broker has different requirements then this formula can be updated to reflect that.
It can be used to calculate the annualized rate of return column U. However, this field is not currently used. The annualized rate of return is based solely on the option rate of return as calculated on the strike price.
It does not take into account any gains or losses from selling the underlying stock. Any stock transactions can be performed on a separate spreadsheet. This can changed by editing the formula in column U, if desired.
Calculating the rate of return can be tricky, as discussed here: Fees Enter any fees associated with the trade. If I bought to close the option then I would add that additional commission to the original value. Exit Price This is the price to exit the option. If you bought to close, then type in whatever premium you paid. Close Date This is the date you either closed the option or it expired.
It is used to calculate the Days Held column and is important for accurately calculating the annualized rate of return. The cell will be dark green for profit and red for a loss. As described above, it does not include any profit or loss from selling the underlying stock in a covered call situation.
Margin Annualized ROR Calculates the annualized rate of return based on the smaller margin cash reserve. This field is only used at this point for puts. It is not yet set up for calculating the rate of return for naked calls. Status Open, Closed, or Exercised.
An example of exercised would be that you sold a covered call and it got called away from you. Account I included this column in case you have multiple accounts in which you do your option trading.
While it does feel good to book the premium collected as profit for the month you sold it, it makes more sense to realize it after the option is closed, either through expiration or through buying the option to close. This keeps things simple. In doing things this way, the entire trade is complete on a single row. And, as similar to my dividend tracker, it also shows a table displaying monthly option income each year.
Please let me know what you think! This is version 1. Any other features you would like to see? The spreadsheet is free and will always be available for free. However, if you find this spreadsheet useful, please consider donating to support my coffee fund and hosting costs. Thank you Scott for taking the time to pull this tracker together.
Thanks for the tracker tool. Should the formula not contain a check if the option is still open. If the option is closed there is no longer a need for the reserve so the sheet will indicate a higher reserve that required.
Hi John, thanks for the comment. There is a check of if the option is open or not on the Cash Reserve section of the Summary sheet.
Would you like it added there too? I am a fan of your dividend tracker and happy to see an options tracker as well. I am going to see if I can incorporate the dividend and options trackers into one. Just wondering if you had thought about doing that.
BTW, I have enough accounts at Scottrade that they give me free regular trades, I only have to pay for options. The dividends I collect go into a pool to buy any other stock s , up to 4, automatically fee-free.
I actually combined the dividend and option tracker into one spreadsheet for my personal use. If you want to get a copy of that, just send me your email on the Contact Us link. Sorry to hear about the loss in your IRA, but great job using options to get that amount back up. Let me know how you like it.
It does combine both options and dividends into one spreadsheet. I had some individual option return calculation spreadsheets I had been using but having everything on one spreadsheet is awesome, so thank you! I have a couple questions, 1. Do you have your options spreadsheet in Excel format by chance? Could you send me your most updated version of your options spreadsheet and any other ones you use to track your investments?
That would be amazing! Did you figure out how to include a formula or another couple columns to calculate your gains on a covered call position on a stock you already own? The latest versions of all my investing spreadsheets are available online here.
I could definitely include a formula to calculate gains on a covered call position. I left that out but will get back to you soon with this feature. Gonna check out the dividend tracker next. Thanks again, really appreciate the time you must have put into this and the generosity in sharing it. I just released a minor update that now correctly calculates the annualized returns. It was off slightly on the initial version.
Also, this new version now also tracks annualized returns for buying calls and puts as well as selling them. Thanks for finding these, Ryan! Can you let me know the steps for a covered call. Already love and use your Dividend spreadsheet…now found this today. Is this still work in progress? Hi Jeff, Glad you are enjoying the spreadsheets. Yep, spreads and iron condors are two things that I will be working on. I first wanted to make sure that simple calls and puts worked well first.
The problem comes that if I make a change on one spreadsheet, then I also have to make that change on every version of it as well. I get enough questions about simple things on the dividend spreadsheet that I feel that adding more functionality with options may make it too complex. Thank you for creating your deceptively simple yet elegant spreadsheet for tracking options trades; specifically covered calls. Have you any plans in the future for adding an additional level of security to your covered call spreadsheets by offering a married put or collar version?
I am definitely open to creating additional versions. I was going to work on iron condors next. Thanks for your comment.More...