Excel Worksheet for Stocks Trading - Excel

Document Sample
Excel Worksheet for Stocks Trading - Excel Powered By Docstoc
					Version 2.1
Introduction
This spreadsheet is provided free of charge for you to use to further your education in options trading.
The formulas used were taken from two great books on option trading
          Option Volatility and Pricing by Sheldon Natenberg
          Financial Models using Excel by Simon Benninga
                    You can read more about these books from the www.OptionTradingTips.com website
                    http://www.optiontradingtips.com

If you have any suggestions for this workbook or even have a bug to report, please feel free to email
me at admin@optiontradingtips.com

To stay up to date with changes to this workbook and other modifications to the website, please feel free
to subscribe:
http://www.optiontradingtips.com/subscribe.html

I have written all the formulas in Visual Basic using modules and are freely disclosed, so you may examine
the logic, edit and change the calculations as you like.


Troubleshooting
You will need to have Macros enabled for the calculations to work.
Follow the steps below to enable Macros:

Excel 97 - 2003
Go to Tools/Options
Click on the Security tab
Go to Macro Security
Change the setting to Medium
Close and reopen the workbook. It will ask you if you want to enable Macros, click Yes

Excel 2007
First, make sure that you can see the Developer tab
If there is no Developer tab, click on the Office icon to the top left of the application
and choose Excel Options, which is located at the bottom right of the popup.
Now click on Popular at the top left. Check the box titled "Show developer tab in the ribbon".

Now, click on the Developer tab.
Click on Macro Security.
Select "Disable all macros with notification" and press ok.
Close and reopen spreadsheet.
Now you should see a Security Warning appear in the toolbar
that reads "Macros have been disabled". Click on the options button.
Select "Enable this content" and press ok.


Release Updates
11/06/07 - Fixed a bug on the OptionStrategies tab. The greeks for Stock positions were previously displaying
27/04/06 - Fixed a bug that failed to calculate the theoretical change in P&L for stocks in the strategies tab.
28/08/06 - Fixed a small calculation bug for the Option Theta, which now has a near perfect accuracy.
1/10/06 - Added support for Dividend Yield, which can be used as a workaround to price options on futures
by making the Dividend Yield the same as the Interest Rate.


Basic
The worksheet labelled Basic is a simple option pricer.
You simply enter the option details into the yellow shaded areas and
the output values will be displayed underneath in the blue shaded cells.


OptionPage
OptionPage is a worksheet that allows you to price a string of calls and puts for the same expiration
date. Again, you enter the option details into the yellow cells and the output values are in the blue
shaded cells.

You can calculate the market implied volatility for each option by simply typing in the market price of
the option in the column labelled "Market Price" and the volatility implied by the option's market value
will show in the column "Implied Volatility".

Column's A and L are where you can change the strike prices used for the calculations. Or you can just
have the values derive straight from the Underlying Price by default.


OptionStrategies
This worksheet lets you enter combinations of options positions so you can view the payoff/risk graphs.
The underlying data for the options is taken from the information entered into the OptionPage worksheet.
Then just enter the number of contracts for each position and remember to put a negative number for sell
positions.
For Type, just enter "c" for call options, "p" for put options and "s" for stock/futures position.
If you know what the option is trading at, you can enter the market price in the "override premium"
row to calculate the P&L of the position after taking into account the actual price of the option.


StrategyGraphs
This worksheet will show the current Greek position values of your option strategy while referencing the
a range of underlying prices. You can change the range of values by changing the cell A17 in the OptionStrateg
worksheet.
ation in options trading.



nTradingTips.com website


ease feel free to email


e website, please feel free



closed, so you may examine




n the ribbon".




tions were previously displaying as Put options.
 r stocks in the strategies tab.
a near perfect accuracy.
 d to price options on futures
r the same expiration
alues are in the blue


g in the market price of
 e option's market value


ulations. Or you can just




w the payoff/risk graphs.
the OptionPage worksheet.
t a negative number for sell

tures position.
e "override premium"
ce of the option.



