Docstoc

Steps in Calculating Stock Volatility, Microsoft Excel - Excel

Document Sample
Steps in Calculating Stock Volatility, Microsoft Excel - Excel Powered By Docstoc
					                                                           Index Option IMR




                                      MARGIN CALCULATOR FOR SAFEX INDEX OPTIONS

                             OPTION DESCRIPTION                         Input Parameters
               Index Futures Level                                                19,500.00
               Strike Level                                                       20,000.00
               Current Date/Valuation Date                                       18-Mar-09
               T+1                                                               19-Mar-09
               Maturity/Expiry Date                                              17-Sep-09
               ATM Volatility                                                       33.00%
               Volatility from Skew and Moneyness                                   32.16%
               Call/Put                                                                 call
               Time till Expiry                                                     0.5014
               Volatility Scanning Range (VSR)                                       2.50%
               Risk Parameter (S)                                                      3.50
               Fixed Initial Margin per Futures Contract                         19,000.00

                                 Margin Requirement per Contract                               No of Contracts
               IMR with Volatility Scenario - sell option                        14,643.00              100.00
               IMR with Volatility Scenario - buy option                         10,105.00
               Sell IMR no Volatility Scenario                                   12,877.50
               Buy IMR no Volatility Scenario                                     8,646.98

               Scenario Analysis

               Volatility Scenario Set (VSS)                                          1.00               (1.00)
               Price Scenario Set (PSS)                                              (1.00)              (0.75)
               Range Price Volatility Effect (RPVE -> N_E)                          225.00
               Option Value Today                                                15,513.00
               Index Future's Scenario Values (SV)                                  17,600              18,075
               IMR including Volatility Scenario
               Volatility from RPVE and ATM                                         36.31%             35.930%
               Volatility Scenario SELL (using skew)                               32.120%             32.600%
               Option Values                                                      7,586.00            9,470.00
               Risk Array SELL                                                   (7,927.00)          (6,043.00)
               Volatility from RPVE and ATM                                        31.310%             30.930%
               Volatility Scenario BUY (using skew)                                27.120%             27.600%
               Option Values                                                      5,408.00            7,094.00
               Risk Array BUY                                                   (10,105.00)          (8,419.00)

               IMR excluding Volatility Scenario
               Volatility Scenario BUY                                             35.500%             35.500%
               Option Values                                                      9,122.31           10,881.16
               Risk Array SELL                                                   (6,390.69)          (4,631.84)
               Volatility Scenario SELL                                            30.500%             30.500%
               Option Values                                                      6,866.02            8,461.87
               Risk Array BUY                                                    (8,646.98)          (7,051.13)




Excel pricing software developed by
Financial Chaos Theory




                                                               Page 1
                               Index Option IMR




http://www.quantonline.co.za




                                   Page 2
                                              Index Option IMR




Download the newest information from
http://www.safex.co.za/ed/products_margin_req.asp


Total Margin
      1,464,300
      1,010,500




           (0.50)                (0.25)                   -          0.25        0.50


          18,550               19,025                 19,500       19,975      20,450

        35.650%               35.480%                35.430%       35.480%     35.650%
        33.170%               33.810%                34.590%       35.440%     36.450%
      11,635.00             14,068.00               16,800.00    19,778.00   23,037.00
      (3,878.00)            (1,445.00)               1,287.00     4,265.00    7,524.00
        30.650%               30.480%                30.430%       30.480%     30.650%
        28.170%               28.810%                29.590%       30.440%     31.450%
       9,097.00             11,407.00               14,053.00    16,982.00   20,221.00
      (6,416.00)            (4,106.00)              (1,460.00)    1,469.00    4,708.00


       35.500%                35.500%                35.500%       35.500%     35.500%
      12,830.95             14,971.20               17,299.52    19,811.81   22,502.45
      (2,682.05)              (541.80)               1,786.52     4,298.81    6,989.45
       30.500%                30.500%                30.500%       30.500%     30.500%
      10,273.99             12,304.66               14,553.08    17,015.61   19,686.10
      (5,239.01)            (3,208.34)                (959.92)    1,502.61    4,173.10




                                                     Page 3
Index Option IMR




    Page 4
                        Index Option IMR




    0.75        1.00


  20,925      21,400

  35.930%    36.310%
  37.540%    38.660%
26,513.00   30,156.00
11,000.00   14,643.00
  30.930%    31.310%
  32.540%    33.660%
23,701.00   27,366.00
 8,188.00   11,853.00


  35.500%    35.500%
25,364.62   28,390.50
 9,851.62   12,877.50
  30.500%    30.500%
22,556.33   25,616.36
 7,043.33   10,103.36




                            Page 5
                                              SSF Option IMR




           MARGIN CALCULATOR FOR SAFEX SINGLE STOCK FUTURE OPTIONS

              OPTION DESCRIPTION              Input Parameters
