Options Analysis Toolkits
Contents
1. IMUOA_DPRICE IMUOA_DDELTA IMUOA_DGAMMA IMUOA_DRHO IMUOA_DKAPPA IMUOA_DTHETA IMUOA_DTHETA2 IMUOA_DFUGIT Chapter 1 Before You Begin 21 Sample Applications 3 D Simulation Analysis File Name IMSOPT1 XLS Strike price Cox Rubenstein American w 1 26 783 00234 Samm Strike price 7 Description Users can use this to visualise effect of changes in any of the key options variables The user can select any of the input variables for X or Y axis and any of the calculated variables for Z axis The up and down percentage is specified by the users This provides a flexible scenario analysis tool for all the options 22 Chapter 1 Before You Begin Simple Options Calculator File Name IMSOPT2 XLS 4 22 2006 18 16 Cox Rubenstein Amer Stocks UT Description This is an option calculator describing how the non discrete dividend add in functions can be used in their simplest form The sheet calculates option price and Greeks and also calculates implied volatilities from the market price of the option based on the selected model Chapter 1 Before You Begin 23 Discrete Dividend File Name IMSOPT3 XLS MSFT 50 27 J a 18 30 Cox Rubenstein Amer Stocks w T v I Description This sheet describes how the discrete dividend payments can be incorporated in the models The user is required to enter present value of di2. price 0 5 of the option price passed to IMUOA_IMPLIED_VOLZ i e tolerance on 0 5 In Excel if a different level of accuracy is required the IMUOA_VOL_TOL function can used to alter the tolerance in the implied volatility function i e how close a price is considered hit This may considerably alter the speed of calculation Excel Syntax IMUOA_VOL TOL tolerance tolerance is the tolerance for IMPLIEDVOL Example To set a 0 1 tolerance for the IMPLIEDVOL use MUOA_VOL_TOL 0 1 Related Function IMUOA_DIMPLIEDVOL IMUOA_IMPLIEDVOL IMUOA_NORMALI Description returns the cumulative normal distribution of a normal distribution Excel Syntax IMUOA_VOL_TOL standard_deviations standard_deviations is the standard deviation Example The cumulative normal below the 1 standard deviation mark is given by MUOA_NORMAL 1 0 16 Chapter 1 Before You Begin which equals 0 841 Chapter 1 Before You Begin 17 IMUOA_SETOPTSTEP Description Sets the numbers of steps used in the binomial tree for American options Excel Syntax MUOA_SETOPTSTEP steps steps is the number of steps The minimum recommended steps is 16 the maximum is 606 The steps should be an even number Example To set the steps to 50 use IMUOA_SETOPTSTEP 50 which equals 50 18 Chapter 1 Before You Begin Discrete Dividend Functions If European options on cash pays one or more discrete dividends before
3. rho theta and theta2 It uses Black Scholes and Cox Rubenstein binomial models for the calculations thus supporting options on Stocks and Stock Indices Users have ability to change the number of steps to over 600 for Cox Rubenstein binomial model It also contains a cumulative normal distribution function to provide extra flexibility This enables the users to produce pricing matrices risk return profiles and implied volatility analysis for either individual or portfolio of options The models take account of constant and or discrete dividend payments when calculating the values The Option add in comprises a 3 D graphical simulation for option analysis Users can use this to visualize effect of changes in any of the key option variables Functions Reference IMUOA_PRICE Description Chapter 1 Before You Begin 5 returns the theoretical price of an option based on the option_class_type and option_model_type Excel Syntax Example IMUOA_PRICE short_term_ interest rate underlying_price strike_price volatility years to expiry external_discount option class type option model type short_term_interest_rate underlying_price strike_price volatility discount option class_type option model_type is the short term interest rate is the price of the underlying instrument is the strike price is the volatility of the underlying is years to maturity is the external fo
4. the option expires the present value of the dividend should be deducted from the underlying instrument price and the standard functions above should be used However American options due to the possibility of early exercise need a different approach There are 9 additional functions listed below which mirror IMUOA_PRICE IMUOA_DELTA IMUOA_GAMMA IMUOA_RHO IMUOA_KAPPA IMUOA_THETA IMUOA_THETA2 IMUOA_FUGIT and IMUOA_IMPLIEDVOL functions discussed previously but take two extra parameters at the end of the list of arguments IMUOA_DPRICE IMUOA_DDELTA IMUOA_DGAMMA IMUOA_DRHO IMUOA_DKAPPA IMUOA_DTHETA IMUOA_DTHETA2 IMUOA_DFUGIT IMUOA_DIMPLIEDVOL Description Discrete dividend functions for IMUOA_PRICE IMUOA_DELTA IMUOA_GAMMA IMUOA_RHO IMUOA_KAPPA IMUOA_THETA IMUOA_THETA 2 IMUOA_FUGIT and IMUOA_IMPLIEDVOL Excel Syntax MUOA_DPRICE pv_dividend_array dividend_time_array 3MUOA_DELTA pv_dividend_array dividend_time_array MUOA_DGAMMA pv_dividend_array dividend_time_array 3MUOA_DRHO pv_dividend_array dividend_time_array MUOA_DKAPPAI pv_dividend_array dividend_time_array MUOA_DTHETA pv_dividend_array dividend_time_array SMUOA_DTHETAQ pv_dividend_array dividend_time_array SMUOA_DFUGIT pv_dividend_array dividend_time_array SMUOA_DIMPLIEDVOL pv_dividend_array dividend_time_array
5. Chapter 1 Before You Begin 1 Options Analysis Toolkit Before You Begin The User s Manual describes how to use Intermark Financial Tool kits Version 8 for Microsoft Excel on a Windows platform Before continuing you should be familiar with basic Excel skills in particular formula concepts and techniques Chapter 2 describes some features of Excel array formulas for those not familiar with the subject System Requirements To run the Intermark Financial Tool kits you must have the following hardware and software installed on your computer e Microsoft Windows 2000 SP4 XP SP1 e Upto 15 megabytes MB of available disk space on a stand alone computer e Microsoft Excel 2000 or above Chapter 1 Using The Tool kits The add in functions are developed as Dynamic Link Libraries DLLs DLLs are a key feature of Microsoft s operating environment It enables applications to use libraries at run time rather than at compile time This provides flexibility and enables the functions to be called from Excel Macro Visual Basic for Application Visual Basic Microsoft Access C C or Visual C compilers Attaching the Add In Macro Spreadsheet To make the functions of Intermark Financial Toolkits available you need to activate the Optoolkit add in The file optoolkit xla is placed into the Volatility SDK installation folder Please use the Tools gt Add Ins menu item in Microsoft Excel and press the Browse button Navigate then t
6. DVOL Description returns the implied volatility of an option based on the option_class_type and option model type Excel Syntax IMUOA_IMPLIEDVOL short_term_interest rate underlying_price strike_price option_price years_to_expiry external discount option_class_type option model type short_term_interest_rate underlying_price strike_price option_price discount option_class_type option_model type Remarks is the short term interest rate is the price of the underlying instrument is the strike price is the price of the option is years to maturity is the external force of discount and is required for options on currencies and cash For options on cash it is the continuous compounded constant dividend yield Where an underlying cash instrument pays a discrete dividend please see section on Discrete Dividend Functions is set to 0 for puts and 1 for calls is set to 0 for Black Scholes 1 for Cox Rubinstein By default a returned implied volatility if entered in the PRICE function will return an option price 0 5 of the option price passed to IMPLIED_VOLA i e tolerance on 0 5 Related Function IMUOA_DIMPLIEDVOL IMUOA_VOL_TOL Chapter 1 Before You Begin 15 IMUOA_VOL_TOL Description Sets the tolerance in the implied volatility function By default a returned implied volatility if entered in the PRICE function will return an option
7. e theoretical minimum price the function will return the error value of 99999999 9 in Excel When analysing option portfolios these error values should be checked for HAHAHA This occurs if the column is not wide enough to display the values This could be because the add in is returning the error value 99999999 9 Status Code For the Yield Add in if the function call is successful the result requested by return_type is returned See Appendix I for return_type definitions However if an error occurs then the value ERR or N A is returned There is one exception to this rule which occurs when return_type specifies that the status for the calculation be returned type 27 In this case a value of ERR or N A will not be returned but a status code which can be useful in determining the source of error in a calculation is returned See Appendix I for Status Codes Note Please refer to Microsoft Excel s manuals or on line help for further information Exiting Excel When exiting Excel the system will automatically close the hidden file XLMs by calling the AUTO_CLOSE macro defined in the macro sheets This can be invoked by using the Macro Run Command 4 Chapter 1 Before You Begin Chapter 2 Options Valuation Module reference The Options add in calculates option price and implied volatility for European and American style options It also calculates sensitivities such as delta gamma fugit kappa vega
8. h THETA2 In the rare event of a negative value being returned this would indicate a price increase in the option To calculate the theta per actual day 365 days per year multiply the returned value by 252 365 Excel Syntax IMUOA_THETA Sshort term_interest rate underlying_price strike_price volatility years_to_expiry external discount option_class_type option model type short_term_interest rate 15 the short term interest rate underlying_price is the price of the underlying instrument strike_price is the strike price volatility is the volatility of the underlying is years to maturity external_ discount is the external force of discount and is required for options on currencies and cash For options on cash it is the continuous compounded constant dividend yield Where an underlying cash instrument pays a discrete dividend please see section on Discrete Dividend Functions option class_type is set to for puts and 1 for calls option model_type is set to 0 for Black Scholes 1 for Cox Rubinstein Example Consider a European put option with a strike of 2200 on an index The present value of the index is 2190 with a volatility of 37 12 The short term interest rate is 14 0 the option expires on 31 Oct 89 and today s date is 29 Sep 89 The theta of the option under a Black Scholes model is calculated by IMUOA_THETA 14 2190 2200 37 12 DATE 89 10 31 DATE 89 9 29 365 0 0 0 whic
9. h equals 2 09 Per actual day with would be 2 09 252 365 which equals 1 44 Related Function IMUOA_DTHETA IMUOA_DTHETA2 IMUOA_THETA2 Chapter 1 Before You Begin 11 12 Chapter 1 Before You Begin IMUOA_THETA2 Description returns the theta2 of an option based on the option_class_type and option model type This is the price drop of the option per business day 252 days per year assuming the instrument price does not change compare with IMUOA_THETA This would be the same as Theta for a future In the rare event of a negative value being returned this would indicate a price increase in the option To calculate the theta per actual day 365 days per year multiply the returned value by 252 365 Excel Syntax IMUOA_THETA2 short_term_interest rate underlying_price strike_price volatility years to expiry external_discount option class type option model type short_term_interest rate is the short term interest rate underlying_price is the price of the underlying instrument strike_price is the strike price volatility is the volatility of the underlying is years to maturity external_ discount is the external force of discount and is required for options on currencies and cash For options on cash it is the continuous compounded constant dividend yield Where an underlying cash instrument pays a discrete dividend please see section on Discrete Dividend Functions option class_t
10. in Chapter 1 Before You Begin 9 IMUOA_KAPPA Vega Description returns the kappa of an option based on the option_class_type and option_model_type This is the price change of the option if the implied volatility moves by 1 Excel Syntax IMUOA_KAPPA short term_interest rate underlying_price strike_price volatility years_to_expiry external discount option_class_type option model type short_term_interest_rate underlying_price strike_price volatility external option class_type option model_type Related Function IMUOA_DKAPPA is the short term interest rate is the price of the underlying instrument is the strike price is the volatility of the underlying is years to maturity is the external force of discount and is required for options on currencies and cash For options on cash it is the continuous compounded constant dividend yield Where an underlying cash instrument pays a discrete dividend please see section on Discrete Dividend Functions is set to for puts and 1 for calls is set to 0 for Black Scholes 1 for Cox Rubinstein 10 Chapter 1 Before You Begin IMUOA_THETA Description returns the theta of an option based on the option_class_type and option_model type This is the price drop of the option per business day 252 days per year assuming the instrument price moves exactly as assumed by the underlying model compare wit
11. o Volatility SDK installation folder and find this file press OK Now all the different option calculation models implemented in Intermark Toolkits are available for using in your MS Excel application Calling The Add In Functions From Excel Excel Syntax Example IMUOA Price parameters Excel usage hints Functions Passing Arrays Some of the tool kit functions require that an argument be entered as an array In Excel arrays can be passed as either I arange on a spreadsheet e g 1 5 or II as absolute values enclosed in Y braces e g 1 2 3 4 5 6 A signifies the end of one row and the beginning of next 2 Chapter 1 Before You Begin In Excel a mixture of I and II is not permitted i e A1 B1 C1 is not a valid function input Functions Returning Arrays Some of the tool kit functions return an entire array of values In the Yield add ins the functions ending with RANGE are such functions e g IMUOA_DARRAY Excel has an in built capability for functions to return arrays A few Excel functions that use this feature are the TREND LINEST and GROWTH functions Functions that return arrays must however be entered as array formulas To create an array formula 1 Selectthe range of cells in which you want to enter the formula 2 Enter the formula 3 Press CONTROL SHIFT ENTER or press SHIFT ENTER click the check box in the formula bar For information on arrays see Array in E
12. rce of discount and is required for options on currencies and cash For options on cash it is the continuous compounded constant dividend yield Where an underlying cash instrument pays a discrete dividend please see section on Discrete Dividend Functions is set to for puts and 1 for calls is set to 0 for Black Scholes 1 for Cox Rubinstein Consider a European put option with a strike of 100 on a stock The present value of the stock is 85 5 with a volatility of 37 12 The short term interest rate is 14 0 the option expires on 31 Oct 89 and today s date is 29 Sep 89 The price of the option under a Cox Rubinstein model is calculated by Related Function MUOA_PRICE 14 85 5 100 37 12 DATE 89 10 31 DATE 89 9 29 365 0 0 1 IMUOA_DPRICE IMUOA_DARRAY Chapter 1 Before You Begin IMUOA_DELTA Description returns the delta of an option based on the option class_type and option_model_type Excel Syntax MUOA_DELTA short_term_interest_rate underlying_price strike_price volatility years to expiry external_discount option class type option model type short_term_interest_rate underlying_price strike_price volatility external option class_type option model_type Related Function IMUOA_DDELTA is the short term interest rate is the price of the underlying instrument is the strike price is the volatility of the underlying i
13. represent the parameters of the non discrete dividend associated function pv_dividend_array is an array containing the present value of the discrete dividends dividend_time_array is an array containing the time in years of the discrete dividends ordered with the most recent dividend first Example See IMOPT3 XLS for an illustration of these functions Related Function IMUOA_DARRAY IMUOA_PRICE IMUOA_DELTA IMUOA_GAMMA IMUOA_RHO IMUOA_KAPPA IMUOA_THETA IMUOA_THETA2 IMUOA_FUGIT IMUOA_IMPLIEDVOL Chapter 1 Before You Begin 19 20 Chapter 1 Before You Begin IMUOA_DARRAY Description Returns an array of analysis for the discrete dividends IMUOA_DPRICE function The elements of the array are option price delta gamma rho theta theta2 kappa fugit Excel Syntax MUOA_DARRAY pv_dividend_array dividend_time_array represent the parameters of the non discrete dividend associated function pv_dividend_array is an array containing the present value of the discrete dividends dividend_time_array is an array containing the time in years of the discrete dividends ordered with the most recent dividend first Remarks This function must be entered as an array formula in Excel The horizontal array that is return with this function can be transposed using the Excel TRANSPOSE function Example See IMOPT4 XLS for an illustration of this function Related Function
14. ries by dividing each data item in the series by the previous data item and taking the natural log of this number i e using LN function With this new series the volatility can be calculated using the standard deviation function i e STDEV STDEVP in Excel The volatility needs to be annualized Thus if the series is a daily series the volatility would be multiplying by the square root of the number of business days per year e g SQRT 252 if you assume there are 252 business days per year It is thus possible to creating a historical volatility graph using a variable time window Short Term Interest Rate This is the continuously compounded risk free interest rate to the maturity of the option It is thus necessary to convert any simple money market interest rate to the continually compounded rate The following formula can be used LN 1 money market rate x countinously compounded rate time to maturity where 7 days to maturity money market days per annum If days to maturity is greater than 365 use 365 in formula and set time to maturity to 1 Example If T 182 360 if money market rate is quoted 360 basis time to maturity 182 365 or division by 365 25 for leap years and money market rate 8 then the continuously compounded rate 7 95
15. s years to maturity is the external force of discount and is required for options on currencies and cash For options on cash it is the continuous compounded constant dividend yield Where an underlying cash instrument pays a discrete dividend please see section on Discrete Dividend Functions is set to for puts and 1 for calls is set to 0 for Black Scholes 1 for Cox Rubinstein Description Chapter 1 Before You Begin 7 IMUOA_GAMMA returns the gamma of an option based on the option_class_type and option_model_type Excel Syntax Example MUOA_GAMMA short_term_interest_rate underlying_price strike_price volatility years to expiry external_discount option class type option model type short_term_interest_rate underlying_price strike_price volatility external_discount option class_type option model_type is the short term interest rate is the price of the underlying instrument is the strike price is the volatility of the underlying is years to maturity is the external force of discount and is required for options on currencies and cash For options on cash it is the continuous compounded constant dividend yield Where an underlying cash instrument pays a discrete dividend please see section on Discrete Dividend Functions is set to for puts and 1 for calls is set to 0 for Black Scholes 1 for Cox Rubinstein Consider a European put option with a
16. strike of 2200 on an index The present value of the index is 2190 with a volatility of 37 12 The short term interest rate is 14 0 the option expires on 31 Oct 89 and today s date is 29 Sep 89 The gamma of the option under a Black Scholes model is calculated by which equals Related Function IMUOA_GAMMA 14 2190 2200 37 12 DATE 89 10 31 89 9 29 365 0 0 0 0 0016 IMUOA_DGAMMA IMUOA_RHO Description Chapter 1 Before You Begin returns the rho of an option based on the option_class_type and option_model type Excel Syntax IMUOA_RHO short_term_interest rate underlying_price strike_price volatility years to expiry external_discount option class type option model type short_term_interest_rate underlying_price strike_price volatility external_discount option class_type option model_type Related Function IMUOA_DRHO is the short term interest rate is the price of the underlying instrument is the strike price is the volatility of the underlying is years to maturity is the external force of discount and is required for options on currencies and cash For options on cash it is the continuous compounded constant dividend yield Where an underlying cash instrument pays a discrete dividend please see section on Discrete Dividend Functions is set to for puts and 1 for calls is set to 0 for Black Scholes 1 for Cox Rubinste
17. urrent clean price of the bond setting the Strike Price to the clean strike price of the option and setting the External Force of Discount to the current yield of the bond coupon divided by price This approach is applicable to both European and American style options The second approach is Dirty Price including accrued approach This approach is applicable only to European style options This uses the simple non discrete dividend functions while setting the Underlying Instrument Price to the current dirty price of the bond setting the Strike Price to the dirty strike price of the option and setting the External Force of Discount to zero If the bond pays one or more coupons before expiry of the option the present value of the coupon should be deducted from the Underlying Instrument Price The Yield Add in could be used to calculate the dirty prices 26 Chapter 1 Before You Begin This Dirty Price approach is more accurate than the Clean Price approach since it takes into account the reinvestment income from the accrued of the bond The Clean Price approach can however be modified to take this into account by first calculating the forward price of the bond then calculating the External Force of Discount which would create that forward price Historical Volatility To calculate the historical volatility of a series the series would first be transformed into a natural log se
18. vidend and dividend dates All of the function which take account of the discrete dividend payments start with D e g IMUOA_DPRICE IMUOA_DDELTA etc 24 Chapter 1 Before You Begin Discrete Dividend Array Return File Name IMSOPT4 XLS 4 Oct 20 Jan 0 0 0 I MSET Cox Rubenstein Amer Stocks w A gt La Goto Array Example Description This sheet is same as the previous one but describes how the function IMUOA_DARRAY can be used for the calculation of price and sensitivities The function returns a horizontal array and it is faster than the individual function calls Chapter 1 Before You Begin 25 Multiple Option Series File Name IMSOPT5 XLS cox Rubenstein Aner stocks os 232 007 oa 1951 1989 L 2026 877 7 25 50 0 175 __21 56 19 90 __19 15 18 48 Description This spreadsheet shows how the functions can be used for multiple option analysis The market prices are entered for both call and put options with a different exercise date and price The spreadsheet calculates implied volatilities and deltas Analytic Notes Options on Bonds Cash Models Black Scholes 0 and Cox Rubenstein 1 There are two main approaches to analyzing an option on a bond The first approach is the Clean Price approach This uses the simple non discrete dividend functions while setting the Underlying Instrument Price to the c
19. xcel Reference Manual A useful function to use in conjunction with array formulas is TRANSPOSE function Errors When using the add ins the following error values may be displayed on a spreadsheet NAME The NAME error value occurs when you use a name that Microsoft Excel does not recognise A possible cause of this error is misspelling the function Use the function wizard to help prevent this The REF error value occurs when you refer to a cell that is not valid The most likely cause of this error is when the add in has not been loaded Please try loading the relevant macro sheet by going to the directory and then selecting the filename from the open file dialogue box Microsoft Excel 22 This document contains links Rie establish links No If a sample spreadsheet contains links and display the message This document contains links Re establish link then you should make sure the relevant macro sheet is opened Chapter 1 Before You Begin VALUE The VALUE error value occurs when you use the wrong type of argument or operand 3 This error can be caused when there is a space or a character in an input range or in an input cell 99999999 9 If an incorrect value e g negative volatility or negative time is given as a parameter in a function call or if a function could not return a valid value e g the implied volatility function due to an option price being lower than th
20. ype is set to for puts and 1 for calls option model_type is set to 0 for Black Scholes 1 for Cox Rubinstein Related Function IMUOA_DTHETA2 IMUOA_DTHETA IMUOA_THETA Chapter 1 Before You Begin 13 IMUOA_FUGIT Description returns the fugit of an option based on the option_class_type and option_model_type This is the expected time to exercise For a European option this will always be the number of years to expiry For an American option the fugit can be significantly shorter Excel Syntax IMUOA_FUGIT short_term_interest rate underlying_price strike_price volatility years to expiry external discount option class_ type option_model type short_term_interest_rate underlying_price strike_price volatility external_discount option class_type option model_type Related Function IMUOA_DFUGIT is the short term interest rate is the price of the underlying instrument is the strike price is the volatility of the underlying is years to maturity is the external force of discount and is required for options on currencies and cash For options on cash it is the continuous compounded constant dividend yield Where an underlying cash instrument pays a discrete dividend please see section on Discrete Dividend Functions is set to for puts and 1 for calls is set to 0 for Black Scholes 1 for Cox Rubinstein 14 Chapter 1 Before You Begin IMUOA_IMPLIE
Download Pdf Manuals
Related Search