egy while referencing the
the cell A17 in the OptionStrategies
http://www.OptionTradingTips.com

            Underlying Price              1000   The current base price of the instrument, eg, the closing price of Microsft Stock
               Exercise Price             1000   The price at which the underlying instrument will be exchanged. Also called Strike Pric
                Today's Date          2/3/2011
                  Expiry Date        2/26/2011   The Date which the contract expires
           Historical Volatility          20%    The Historical Volatility of the asset's returns
              Risk Free Rate            5.00%    The current risk free interest rate i.e. your return on cash held in the bank
             Dividened Yield            0.00%    The Annualized Dividend Growth Rate of the Stock
                 DTE (Years)              0.06

                                   Call Option Put Option
             Theoretical Price      #NAME?      #NAME?
                         Delta      #NAME?      #NAME?         The amount that the theoretical price will change if the market moves up
                      Gamma         #NAME?      #NAME?         The amount that the Delta will change if the market moves up/down 1 po
                        Theta       #NAME?      #NAME?         The amount that the theoretical price will change when 1 day passes.
                         Vega       #NAME?      #NAME?         The amount that the theoretical price will change if the volatility of the as
                          Rho       #NAME?      #NAME?         The amount that the theoretical price will change if interest rates move u
ent, eg, the closing price of Microsft Stock
rument will be exchanged. Also called Strike Price



your return on cash held in the bank




oretical price will change if the market moves up/down 1 point
lta will change if the market moves up/down 1 point
oretical price will change when 1 day passes.
oretical price will change if the volatility of the asset moves up/down by 1 percentage point
oretical price will change if interest rates move up/down by 1 percentage point
       25.00    Underlying Price                            <-- Yellow cells are for user input
   3-Feb-11     Today's Date                                <-- Blue cells are the calculated outputs
    30.00%      Historical Volatility                       This worksheet allows you to price multiple strikes for calls and puts
  28-Feb-11     Expiry Date                                 with the same Underlying Price. Remember, you can customise your
      3.50%     Risk Free Rate                              you like by using the formulas provided.
      2.00%     Dividend Yield
          25    DTE
        0.07    DTE in Years

                                           Call Options
                                Market
                        Theoretical      Implied                 Option Greeks
Strike Prices           Price Price      Volatility Delta   Gamma Vega      Theta        Rho
       22.50      ITM   ######           ####### #######    ####### ####### #######      #######
       23.00      ITM   ######           ####### #######    ####### ####### #######      #######
       23.50      ITM   ######           ####### #######    ####### ####### #######      #######
       24.00      ITM   ######           ####### #######    ####### ####### #######      #######
       24.50      ITM   ######           ####### #######    ####### ####### #######      #######
       25.00     ATM    ######           ####### #######    ####### ####### #######      #######
       25.50     OTM    ######           ####### #######    ####### ####### #######      #######
       26.00     OTM    ######           ####### #######    ####### ####### #######      #######
       26.50     OTM    ######           ####### #######    ####### ####### #######      #######
       27.00     OTM    ######           ####### #######    ####### ####### #######      #######
       27.50     OTM    ######           ####### #######    ####### ####### #######      #######
 rice multiple strikes for calls and puts for the same Expiry
e. Remember, you can customise your own workbook any way




                                                       Put Options
                                            Market
                                    Theoretical      Implied                 Option Greeks
             Strike Prices          Price Price      Volatility Delta   Gamma Vega      Theta     Rho
                    22.50    OTM    ######           ####### #######    ####### ####### #######   #######
                    23.00    OTM    ######           ####### #######    ####### ####### #######   #######
                    23.50    OTM    ######           ####### #######    ####### ####### #######   #######
                    24.00    OTM    ######           ####### #######    ####### ####### #######   #######
                    24.50    OTM    ######           ####### #######    ####### ####### #######   #######
                    25.00    ATM    ######           ####### #######    ####### ####### #######   #######
                    25.50     ITM   ######           ####### #######    ####### ####### #######   #######
                    26.00     ITM   ######           ####### #######    ####### ####### #######   #######
                    26.50     ITM   ######           ####### #######    ####### ####### #######   #######
                    27.00     ITM   ######           ####### #######    ####### ####### #######   #######
                    27.50     ITM   ######           ####### #######    ####### ####### #######   #######
                     Strat1        Strat2      Strat3          Strat4          Strat5          Strat6           Strat7
          Contracts            1        -1
               Type       Stock        Call
              Strike          25        25
