Stock Option Position Profit Spreadsheet

In this video we are going to discuss the stock options combination position trading spreadsheet worksheet called StkOpt.

Our original options spreadsheet had a worksheet for entering stock and option trades, in order to get the position profit and and a graph of math to expiration.

But in this spreadsheet, I have added 2 additional stock option position worksheets:  (1) a worksheet for entering option trades and being able to plot theoretical value and profit for any given date before expiration – this is similar to the pricing worksheet, but will allow a plot for the whole position (2) the worksheet that we are currently discussing.

What makes this worksheet different is that besides being able to see the stock option position expiration profit curvature, we can get an additional plot of a stock only position to compare it too.

Stock Option Position Profit Spreadsheet Video

You are currently looking at a ratio put spread = +1 40p .80 X -2 38p .75 – the graph is plotting the profit using a 100 multiplier for the options.

video-options-spreadsheet-stkopt2

  • You can see that the minimum profit is the credit = .70
  • The maximum profit is at the short strike = 2.70 – credit + spread width
  • Breakeven would be 35.30 = short strike – spread width – credit
  • And below 35.30 the position would lose 1:1 with the underlying

Now let’s compare that to a stock position only = +100 shares 39.00 – consider that you completed the ratio short on a reject of 38.50 support, and then got a buy setup at 39.00.

video-options-spreadsheet-stkopt3

  • The ratio spread is more profitable at the stock entry and down to the short strike 38.00, where the stock would lose $100
  • And the ratio spread loses less if the stock would resume the downside move
  • But the ratio spread can only make the credit – giving it little upside participation compared to the stock

But in our stock option combination strategies, our position would also include the long stock – here is what that position would look like:

  • And now the profit parameters would change
  • The ratio spread + stock has greater upside participation than the stock
  • It loses less than the stock down to the ratio spread breakeven 35.30
  • And then it would lose more than the stock – from the net combination of the extra short put and the long stock
  • However, our trading method would typically give a short trade setup on a resumption of a downside move – where the stock would be short and the extra short put would be covered.

StkOpt Worksheet Inputs

There are 2 input areas on this worksheet to fill in:

  • The top area where you enter your stock and options – this is how you get your math to expiration plot.
  • Be sure in the opt/u column that you have entered an o for options and a u for the underlying.
  • The area to the right of the chart – this is where you set the multiplier for the entire worksheet, so it must be filled in.  And since we are working with equity options, this input is 100.
  • This is also where you setup the starting price for the graph and the incremental changes – you can see that we are starting at 30, with 1 point increases.

And this is also where we can get the stock only plot:

  • Enter the number of shares to the right of the u and the average price to the right of price
  • If you bought 100 shares at 37 and 100 shares at 39 – you would enter 200 and 38
  • If you leave the quantity blank – the yellow line will become the horizontal 0 line