Options Trading Pricing Formula Inputs And Outputs

In this video we are going into the options trading spreadsheet and discuss the worksheet called Greeks.  This worksheet contains the option pricing formula that allows us to calculate theoretical value, the option greeks, and option implied volatility.

Options Spreadsheet Greeks Worksheet

Options Spreadsheet Greeks WorksheetWhen you look at the worksheet you can see the column names for the inputs, which are the blue cells:

  • Date
  • Expiration mult – the number we use for days to expiration
  • Underlying
  • Option type
  • Strike price
  • Volatility

Rate, which is a pricing formula input is a yellow cell, and you can see it is filled in as .25%.  The reason for this is interest rates change so infrequently that I have that as the input I have been using for months.

And you can see the formulas and outputs, which are the white cells – the key outputs are:

  • Theoretical value
  • Delta
  • Gamma
  • Vega
  • Theta

At the bottom of the spreadsheet, you can see an input for option price and an output for implied volatility.

Implied volatility uses all the base inputs from the options pricing formula except volatility – instead it uses the actual option price to back out the volatility that is being implied by that price.

Options Spreadsheet Volatility Input

Before we start filling in all of the pricing formula inputs, let’s talk about volatility.

All the inputs are straightforward, with the exception of what number to use for volatility.  Volatility is a measure for uncertainty and a very key component for pricing options, as well as making option trading decisions.

Consider you buy a put, you know that part [if not all] of that price is time value, because of not knowing how much the underlying price will move before expiration.  For that option to be profitable at expiration, the underlying price must go down further than the volatility priced into the option price.

So, do we use historical volatility, which gives us a measurement for how much the underlying has been moving in the past?  Or do we use implied volatility, which gives us a measurement for the amount of volatility to expect going forward?

For our trading strategies that are often using weekly options with shorter term expirations, I have felt that using implied volatility is a better input than historical volatility.

That being said, it is also important to know whether the relationship between historical volatility calculated for the previous 20-30 days and implied volatility are typical.

For instance, the volatility for Facebook options with 10-14 days to expiration is around 45%.  But I am currently looking at the ATM call that expires in 3.5 days, and it has an implied volatility of 156%.  This huge difference is coming from Facebook earnings on 10/30, which is before the option expiration.

I would not want to be doing any option price projections, or making any trade decisions, using 45% as my volatility input.

Options Greeks Worksheet Inputs And Outputs

Options Spreadsheet Greeks Worksheet Inputs And OutputsStart filling in the spreadsheet with the date you are modelling for and the expiration date.

You will see that days to expiration is actually 1 day short when you include the current date and expiration date – so we will use the exp mult [expiration multiplier] as our days to expiration input.

In this case, there are 9 full days [not 8] between the dates, but you can use partial days as your input.  Just be sure that you do not enter 0 for your input, because the formulas cannot calculate.

Skip the volatility input for now, and then fill in the underlying price, option type, and option strike price.

After that is done, go to the bottom and enter the option price from your quotes.

This will give you the implied volatility – use that for your volatility input.  When you now enter volatility, you will see your output for theoretical value and the option greeks.