Calculated Premium #NAME?          #NAME?               0.00            0.00            0.00            0.00             0.00
 Override Premium
     Premium Used #NAME?           #NAME?               0.00            0.00            0.00            0.00             0.00
              Delta         1.00   #NAME?               0.00            0.00            0.00            0.00             0.00
            Gamma           0.00   #NAME?               0.00            0.00            0.00            0.00             0.00
              Theta         0.00   #NAME?               0.00            0.00            0.00            0.00             0.00
              Vega          0.00   #NAME?               0.00            0.00            0.00            0.00             0.00
                Rho         0.00   #NAME?               0.00            0.00            0.00            0.00             0.00


   Graph Increment
                                                         Profit / Loss at Expiration
              1.00
                                       1.00

                                       0.90

                                       0.80

                                       0.70

                                       0.60

                                       0.50

                                       0.40

                                       0.30

                                       0.20

                                       0.10

                                       0.00
                                              20   21     22     23      24     25      26     27   28     29      30




                    P&L Payoff at Expiration Matrix
   Underlying Price Strat1      Strat2   Strat3      Strat4      Strat5      Strat6      Strat7
                 20       -5.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 21       -4.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 22       -3.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 23       -2.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 24       -1.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 25        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 26        1.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 27        2.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 28        3.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 29        4.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 30        5.00 #NAME?          0.00        0.00        0.00        0.00        0.00

                    Current Theoretical P&L Relative to Underlying Price Changes
   Underlying Price Strat1      Strat2   Strat3      Strat4      Strat5      Strat6      Strat7
                 20       -5.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 21       -4.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 22       -3.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 23       -2.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 24       -1.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 25        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
                 26        1.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              27        2.00   #NAME?          0.00       0.00        0.00       0.00        0.00
              28        3.00   #NAME?          0.00       0.00        0.00       0.00        0.00
              29        4.00   #NAME?          0.00       0.00        0.00       0.00        0.00
              30        5.00   #NAME?          0.00       0.00        0.00       0.00        0.00

                 Current Position Delta Relative to Underlying Price Changes
Underlying Price Strat1      Strat2    Strat3      Strat4      Strat5      Strat6      Strat7
              20        1.00 #NAME?           0.00        0.00        0.00        0.00        0.00
              21        1.00 #NAME?           0.00        0.00        0.00        0.00        0.00
              22        1.00 #NAME?           0.00        0.00        0.00        0.00        0.00
              23        1.00 #NAME?           0.00        0.00        0.00        0.00        0.00
              24        1.00 #NAME?           0.00        0.00        0.00        0.00        0.00
              25        1.00 #NAME?           0.00        0.00        0.00        0.00        0.00
              26        1.00 #NAME?           0.00        0.00        0.00        0.00        0.00
              27        1.00 #NAME?           0.00        0.00        0.00        0.00        0.00
              28        1.00 #NAME?           0.00        0.00        0.00        0.00        0.00
              29        1.00 #NAME?           0.00        0.00        0.00        0.00        0.00
              30        1.00 #NAME?           0.00        0.00        0.00        0.00        0.00

                 Current Position Gamma Relative to Underlying Price Changes
Underlying Price Strat1      Strat2  Strat3      Strat4      Strat5      Strat6      Strat7
              20        0.00 #NAME?         0.00        0.00        0.00        0.00        0.00
              21        0.00 #NAME?         0.00        0.00        0.00        0.00        0.00
              22        0.00 #NAME?         0.00        0.00        0.00        0.00        0.00
              23        0.00 #NAME?         0.00        0.00        0.00        0.00        0.00
              24        0.00 #NAME?         0.00        0.00        0.00        0.00        0.00
              25        0.00 #NAME?         0.00        0.00        0.00        0.00        0.00
              26        0.00 #NAME?         0.00        0.00        0.00        0.00        0.00
              27        0.00 #NAME?         0.00        0.00        0.00        0.00        0.00
              28        0.00 #NAME?         0.00        0.00        0.00        0.00        0.00
              29        0.00 #NAME?         0.00        0.00        0.00        0.00        0.00
              30        0.00 #NAME?         0.00        0.00        0.00        0.00        0.00

                 Current Position Theta Relative to Underlying Price Changes
Underlying Price Strat1      Strat2   Strat3      Strat4      Strat5      Strat6      Strat7
              20        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              21        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              22        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              23        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              24        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              25        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              26        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              27        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              28        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              29        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              30        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00

                 Current Position Vega Relative to Underlying Price Changes
Underlying Price Strat1      Strat2   Strat3      Strat4      Strat5      Strat6      Strat7
              20        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              21        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              22        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              23        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              24        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              25        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              26        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              27       0.00   #NAME?          0.00        0.00       0.00        0.00       0.00
              28       0.00   #NAME?          0.00        0.00       0.00        0.00       0.00
              29       0.00   #NAME?          0.00        0.00       0.00        0.00       0.00
              30       0.00   #NAME?          0.00        0.00       0.00        0.00       0.00

                 Current Position Rho Relative to Underlying Price Changes
