Getting going with the position sheet There are 20 option lines, the default is 10 each for calls and puts. Position details need to be entered into the first 4 columns. In case the lines are protected and you can not click into them, click on the button "add more legs" in order to unprotect them.
Should you need more than 10 calls or puts, you can define which lines are calls and which are puts in column P. Here is how to do it: First, click on the button "add more legs". Second, enter the letter "c" for calls or "p" for puts in column P, defining which line is which. Third, click on the button "clean up", this will give call lines a white and put lines a grey background. Entering the position The example trade options shown on the screenshots have already expired, also the underlying used (GOOG) has moved up a lot since the example was first written.
When going through the example with your copy of the Excel Option Calculator, please use GOOG 700 and 650 strike CALLS, and 450 and 500 strike PUTS, expiring in January 2009 instead of the options shown in the screenhots. Let's try everything out with an example trade, an Iron Condor on Google. (It is not important if this is a good trade, it is just an example).
Enter the ticker symbol GOOG into C27. This will change the underlying for all sheets in the Option Calculator. Please read the comment in C27 about index tickers. Maybe also have a look at the "misc" sheet, which has a table of indices and (in red) the index tickers that will work best with the Excel Option Calculator. Click on the "add more legs" button at cell K14, this unprotects all option lines for entering data. Enter the numbers 10 and -10 into A3 and A4, respectively, and also into A15 and A16, respectively. This defines the long and short legs of the Iron Condor. Click on the "Clean up" button at I14, this blanks out and protects unused lines.
There are three ways to enter the option details: either all manual, or retrieving the option ticker, or retrieving strike and expiration from the web.
We'll assume that we don't know the option tickers and let the program find them:
Enter the strike prices 700 and 650 into C3 and C4, and 450 and 500 into C15 and C16.
Click into D3 and select Jan 09 from the dropdown menu. Do the same for D4, D15 and D16. Note: There is talk on the street about introducing options that expire weekly. You can enter any date you wish into column D by hand, even if it is not listed in the dropdown menu.
In line 14, click on the button "get option tickers". This will pull the option tickers from the Internet.  Now that we know the tickers, you can try the opposite for demonstration purposes: Delete the strike prices and expiration dates you just entered manually, then click on "get strikes/exp.", also in line 14. Dividend settings If you are trading options of an underlying that pays a significant dividend, the dividend has to be included into all calculations to receive precise results. The Option Calculator takes care of dividends in a comfortable way.
(Note: You can try this step by temporarily changing the ticker symbol (position sheet C27) from GOOG to, for example, XOM, which pays dividends. Remember to set dividends back to zero and change back to GOOG afterwards).
Click on the button in E2 on the position sheet, this will take you directly to the dividend settings. Either enter a dividend date and annual amount manually or retrieve it automatically from Yahoo! by clicking the button. Define estimated dividend growth and number of dividends per year in column G and click on "build dividend schedule". Confirm the dialog box. Set all dividends to zero with the button on the left. Risk Free Rate The interest rate settings are one page down from the dividend settings, but can also be reached from O2 on the position sheet. The option model automatically uses the rates from these settings according to the time to expiration of the options involved. The button will retrieve interest rates automatically from the U.S. Treasury's web site. |