Single Stock Futures Price                                 250.00
Strike Price                                               250.00
Current Date/Valuation date                            18-Mar-09
T+1                                                    19-Mar-09
Maturity/Expiry Date                                   18-Jun-09
Volatility                                                62.00%
Call/Put                                                      call
Time till Expiry                                          0.2521
Volatility Scanning Range (VSR)                            4.00%                               Download the newest information from
Risk Parameter (S)                                           3.50                              http://www.safex.co.za/ed/products_marg
Fixed Initial Margin per Futures Contract               4,300.00

                  Margin Requirement per Contract                        No of Contracts    Total Margin
IMR with Volatility Scenario - sell option                   3,130.36           1,000.00         3,130,360
IMR with Volatility Scenario - buy option                    1,985.37                            1,985,370
Sell IMR no Volatility Scenario                              3,010.95
Buy IMR no Volatility Scenario                               2,077.22

Scenario Analysis

Volatility Scenario Set (VSS)                                    1.00              (1.00)
Price Scenario Set (PSS)                                        (1.00)             (0.75)            (0.50)
Range Price Volatility Effect (RPVE -> N_E)                    135.00
Option Value Today                                           3,092.00
SS Future's Scenario Values (SV)                               207.00            217.75            228.50




                                                    Page 6
                                                      SSF Option IMR




Download the newest information from
http://www.safex.co.za/ed/products_margin_req.asp




                           (0.25)               -


                         239.25              250.00




                                                         Page 7
                                                       Help


Excel pricing software developed by

Financial Chaos Theory                                        HELP: MARGIN CALCULATOR FOR SAFEX OPTIO
http://www.quantonline.co.za
                                      Calculates the margin requirements for options on single stock futures and


                                                      Option Inputs
                                      Underlying Price/Level
                                      Strike Price/Level
                                      Current Date/Valuation Date
                                      Maturity/Expiry Date
                                      Volatility (from surface for index options)
                                      Call/Put
    Index Options: Please                            Margin Inputs
    note that the volatility          Volatility Scanning Range (VSR)1
    input should be the value
    as obtained from the vol          Risk Parameter (S)2
    surface. This is NOT the
    ATM vol but the vol read
                                      Fixed Initial Margin per Futures Contract3
    off the vol surface using
    the strike/moneyness and          Notes:
    expiry date                          1. The Volatility Scanning Range (VSR) is the maximum increase or decrease in the volatility
                                            volatility scenarios. For more information on the Volatility Scanning Range, see the referen
                                         2. The Risk Parameter (S) is the number of standard deviations expressing the probability of
                                            over one day. Assuming lognormal distributed changes in the underlying, S is usually take
                                          This value of S correspond to assuming that over one day a 0.05% (100% - 99.95%) worst c
                                            For more information on the Risk Parameter, see the reference below.
                                         3. Safex updates this from time to time. See the link below.

                                      Reference:
                                       SAFEX MARGINING: TECHNICAL SPECIFICATIONS.
                                       http://www.safex.co.za/ap/docs/clearing/margining_specs1.pdf
                                       http://www.safex.co.za/ed/margining_methodology.asp
                                       SAFEX MARGIN REQUIREMENTS
                                      http://www.safex.co.za/ed/products_margin_req.asp




                                                      Page 8
                                                                          Help




P: MARGIN CALCULATOR FOR SAFEX OPTIONS

equirements for options on single stock futures and index futures.

                                                                                              Cells in blue
                            Example                     Restriction/Description               take inputs
                               20,000.00       underlying forward or futures price/level.
                                                                                              Cells in shades
                               20,000.00       >0                                             of yellow and
                               20-Oct-08       < Maturity / Expiry Date                       gray are outputs
                              18-Jun-09        > Current Date                                 and can't be
                                                                                              changed
                                 35.00%        > 0 - in percentage format
                                     call      p / put or c / call                            Gives the
                                                                                              margins
                                     3.00% > 0 - in percentage format
                                       3.50 > 0
                                 19,000.00 > 0


Range (VSR) is the maximum increase or decrease in the volatility used to determine the
more information on the Volatility Scanning Range, see the reference below.
is the number of standard deviations expressing the probability of the loss event occurring
 lognormal distributed changes in the underlying, S is usually taken as 3.5.
d to assuming that over one day a 0.05% (100% - 99.95%) worst case loss event occurs.
 the Risk Parameter, see the reference below.
time to time. See the link below.


ICAL SPECIFICATIONS.
 s/clearing/margining_specs1.pdf
rgining_methodology.asp




                                                                         Page 9
                    Help




Cells in blue
take inputs

Cells in shades
of yellow and
gray are outputs
and can't be
changed


Gives the
margins




                   Page 10
USER NOTES on Excel SETUP

Excel 2002/XP/2003

