Don't Be Afraid of Options - Part 8: Options Spreadsheet

Figure 1: Pre-built Option Strategies

Ok, I lied when I said Part 7 was the last of this Option series...it's what happens when you have bad planning!  Really, this is the last post of the series!  As I promised in my Fancy Options post, I would make my options spreadsheet available.  You can download it here.  Please remember to have macros enabled.

What Does It Do?
I call the spreadsheet an options builder, and that's exactly what it does.  You can build an options strategy with stock shares and up to 4 different options.  At the top of the spreadsheet, you will see a number of buttons that outlines the various pre-built strategies.  By clicking on any of the buttons here, the spreadsheet will configure itself for that particular strategy.  For example, if I click on "Long Strangle", it would set the number of stock shares to 0, set one of the option to be buying of a call, and another option to be buying of a put.  It doesn't actually enter the prices for you...come on, you still have to do some work!

Figure 2: Entering Data

Now that you have chosen your strategy, it's time to enter in the data.  In Figure 2, I have shown my recent position in GRMN, This is simply a Long Call strategy, where I bought 2 contracts of Jan 2011 calls @ $26 strike price.  At the left hand side are some input boxes for the general information about the underlying stock.  At the time of purchase, the stock price was around $27.  I bought 0 shares because I had only bought the calls.  The "display low price" and "display high price" is for graphical display purposes only.  This is the range in which the data will be shown in the graphs to the right.  Lastly, there is the commission cost per transaction.

To the right, there are 4 columns where you can enter in your various options.  Here, I only had 1 option, where I bought a call with an expiry date of January 2011 and strike price of $26.  The actual option price was $3.75 and I bought 2 contracts.  I've added some "clear cells" buttons to make it easier for you to start over from scratch.

Figure 3: Option Strategy Outcomes

Now that you've entered all of the data, it's time to see what happens in various situations.  For this, you can scroll further down in the spreadsheet for the raw data, or you can scroll to the left to look at the graphs, which is what I prefer.  I've shown 2 graphs.  The first one on the left shows the absolute dollar amounts that your option strategy would be worth at the expiry date.  The yellow line is a reference line of what the stock price was when you first entered into this position.  It gives you an idea of how far the stock needs to move for your position to be profitable.  As you can see here, my position breaks even if the stock rose to $30.

On the graph to the right, the information shown is similar, except the gains are shown in percentage rather than absolute dollar amounts.  I've added a light blue line that shows the percentage gain that the underlying stock has made.  This is intended to show the amount of leverage that you have with your position.

How Should This Spreadsheet Be Used?
In my opinion, this options spreadsheet should be used anytime you plan on entering into a position with options involved.  One of the biggest hidden costs for any options strategy is the commissions.  Since option contracts are typically cheap (e.g. my 2 GRMN calls cost me $779), commissions can add up fairly quickly if you're planning on trading the option.

In addition, the time value of an option is also something that is easily forgotten.  When I bought my GRMN calls, the stock was at around $27.  The strike price was $26, and yet the option cost me $3.75/share.  Therefore, the time value of the option was $2.75!  If I were to hold onto the option until expiry, the stock would need to move $2.75 (or greater than 10%) in order for my position to be profitable.  The graphs will highlight that for you.

Lastly, if you're into spreads and other fancy options, this sheet is useful because you can quickly tell when your strategy becomes profitable just by looking at the graph.  You can also play around with strike prices and expiry dates to optimize your position.  Play around with it.  It's actually quite fun to try different scenarios.

Questions?
If you have any questions on how to use this spreadsheet or have a suggestion for improvement, please leave me a comment or email me.  You can find my contact info in the "About Me" link on the right.  Have fun!