Underlying Price Strat1      Strat2   Strat3      Strat4      Strat5      Strat6      Strat7
              20        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              21        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              22        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              23        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              24        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              25        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              26        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              27        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              28        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              29        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
              30        0.00 #NAME?          0.00        0.00        0.00        0.00        0.00
Strat8          Strat9          Strat10      Total



         0.00            0.00        0.00

         0.00            0.00        0.00    #NAME?    Total Cost
         0.00            0.00        0.00    #NAME?    Position Delta
         0.00            0.00        0.00    #NAME?    Position Gamma
         0.00            0.00        0.00    #NAME?    Position Theta
         0.00            0.00        0.00    #NAME?    Position Vega
         0.00            0.00        0.00    #NAME?    Position Rho




Strat8          Strat9          Strat10      P&L     Intercept
         0.00            0.00         0.00    #NAME? #NAME?
         0.00            0.00         0.00    #NAME? #NAME?
         0.00            0.00         0.00    #NAME? #NAME?
         0.00            0.00         0.00    #NAME? #NAME?
         0.00            0.00         0.00    #NAME? #NAME?
         0.00            0.00         0.00    #NAME? #NAME?
         0.00            0.00         0.00    #NAME? #NAME?
         0.00            0.00         0.00    #NAME? #NAME?
         0.00            0.00         0.00    #NAME? #NAME?
         0.00            0.00         0.00    #NAME? #NAME?
         0.00            0.00         0.00    #NAME? #NAME?


Strat8          Strat9          Strat10      P&L
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00        0.00    #NAME?
         0.00            0.00        0.00    #NAME?
         0.00            0.00        0.00    #NAME?
         0.00            0.00        0.00    #NAME?


Strat8          Strat9          Strat10      Total
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?


Strat8          Strat9          Strat10      Total
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?


Strat8          Strat9          Strat10      Total
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?


Strat8          Strat9          Strat10      Total
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00        0.00    #NAME?
         0.00            0.00        0.00    #NAME?
         0.00            0.00        0.00    #NAME?
         0.00            0.00        0.00    #NAME?


Strat8          Strat9          Strat10      Total
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
         0.00            0.00         0.00    #NAME?
                      P&L at Expiration                                                   Delta

1.00                                                           1.00
0.90                                                           0.90
0.80                                                           0.80
0.70                                                           0.70
                                                               0.60
0.60
                                                               0.50
0.50
                                                               0.40
0.40                                                           0.30
0.30                                                           0.20
0.20                                                           0.10
0.10                                                           0.00
0.00                                                                  20   21   22   23   24
       20   21   22    23   24   25   26   27   28   29   30

                            Gamma                                                         Theta


1.00                                                           1.00
0.90                                                           0.90
0.80                                                           0.80
0.70                                                           0.70
                                                               0.60
0.60
                                                               0.50
0.50
                                                               0.40
0.40
                                                               0.30
0.30                                                           0.20
0.20                                                           0.10
0.10                                                           0.00
0.00                                                                  20   21   22   23   24
       20   21   22    23   24   25   26   27   28   29   30

                            Vega                                                           Rho


1.00                                                           1.00
0.90                                                           0.90
0.80                                                           0.80
0.70                                                           0.70
0.60                                                           0.60
0.50                                                           0.50
0.40                                                           0.40
0.30                                                           0.30
0.20                                                           0.20
0.10                                                           0.10
0.00                                                           0.00
       20   21   22    23   24   25   26   27   28   29   30          20   21   22   23   24
Delta




     25     26   27   28   29   30


Theta




     25     26   27   28   29   30


 Rho




24     25   26   27   28   29   30
                                                      IMPLIED VOLATILITY
                                Low                         Neutral
              Buy Naked Puts                              Sell the
              Bear Vertical Spreads:                      Underlying
                Buy ATM Call/Sell ITM Call
    Bearish
                Buy ATM Put/Sell OTM Put
M             Sell OTM (ITM) Call (Put) Butterflies
A             Buy ITM (OTM) Call (Put) Time Spreads
R
K
              Backspreads                                 Do Nothing
E
              Buy Straddles/Strangles
T   Neutral
              Sell ATM Call Or Put Butterflies
D             Buy ATM Call Or Put Time Spreads
I
R             Buy Naked Calls                             Buy the
E
              Bull Vertical Spreads                       Underlying