Some of the calculators used in this workbook are written in VBA
Before any calculation can be undertaken, the following needs to be done
Step 1: Add-Ins
Add-in programs are small modules that extend Excel's power by giving you access to an array of features and cal
To access the required Add-Ins, open Excel. Click on the menu item Tools. In the drop down menu, click on Add-I
Ensure that there are ticks in front of the following add-ins as shown
              Ø Analysis Toolpak
              Ø Analysis Toolpak – VBA
              Ø Conditional Sum Wizard
              Ø Lookup Wizard
              Ø Solver Add-In
Click OK .




If some of the above add-ins are not available, it might be that you only operate off a partial MSOffice installation.
Either do a full installation or contact your IT department to upgrade your installation to a full MSOffice installation.
You might be prompted to insert the MSOfffice CD. Do this or contact your IT department.

Step 2: Security
Open Excel. Click on the menu item Tools. In the drop down menu, move your curser to Macro. This brings up a s
Click on Security…
This brings up the Security dialog box. Click on the Security Level tab.
Ensure that Medium is selected.
Click OK



Excel pricing software developed by
Financial Chaos Theory
http://www.quantonline.co.za
 array of features and calculating functions not otherwise offered in Excel.
own menu, click on Add-Ins . This brings up the Add-Ins dialog box.




 al MSOffice installation.
full MSOffice installation.




Macro. This brings up a sub-menu.
USER NOTES on Excel SETUP

Excel 2007

If you battle to convert from MS Office 2003 to 2007, see http://office.microsoft.com/en-us/help/HA101491511033.
Some of the calculators used in this workbook are written in VBA
Before any calculation can be undertaken, the following needs to be done

Step 1: Add-Ins
Add-in programs are small modules that extend Excel's power by giving you access to an array of features and cal
To access the required Add-Ins, open Excel. Click on the menu item Tools. In the drop down menu, click on Add-I

When you first install Excel 2007, the built-in add-in programs included with Excel are not loaded and therefore are
To load any or all of these built-in add-in programs, you follow these steps:

1. Click the Office Button,         then click Excel Options or press Alt+FI to open the Excel Options dialog box an

The Add-Ins tab lists all the name, location, and type of the add-ins to which you have access.

2. Click the Go button while Excel Add-Ins is selected in the Manage drop-down list box.

Excel opens the Add-Ins dialog box showing all the names of the built-in add-in programs you can load.

3. Click the check boxes for each add-in program that you want loaded in the Add-Ins Available list box.
Ensure that there are ticks in front of the following add-ins as shown
              Ø Analysis Toolpak
              Ø Analysis Toolpak – VBA
              Ø Conditional Sum Wizard
              Ø Lookup Wizard
              Ø Solver Add-In

4. Click the OK button to close the Add-Ins dialog box.

An alert dialog box appears, asking you if you want to install each selected add-in.

5. Click the OK button in each alert dialog box to install its add-in.

After activating add-ins in this manner, Excel automatically places command buttons for the add-ins in either an An
Ribbon's Data tab or in a Solutions group on the Formulas tab, depending upon the type of add-in. For example, w
Analysis ToolPak or Solver Add-in, Excel places their command buttons in the Analysis group on the Data tab. Wh
Conditional Sum Wizard, Euro Currency Tools, or Lookup Wizard add-ins, Excel places their command buttons in
Formulas tab.

If some of the above add-ins are not available, it might be that you only operate off a partial MSOffice installation.
Either do a full installation or contact your IT department to upgrade your installation to a full MSOffice installation.
You might be prompted to insert the MSOfffice CD. Do this or contact your IT department.
Step 2: Security
Excel's security settings needs to be updated enable all macros.
The document at http://www.ksde.org/LinkClick.aspx?fileticket=BhomGawziW8=&tabid=1844
gives an excellent step by step description on how to achieve this.

Quick instructions: You can change the macro security settings in the Trust Center (Microsoft Office Button      ,
Options button, Trust Center category, Trust Center Settings button, Macro Settings category. Click beside the
option of Enable all Macros. This setting can be changed back to Disable all macros with notification after comple
budget. Click on OK, and then click on OK again.) However, if you work in an organization, your system administ
might have changed the default settings and prevented you from changing the settings. Check with system
administrator if this is the case.


Excel pricing software developed by
Financial Chaos Theory
http://www.quantonline.co.za
/help/HA101491511033.aspx




 array of features and calculating functions not otherwise offered in Excel.
own menu, click on Add-Ins . This brings up the Add-Ins dialog box.

loaded and therefore are not yet ready to use.


el Options dialog box and then click the Add-Ins tab.




you can load.

 ilable list box.




he add-ins in either an Analysis group on the
of add-in. For example, when you activate the
 oup on the Data tab. When you activate the
 eir command buttons in the Solutions group on the


 al MSOffice installation.
full MSOffice installation.
osoft Office Button       , Excel
 tegory. Click beside the last
  notification after completing
on, your system administrator
Check with system

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:37
posted:8/19/2011
language:English
pages:18
Description: Steps in Calculating Stock Volatility, Microsoft Excel document sample