Options Pricing Spreadsheet For Theoretical Values And Greeks

The options price chain worksheet has been added to our trading spreadsheet for doing different trading scenarios.  By using this options worksheet, we can get the calls and puts theoretical values and related greeks, like from a typical quotes page – but calculated from user inputs for underlying price, days to expiration, and volatility.

You will find the worksheet tab in your spreadsheet, with the name GreeksChain – if you don’t have this worksheet, then get the update from the options trading spreadsheet download page.

While doing a facebook stock options trading video [11-6 to 11-7 trading], we talked about a trading scenario for buying calls and selling puts, if there was a confirmed reject of 48.20 support.

We went into the options trading spreadsheet and entered the greeks pricing formula inputs for multiple calls and puts using 2 expirations.  From the different theoretical value calculations, it turned out that we would buy 48.5 calls for around 1.10 [11-15 expiration] and sell 46 puts for around .75 [11-22 expiration].

And while I was setting up the greeks worksheet, I was thinking [like I had many times before] about how much easier it would be if we could just enter the pricing inputs and get an options price chain showing the theoretical value and all the greeks.

So, I wrote this new worksheet call GreeksChain that will allow us to get an options chain like we get for current prices – but get this for theoretical value and the rest of the greeks using our pricing formula inputs.  And then will will be able to model effective and more efficiently.

GreeksChain Options Spreadsheet Inputs

We will go into the GreeksChain worksheet in a moment, but let’s go through the inputs and what the theoretical call options pricing chain looks like.

GreeksChain Options Spreadsheet Input

  • You should recognize the pricing formula inputs at the top – but I named the volatility input avgmiv
    • That input is for the average market implied volatility of all options
    • I will show you the chart I got this from – and you can also go to ivolatility.com to get a similar chart and numbers
    • The inputs you see are the ones we used for our potential call buy
      • The second set of inputs are for the modeled underlying price
      • Then fill-in the at the money option [ATM] strike – and the increment between the strikes
      • We were using a 48.20 underlying, which makes 48 the ATM strike – and we get strikes every .50
        • If you will look in the options price chain for the 48.5 strike, you will see the 1.122 theoretical value for our options buy

Average Implied Volatility Chart For All Options

Here is my facebook daily chart, with a volatility indicator.

Options Implied Volatility Chart

  • Yellow line is the 30 day historical volatility
  • Blue line is the implied volatility for all options
  • Red line is a 10 day moving average for the all options implied volatility

GreekChains Options Spreadsheet

We are in the greekchains worksheet now – you can see the same inputs and the calls theoretical values and greeks:

  • We were modeling for a 48.20 underlying
    • Here is what we would do if we wanted to make these changes
      • 11/8 date – 44% volatility – 47.50 underlying
      • We would buy a 48 call instead of 48.50 – and you can see the theoretical value would be .969

Now, here are the inputs for the puts sell – look at the second section

  • 11/7 11/22 expiration – 15.50 expiration mult
    • 43% avg miv – 48.20 underlying – 48 ATM strike
    • 46 put theoretical value .789

You can see that I have calls and puts options pricing chains for 3 different inputs sections.  So, you can get your theoretical prices for multiple days and or underlyings for the same expiration – or for multiple expirations.

The last section you will have to the right of the pricing chains is for profits using the theoretical value:

  • I have entered the 49-47 put ratio short – you can see that using theoretical value, you have a 1.24 profit
  • Do not change anything in the colored sections
    • For theoretical value [gray section] there is a lookup formula that will go to the pricing chains to get the value
    • Now, what if you get a 48.20 continuation sell setup – and you choose to close the short puts to be long the 49 puts
    • Let’s look at that in the next section
      • Be sure that all of the inputs are the same
      • Enter your short puts in the blue section and take the profit [the .52 profit –vs- .53 above is rounding]
      • And then delete the trade from the top section
      • And now make the following changes:
        • On the top section lower the underlying price to 47
        • The profit has gone done to 1.04 because the short puts are losing money
        • On the next section also lower the underlying price to 47
        • And you can see that the profit is 2.02, because the short 47 puts had been closed

Do note that what we just did involves taking profits on a position – and it has nothing to do with the short ratio put breakeven range or profits if held to expiration.

But now look at the 46.50 put theoretical value at .989 – what if we decide to sell those now and turn the ratio put short into a 49-46.50 spread.

  • I entered the position into the profit section and used .99 as the sell price
  • You can see it shows 0.00 profit since the price sold and theoretical value are the same

And that’s the GreeksChain worksheet from our options trading spreadsheet.  I think that it has lots of flexibility and different uses – and I really like the options price chain, compared to having to enter individual options separately.