C
                Buy ATM Call/Sell OTM Call
T   Bullish
I               Buy ATM Put/Sell ITM Put
O             Sell ITM (OTM) Call (Put) Butterflies
N             Buy OTM (ITM) Call (Put) Time Spreads
IMPLIED VOLATILITY
                                    High
                 Sell Naked Calls
                 Bear Vertical Spreads:
                     Buy OTM Call/Sell ATM Call
                     Buy OTM (ITM) Call (Put) Time Spreads
                 Buy ITM (OTM) Call (Put) Butterflies
                 Sell OTM (ITM) Call (Put) Time Spreads


                 Ratio Vertical Spreads
                 Sell Straddles/Strangles
                 Buy ATM Call Or Put Butterflies
                 Sell ATM Call Or Put Time Spreads


                 Sell Naked Puts
                 Bull Vertical Spreads
                     Buy ITM Call/Sell ATM Call
                     Buy OTM Put/Sell ATM Put
                 Buy OTM (ITM) Call (Put) Butterflies
                 Sell ITM (OTM) Call (Put) Time Spreads
     100   Underlying Price
     100   Exercise Price
3-Feb-11   Today's Date
 25.00%    Historical Volatility
4-Apr-11   Expiry Date
     0%    Risk Free Rate
     0%    Dividend Yield
      60   DTE
    0.16   DTE in Years

             75     80     85     90     95    100    105    110
     90 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
    100 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
    110 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?



                                                   Gamma vs Underlying Price

                                    1

                                   0.9

                                   0.8

                                   0.7

                                   0.6                                                                90

                                   0.5                                                                100

                                                                                                      110
                                   0.4

                                   0.3

                                   0.2

                                   0.1

                                    0

                                         75   80   85   90   95   100   105   110   115   120   125
    115    120    125
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
     100   Underlying Price
     100   Exercise Price
3-Feb-11   Today's Date
 25.00%    Historical Volatility
4-Apr-11   Expiry Date
     0%    Risk Free Rate
     0%    Dividend Yield
      60   DTE
    0.16   DTE in Years

             75     80     85     90     95    100    105    110
     90 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
    100 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
    110 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?



                                                        Vega vs Underlying Price

                                    1

                                   0.9

                                   0.8

                                   0.7

                                   0.6                                                                 90

                                   0.5                                                                 100

                                                                                                       110
                                   0.4

                                   0.3

                                   0.2

                                   0.1

                                    0

                                         75   80   85    90   95   100   105   110   115   120   125
    115    120    125
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
     100    Underlying Price
     100    Exercise Price
3-Feb-11    Today's Date
 25.00%     Historical Volatility
4-Apr-11    Expiry Date
     0%     Risk Free Rate
     0%     Dividend Yield
      60    DTE
    0.16    DTE in Years

                 100     90     80     70     60     50     40     30
         100 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
         100 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
             #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?


                                 Call Theta                                                 Call Option Price

    1
                                                                        1
   0.9                                                                0.9
   0.8                                                                0.8
   0.7                                                                0.7
   0.6                                                                0.6
   0.5                                                                0.5
   0.4                                                                0.4
   0.3                                                                0.3
   0.2                                                                0.2
   0.1                                                                0.1
    0                                                                   0
          100   90    80    70      60   50   40   30   20   10   1         100   90   80    70   60   50
                                        DTE         Theta   Theo   % Diff
                                              100    #NAME? #NAME? #NAME?
                                               99    #NAME? #NAME? #NAME?
                                               98    #NAME? #NAME? #NAME?
                                               97    #NAME? #NAME? #NAME?
                                               96    #NAME? #NAME? #NAME?
                                               95    #NAME? #NAME? #NAME?
                                               94    #NAME? #NAME? #NAME?
                                               93    #NAME? #NAME? #NAME?
                 20     10      1              92    #NAME? #NAME? #NAME?
            #NAME? #NAME? #NAME?               91    #NAME? #NAME? #NAME?
            #NAME? #NAME? #NAME?               90    #NAME? #NAME? #NAME?
            #NAME? #NAME? #NAME?               89    #NAME? #NAME? #NAME?
                                               88    #NAME? #NAME? #NAME?
                                               87    #NAME? #NAME? #NAME?
