Excel Sheets For Options Trading

Excel sheets for options trading

Excel sheets for options trading

If you follow Options Hunting you’ll know that I’ve been working on a spreadsheet to track options. Version 1.0 is now complete.

Options Trading Spreadsheet Download

It currently works for selling covered calls, selling cash secured puts, and selling naked puts. It will also work for selling naked calls, but I haven’t worked out the margin cash reserve calculations for that trade yet.

This is what it looks like:

Click here to access the Options Tracker Spreadsheet on Google Sheets

You’ll have to click File > Make a copy… in order to start using it yourself.

 

As with my dividend tracker spreadsheet, the orange cells are ones that you manually edit and the green cells are calculated automatically.

Here’s what each column means:

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.

Open Date
Open Date is the date that the option contract was opened.

Exp Date
Exp Date stands for expiration date and is the date that the contracted is scheduled to end.

Options Strategy Payoff Calculator Excel Sheet

If you decide to close a contract early, then make sure to update the Close Date (see below).

Call or Put
Type “Call” or “Put” here depending on what kind of contracted you opened.

B/S
This stands for buy or sell and refers to how the option was first started.

If you sell a put you are technically “selling to open.” Most people will likely just keep this as an S.

Stock Price DOC
Enter the underlying stock price at the time you opened the contract.

Owner design usb adapter and case for ethereum blockchain cryptocurrency

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.

A TJS for ALL Traders!

For example if I sold a put on a stock priced at $100 and received a premium of $1.50, my break even price is $98.50. If I sold a covered call on that same stock and received $1.00, my break even point would be $101.

Strike Price
The strike price of the option.

Premium
Premium is the money collected for selling a put or call.

It is also what you pay if buying a put or call. Since contracts are transacted in increments of 100 shares, you’d enter the 2.38 into column if you received $238 in premium.

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.

Trading Excel Sheets

This will be 100 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.

I used Schwab’s calculations for this which is (25% of the underlying stock’s market value + the option ask price – any out-of-the money amount) x 100 (per contract) x the number of contracts.

Where Trades Come to Life!

If your broker has different requirements then this formula can be updated to reflect that.

(Call) Cost Basis/Share
This field is for selling covered calls. 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.

Excel sheets for options trading

It does not take into account any gains or losses from selling the underlying stock. I did this to keep this spreadsheet dedicated to profit/loss only related to options. Any stock transactions can be performed on a separate spreadsheet. This can changed by editing the formula in column U, if desired.

Options Trading Excel Calculator

Let me know if you’d be interested in that and I can do the formula for you. Calculating the rate of return can be tricky, as discussed here: Calculating Covered Call Profits – Not As Easy As It Sounds

Fees
Enter any fees associated with the trade.

If I sold to open a contract and it expired, then I’d just put the fee of the initial trade.

Excel sheets for options trading

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 it expired, then you’d either leave it blank or type in 0. 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.

Option Chain Analysis - Download Free Excel Sheet

It is used to calculate the Days Held column and is important for accurately calculating the annualized rate of return.

Profit/Loss
This show the final profit or loss for each finalized option trade. The cell will be dark green for profit and red for a loss.

Annualized ROR for Options
This calculates the annualized rate of return for the option trade.

Congresman we need to shut down cryptocurrencies

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.

Trading Journal Spreadsheet Download

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.


The Puts/Calls sheet is where you enter all the transactions data.

For my spreadsheet I’ve decided that it is best to “realize” the profit or loss when the option trade is actually completed. 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. If I were to need to roll an option I’d first finalize the old option by buying to close and then opening a new one on a later row.

In doing things this way, the entire trade is complete on a single row.

It makes it easy to go to the profit/loss column and see how each of your options performed.


Summary

The Summary sheet automatically shows the total profit/loss that is both realized and unrealized, the cash reserves for the open trades, and total fees.

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.0, but I did enter in all my options transactions in 2015 and 2016 and looks pretty good. 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.

Thanks!

 

 

151xbHsMKpNBtHPb1tsacGWzoaCKYG9QH6