Wednesday, October 13, 2010

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!

5 comments:

  1. Opti Farms Keto supports healthy immunity and drives to reduce overweight size
    of the body by eliminating bad cholesterol level. It improves ketosis that restricts
    fat accumulation inside the body and makes best utilized as energy boost. The supplement
    is one of the best selling product over the internet that makes you slim and stylish
    Opti Farms Keto

    ReplyDelete
  2. Skintology MD Cream is one of the best skin tag removal products that can be used to completely eliminate skin tags. Skintology products - Skintology MD skin tag removal cream uses scientific blend, natural skintology md ingredients which are active and clinically proven skin tags remover. The good part is that you can remove skin tags and mole with the help of Skintology MD skin tag removal cream or mole removal, you don't need to use the expensive and painful laser surgery. Kindly Visit on Skintology Md Mole Removal Cream

    ReplyDelete
  3. Premium Pure keto is a weight loss supplement. Premium Pure Keto Diet has numerous normal fixings which can diminish your weight mystically in only fourteen days from utilizing. It can likewise keep your digestion rate in the body. It can consume calories every day and can consume put away fat from the body. This normal weight reduction supplement has the ability to deal with your wellbeing amid its standard utilizing. You don't have to stifle your strive after taking this stunning supplement and can take after your day by day schedule abstain from food however without slick dinners dependably. Kindly Visit on Premium Pure Keto Weight Loss Diet

    ReplyDelete
  4. Hello, I am Prithvika malhotra thank you for this informative post. That is a great job. Wish you more success.Thank you so much and for you all the best. Takes Down
    https://thiscruelwar.tech

    ReplyDelete