Option Position Spreadsheet For Graphing Current Profits

In this video we are now going to discuss the newest worksheet in our options trading spreadsheet, which is called OptPos or option position.

This is a trading worksheet that can give the theoretical value and profit of an entire options position, at a given date before expiration, and compare it to the profit if the position was held to expiration.

Note:  There is a new options trading spreadsheet download available

This is the 3rd worksheet in the options trading spreadsheet for entering your trades or doing whatif position modeling.  And although these are similar, they do have some unique differences.

  • The StkOpt or stock option worksheet will show a stock option position profit at expiration, compared to a stock only position profit
  • The Position worksheet also will show the expiration profit of a stock option position – but there is no plot for a stock only position.
    • Although stocks can be entered on this worksheet, I predominantly use it for modeling different option trades and whatif changes – and use the StkOpt worksheet to look at combination positions
    • NOTE:  I am not going to do a separate video for the Position worksheet – it is the same as StkOpt, without the additional inputs for plotting the stock for comparison
    • The OptPos worksheet is only for entering options and looking at the profit before expiration, in terms of the change in theoretical value and the original trade price – do not enter any stock trades on this worksheet

The option position worksheet has become one that I really like and use a lot.  Our trading spreadsheet has always had a worksheet for modeling profit before expiration for 1-2 options and at 1-2 different dates – but we haven’t had a worksheet that will track the profit of a position.

Options Position Profit Graph

We are in the option position worksheet now.  You are looking at the math to expiration for a long 51p that cost 1.25, on 10/31 when the underlying was 51.35.  But when you look at the profit columns on the graph, you see nothing for profit in the date column.  The reason for this is because I haven’t filled in the theoretical value inputs yet – the trade was just done, so there is nothing to look at for profit, besides the possible difference in theoretical value and the price of the option.

Price went down to 49.56 in the same day – now look at the date column when I enter the inputs.  You can see that at 49.50 you would have a profit of 106.00 based on the change in theoretical value –vs- a profit of 25.00 if you held the long option to expiration and the stock was 49.50

Now let’s see what happens when I enter the 2 puts that I sold to complete a ratio put short – I sold the 49 strike, with the underlying at 50.25, for .85.

I have changed the begin input to 44.25, so we can see 50.25 on the profit graph – you can see that the current profit goes down, which is because the stock has moved up, and you can see that the math to expiration has increased, which would be a function of the short puts expiring worthless at this expiration price.

Options Position Spreadsheet Profit Graph

Now it’s 11/4 and the underlying is 49.75.  So I change the inputs for the new date and price, and you see the profit would be 85.00, with the math to expiration at 170.00 – again the puts will expire worthless and the long put would make more at this lower stock price.

Option Trading Position Worksheet Inputs

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

  • The spreadsheet area where you enter your trades and the theoretical value inputs
  • The right side of the profit graph where you enter the rate and option multiplier, along with the graph begin point and incremental change factor

Option Position Input

Options Position Profit Spreadsheet Trade Entry

  • When you look at the opt column, you can see that it is gray and the position area is filled with o – since this worksheet is for options only, this column is not an input
  • Only input into the blue cells in the grayed area of the worksheet – if there is a quantity that has been entered in the position section, then the theoretical value inputs will automatically be filled in.
  • Now look at what happens if I enter all the theoretical value inputs but forget to fill in the exp mult – you lose the profit plot because theoretical value can’t be calculated
  • When entering an additional trade like the short 49 puts, look what happens when I fill in the strike, type and price BUT not the quantity – the theoretical value inputs don’t fill in and you can’t get a profit plot
  • Remember that you only fill in the blue cells and remember to change the exp mult after changing the dates
    • Watch as I change the date to 11/4, the exp mult to 4.5, and the underlying to 49.75

Lastly, look at the last 2 columns on the trade entry spreadsheet – you can see the individual theoretical value and profit of each option in the position.

  • At 49.75 the 51p long has a profit of .54 and the 2 49p shorts have a profit of .31 = .85 – which coincides with what you can see on the date column of the profit graph at 49.75

The 2nd input area is very simple:

  • You have the rate and option multiplier cells in yellow – you can enter a new rate when it changes, but leave the mult at 100 for equity options
  • And like the inputs for all of the profit graphs, you control the graph start point and incremental moves using the incr and begin inputs