Call Option Price
                                               86    #NAME? #NAME? #NAME?
                                               85    #NAME? #NAME? #NAME?
                                               84    #NAME? #NAME? #NAME?
                                               83    #NAME? #NAME? #NAME?
                                               82    #NAME? #NAME? #NAME?
                                               81    #NAME? #NAME? #NAME?
                                               80    #NAME? #NAME? #NAME?
                                               79    #NAME? #NAME? #NAME?
                                               78    #NAME? #NAME? #NAME?
                                               77    #NAME? #NAME? #NAME?
                                               76    #NAME? #NAME? #NAME?
                                               75    #NAME? #NAME? #NAME?
           50   40   30   20   10   1          74    #NAME? #NAME? #NAME?
                                               73    #NAME? #NAME? #NAME?
                                               72    #NAME? #NAME? #NAME?
                                               71    #NAME? #NAME? #NAME?
                                               70    #NAME? #NAME? #NAME?
                                               69    #NAME? #NAME? #NAME?
                                               68    #NAME? #NAME? #NAME?
                                               67    #NAME? #NAME? #NAME?
                                               66    #NAME? #NAME? #NAME?
                                               65    #NAME? #NAME? #NAME?
                                               64    #NAME? #NAME? #NAME?
                                               63    #NAME? #NAME? #NAME?
                                               62    #NAME? #NAME? #NAME?
                                               61    #NAME? #NAME? #NAME?
                                               60    #NAME? #NAME? #NAME?
                                               59    #NAME? #NAME? #NAME?
                                               58    #NAME? #NAME? #NAME?
                                               57    #NAME? #NAME? #NAME?
                                               56    #NAME? #NAME? #NAME?
                                               55    #NAME? #NAME? #NAME?
                                               54    #NAME? #NAME? #NAME?
                                               53    #NAME? #NAME? #NAME?
                                               52    #NAME? #NAME? #NAME?
                                               51    #NAME? #NAME? #NAME?
50   #NAME?   #NAME?   #NAME?
49   #NAME?   #NAME?   #NAME?
48   #NAME?   #NAME?   #NAME?
47   #NAME?   #NAME?   #NAME?
46   #NAME?   #NAME?   #NAME?
45   #NAME?   #NAME?   #NAME?
44   #NAME?   #NAME?   #NAME?
43   #NAME?   #NAME?   #NAME?
42   #NAME?   #NAME?   #NAME?
41   #NAME?   #NAME?   #NAME?
40   #NAME?   #NAME?   #NAME?
39   #NAME?   #NAME?   #NAME?
38   #NAME?   #NAME?   #NAME?
37   #NAME?   #NAME?   #NAME?
36   #NAME?   #NAME?   #NAME?
35   #NAME?   #NAME?   #NAME?
34   #NAME?   #NAME?   #NAME?
33   #NAME?   #NAME?   #NAME?
32   #NAME?   #NAME?   #NAME?
31   #NAME?   #NAME?   #NAME?
30   #NAME?   #NAME?   #NAME?
29   #NAME?   #NAME?   #NAME?
28   #NAME?   #NAME?   #NAME?
27   #NAME?   #NAME?   #NAME?
26   #NAME?   #NAME?   #NAME?
25   #NAME?   #NAME?   #NAME?
24   #NAME?   #NAME?   #NAME?
23   #NAME?   #NAME?   #NAME?
22   #NAME?   #NAME?   #NAME?
21   #NAME?   #NAME?   #NAME?
20   #NAME?   #NAME?   #NAME?
19   #NAME?   #NAME?   #NAME?
18   #NAME?   #NAME?   #NAME?
17   #NAME?   #NAME?   #NAME?
16   #NAME?   #NAME?   #NAME?
15   #NAME?   #NAME?   #NAME?
14   #NAME?   #NAME?   #NAME?
13   #NAME?   #NAME?   #NAME?
12   #NAME?   #NAME?   #NAME?
11   #NAME?   #NAME?   #NAME?
10   #NAME?   #NAME?   #NAME?
 9   #NAME?   #NAME?   #NAME?
 8   #NAME?   #NAME?   #NAME?
 7   #NAME?   #NAME?   #NAME?
 6   #NAME?   #NAME?   #NAME?
 5   #NAME?   #NAME?   #NAME?
 4   #NAME?   #NAME?   #NAME?
 3   #NAME?   #NAME?   #NAME?
 2   #NAME?   #NAME?   #NAME?
 1   #NAME?   #NAME?   #NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:105
posted:2/3/2011
language:English
pages:28
Description: Excel Worksheet for Stocks Trading document sample