Home

SOFTWARE MANUAL - Real Options Valuation, Inc.

1. Stock price Strike price Maturity Risk free Rate Dividend Yield Volatility Suboptimal Exercise Behavior Multiple To replicate this module use the ESO Function ESOCustomBinomialBasic When using this function set the Vesting and Forfeiture Rate inputs to zero and leave the Risk free Series and Volatility Series variables empty 21 Vesting and Suboptimal Exercise Behavior Option The Vesting and Suboptimal Behavior Option is useful for calculating simple options that cannot be executed during the vesting period but after the vesting period the option will be executed if the future stock price exceeds the Suboptimal Exercise Behavior Multiple times the strike price Typical applications include employee and executive stock options with a vesting period where the employee may execute the option even when it is suboptimal to do so as long as it is in the money i e employees sometimes do not know when it is mathematically or financially optimal to keep the option open or to execute it hence they may exercise suboptimally when the stock price value exceeds a certain threshold multiple of the strike price However the added caveat here is that the option execution cannot occur until the option is fully vested The Suboptimal Exercise Behavior Multiple is calculated from historical data and is calculated as the ratio of the stock price at which employees tend to exercise the option to the strike price at grant date As this valu
2. Benchmark Call P Black Scholes European Closed Form American Binomial European Binomial American Note The Binomial Models in above benchmarks assume a simple call put with 1 000 steps Create audit worksheets Browse Common Commands MAX MIN IF AND OR gt lt Common Variables Stock Future Stock Price Strike Steps Keeping Option Open Figure SLS1 Super Lattice Solver 33 The Basic Inputs section requires the standard option inputs such as initial stock price strike price of the option maturity annualized risk free rate annualized dividend yield annualized volatility and number of lattice steps To understand the annualized volatility please see the relevant section on volatility in this user manual To see an illustration of the use of the appropriate lattice steps see the sample case study in the appendix which provides an example of the convergence of lattice results Typically 100 1 000 steps are sufficient for a binomial lattice to converge After entering the relevant inputs select the American Option European Option or Automatic Option If your option has blackout dates or vesting periods enter the relevant lattice steps that corresponds to the vesting or blackout periods For instance on a 10 year option with 100 steps the first 4 years would be represented by steps 0 399 as step 400 is on the fourth year where the option is now vested Other examples include e
3. X r o 2 T o4T In S X r o7 2 T In S X r 0 2 T Put Xe ao S SF J 54 Generalized Black Scholes Model This is the modification of the Black Scholes model to include a dividend yield The Generalized Black Scholes is also used only for valuing European call and put options Definitions of Variables S stock price at grant date X contractual strike price r risk free rate time to maturity or expiration years annualized volatility T oO cumulative standard normal distribution b carrying cost q continuous dividend payout Computation us Call se rey IG X b a 2 T gero ELD c A In S X b o7 2 T oTo Put Xe of aum J Se H sm Notes 0 Futures options model b r q Black Scholes with dividend payment b r Simple Black Scholes formula b r r Foreign currency options model 95 on Appendix C Path Dependent Simulation Another approach useful in solving simple European options is the application of path dependent Monte Carlo simulation Figure C 1 illustrates an example of path dependent simulation this Excel file is available by clicking on Start Programs Real Options Valuation ESO Valuation Path Dependent Simulation This example requires that Crystal Ball and ESO Valuation 1 1 software be first installed In addition simulation
4. 134 99 149 18 164 87 182 21 201 38 222 55 A 4690 ILI 19 m Dill 86 99 100 34 11530 132 07 Continue Continue Continue Continue Continue Continuej Figure 10 Manual Custom Lattice results Basic American Option with Dividends Assumptions Intermediate Calculations Stock Price Stepping Time dt Exercise Cost Up Step Size up Maturity Years 10 00 Down Step Size down Risk free Rate 95 Risk neutral Probability prob Dividends 95 Volatility 24 Results 10 Step Lattice Results 5771 Generalized Black Scholes lI Closed Form American Approx Main Menu 10 Step Super Lattice 39 71 7G Super Lattice Steps 10 Steps Analyze Figure 11 Verification of results As mentioned using the Manual Custom Lattice has the advantage of being able to link to other sheets as well as to run Monte Carlo simulation Figures 12 and 13 illustrate some assumptions placed in the sheet highlighted green cells stock price and risk free rate where simulation is run using Crystal Ball and where the formulas are all transparent The result is a forecast distribution chart Finally the input assumptions can be linked from other Excel spreadsheets Customized Stock Options Results __UPSIZE_ ROB DISC FACTOR 1 105170918 CTI 0 951229425 Strike Price A Forecast stock Option value El 100 Edt Preferences View Run Help 1 000 Trials Frequency Chart 9
5. Stock price Risk free Rate Strike price Volatility Maturity Risk free Rate Dividend Yield Volatility Suboptimal Exercise Behavior Vesting Forfeiture Rates To replicate this module use the ESO Function ESOCustomBinomialBasic 25 Customized Advanced Option Changing Variables Suboptimal Behavior Forfeiture Risk free Rate Volatility Dividends and Blackouts The Customized Advanced Option is based on the Customized Basic Option module but in this module more exotic variables are used and allowed to change over time Static Inputs Required Inputs Allowed to Change Over Time Stock price Risk free Rate Strike price Volatility Maturity Forfeiture Rate Vesting Dividend Yield Multiple Blackout Periods Suboptimal Exercise Behavior Multiple All input variables are required except for the Blackout Periods which are optional The Blackout Periods entered are the step number on the lattice where the option cannot be executed even if it is in the money or exceeds the suboptimal exercise threshold For instance if the option has a 1 year maturity and 120 lattice steps are used a two week blackout period at the beginning of month six are entered as 61 62 63 64 and 65 Of course trading day adjustments can also be made and accounted for For the exotic inputs that are allowed to change over time each variable must have at least one input The ESO Toolkit allows for up to 10 changing inputs over time while the ESO Functions ca
6. databases or other spreadsheets and the results will be updated automatically In addition the inputs used in the model can be seen directly in the cell For expert users try the Real Options Analysis Toolkit 2 0 which has over 100 functions 14 NORMSDIST X V ESOCustomBinomialBasic E4 E5 E6 E7 E9 E8 5 E11 E10 0 A B G H J K M User s Own Model Function Arguments Stock Pri 50 00 ESOCustomBinomialBasic ock Price 50 lt Strike Price 50 00 elt 50 Maturity 10 00 StrikePrice E5 50 Risk free Rate 5 00 Maturity zd 10 Dividends 0 0096 xj 0 Volatility 55 00 7 0 05 Vesting 4 00 SingleVolatility E9 0 55 si Suboptimal Behavior 1 50 31 54516996 31 58 Returns the American call option with vesting suboptimal early exercise behavior forfeiture Calculation Opion Value changing risk free rates and changing volatilities StockPrice Formula result Help on this function N No d EE 11 6 60 54 G B amp 3 f E O18 S o S Figure 7 Using ESO Functions in existing spreadsheets Auditing of Formulas The software also provides formula auditing spreadsheets Click on Start Programs Real Options Valuation ESO Valuation Audit to access three audit spreadsheets Figure 8 shows the Static Binomial worksheet where the inputs are single values i e input parameters are not changing over time The other two spre
7. must be run before the spreadsheet returns valid values otherwise some of the cells may return an error value as they require simulated values to compute D E E G H l J Kal L M N Path Dependent Simulation m Input Parameters _ Maturity in Years European Option Results m American Option Results 34 Volatility Binomial Approach 39 36 Binomial Approach 39 36 Initial Stock Price Black Scholes Model 39 43 Black Scholes Model NIA Risk free Rate Path Dependent Simulation 39 52 Path Dependent Simulation Dividend Rate Path Independent Simulation 35 21 Path Independent Simulation Strike Strike Generalized Black Scholes 39 43 Path Dependent Simulation SI NIA Closed Form Approximation Mode 39 43 m Simulation Calculatior Simulate Value Binomial Steps 100 Steps Binomial Steps 100 Steps Payoff Function Time Simulate Stepsize Value Time Simulate Stepsize Value Time Simulate Stepsize 0 0 00 100 00 21 0 26 105 38 42 0 28 1 0 25 100 25 22 0 26 105 65 43 0 28 2 0 25 100 50 23 0 26 105 91 44 0 28 3 0 25 100 75 24 0 26 106 18 45 0 28 4 0 25 101 00 25 0 27 106 44 46 0 28 5 0 25 101 26 26 0 27 106 71 47 0 28 6 0 25 101 51 27 0 27 106 97 48 0 28 T 0 25 101 76 28 0 27 107 24 49 0 28 8 0 25 102 02 29 0 27 107 51 50 0 28 9 0 26 102 27 30 0 27 107 78 51 0 28 10 0 26 102 53 31 0 27 108 05 52 0 28 1
8. 0 Super Lattice Steps This term is used to describe the extended number of steps in a lattice the higher the number of steps the higher the level of precision and the higher the level of accuracy This input has to be a positive integer The higher the number of steps the smaller the time between steps become where at the limit infinite number of steps the time between steps approaches zero making the binomial lattice which by itself is a discrete simulation a continuous simulation For a non changing volatility option the typical number of steps is 1 000 For options with significant changing volatilities over time less than 100 steps are typically used In all cases the number of lattice steps has to be carefully calibrated to test for convergence Vesting in Years This applies to employee stock options with a blackout vesting period measured in years The value entered indicates that the option cannot be executed during this vesting period this is similar to a European option until the vesting period which then reverts to an American option This input is either zero with no vesting or a positive value indicating the number of years to vesting is usually less than the maturity of the option and typically ranges between 1 month 1 12 years and 8 years Volatility This is the annualized standard deviation of the continuously compounded natural logarithm of the rate of return obtained from the underlying stock price returns Wh
9. 9559 9220 1 8687 7893 Option Valuation Lattice Figure SLS7 ESO Valuation Toolkit Results of a Vesting Call Option 4l SLS Example IV American Option with Suboptimal Exercise Behavior This example shows how suboptimal exercise behavior multiples can be included into the analysis and how the custom variables list can be used see Figure SLS8 The TE is the same as the previous example but the IE assumes that the option will be suboptimally executed if the stock price in some future state exceeds the suboptimal exercise threshold times the strike price Notice that the IEV is not used because we did not assume any vesting or blackout periods Also the Suboptimal exercise multiple variable is listed on the customs variable list with the relevant value of 1 85 and a starting step of 0 This means that 1 85 is applicable starting from step 0 in the lattice all the way through to step 100 The results again are verified through the ESO Toolkit see Figure SLS9 EJReat Options Valuation Inc BAX Super Lattice Solver Custom Variables List Basic Inputs Variable Value Starting Stock Price 100 Risk Free Rate 9 5 Suboptimal 1 85 Strike Price 100 Dividend Rate 9 0 Maturity Years 10 Volatility 9 5 10 Lattice Steps 100 A inputs are annualized rates Use American Option Nonexercise Blackout Steps BermudanjVesting Option Use European Op
10. Daily Volatility 1 3596 Annualized Volatility 25 8496 Figure 14 Volatility module Be aware that the module calculates the periodic volatility as well as the annualized volatility The annualized volatility is used in the options analysis Figure 15 31 Volatility Estimates To estimate the volatility of a stock price enter the historical dosing stock prices and select the periodicity of these closing stock prices You may enter your own set of periods as required e g 256 trading days a year instead of 365 calendar days 360 days a year when rounding to 30 days a month for 12 months The resulting volatility estimate will be shown both in periodic volatilities and annualized volatilities The annualized value is usually used in the options models The Logarithmic Stock Price Returns Approach calculates the volatility using the individual past stock dosing prices and their corresponding logarithmic returns as illustrated below Starting with a series of historical stock prices the software converts them into relative returns It then takes the natural logarithms of these relative returns The standard deviation of these natural logarithm returns is the volatility of the cash flow series used in an options analysis Notice that the number of returns is one less than the total number of periods That is for time periods 0 to 5 we have six stock prices but only five returns Time Period Stock Price
11. Generalized Black Scholes Closed Form American Approx 1000 Step Binomial Super Lattice Binomial Super Lattice Steps 1000 Steps Y 2008 55 Co iii 738891 73891 Underlying Stock Price Lattice iD BERE 10000 10000 toooo 10000 3679 3079 3679 EE 1926 68 Option Valuation Lattice 66649 65703 21722 20724 19554 ee 000 Trinomial Super Lattice Steps a iE mon juu 6065 6065 ee o o 1140 37 zu o BE 383 66 37334 36210 35305 he EX 8905 6975 67 30 500 Steps 5459 82 1545982 331155 331155 __ 2008 55 2008 55 1218 25 121825 os 14841 32 164 87 a 60 65 _ O 5369 33 5359 82 322547 321642 191807 190855 64842 63891 _ o Figure 51 55 ESO Valuation Toolkit Results of a Simple American Call Option 39 SLS Example III American Option with Vesting Period This next example illustrates how a vesting option with blackout dates can be modeled Simply choose the Automatic option enter the blackout steps 0 39 Because the blackout dates input box has been used you will need to enter the TE IE and IEV Enter Max Stock Strike 0 for the TE Max Stock Str
12. or more memory TABLE OF CONTENTS Glossary of Input AssumptiOns e nnne nnne tinet ta aseo tane eaae setae setas eer aa seen ness snae eta 5 LZ PARTI TTE Guides 9 Basic European Option with Dividends esee ener enne nns 18 Basic American Option with Dividends esses ener ener enne ens 19 Vesting Requirements 20 Suboptimal Exercise Behavior Option sess sese essen enne nennen nnne nnne 21 Vesting and Suboptimal Exercise Behavior Option eese enne 22 Changing Volatility Option 23 Changing Risk Free Rate Option eese eene eene ennt innen tenete innen 24 Customized Basic Option Vesting Suboptimal Behavior Forfeiture Changing Risk free and VOlanlity itt ett ier 25 Customized Advanced Option Changing Variables Suboptimal Behavior Forfeiture Risk free Rate Volatility Dividends and Blackouts esee sees enne 26 Marketability Discount Changing Variables Suboptimal Behavior Forfeiture Risk free Rate Volatility Dividends and Blackouts cesses nennen 27 Manual Custom Latic st e RE ERE NR ERE I Y HO e SRM CER tere ren 28 Volatility Calculation Logarithmic Stock Price Returns Approach esses 3l Super Lattice SOW er SDN 33 LIST OF PUNCTIONS
13. own set of periods as required e g 256 trading days a year instead of 365 calendar days or 360 days a year when rounding to 30 days a month for 12 months The resulting volatility estimate will be shown both in periodic volatilities and annualized volatilities The annualized value is usually used in the options models Starting with a series of historical stock prices the software converts them into relative returns It then takes the natural logarithms of these relative returns The standard deviation of these natural logarithm returns is the volatility of the cash flow series used in an options analysis To replicate this module use the ESO Function ESOVolatility L MEV Calculations Enter or copy and paste the stock s historical closing prices in the area below Then select the periodicity of these closing prices The resulting annualized volatility calculation is the input ino the stock options valuation modules Remember to click on Calculate to recalculate the volatilities Historical Stock Prices m Choose Periodicity 100 00 Daily Closing Prices 101 25 101 25 Trading Days Year 365 101 50 a Calculate 102 00 Weekly Closing Prices p 105 00 Trading Weeks Year 52 wee Main Menu Lll S C Monthly Closing Prices Analyze 107 25 C Annually Closing Prices 109 25 109 25 m Results 107 25
14. start If it does not go to Windows Explorer to view the contents of the CD and double click on the setup exe file A prompt will appear asking if you wish to install the product Select Yes to continue installing the software The Welcome Screen then appears Select Next to continue Read the License Agreement Select I Accept The Agreement and hit Next to continue Select the installation directory you want to install the files to It 1s recommended that you keep the default file path Click Next to continue Select the start menu folder to create the program shortcuts It is recommended that you keep the default settings The default settings will install the software shortcuts to Start Programs ESO Valuation You can also create a desktop icon or a quick launch icon to quickly access the software Click Next when you are finished On the final installation screen click Install to begin the installation process You will be notified when the installation process is complete You may now launch and use the software For first time users you will have to enter the username and registration key that comes with the software prior to first use Note The software requires Windows 2000 XP Windows NT 4 0 SP 6a Excel 2000 XP 2003 10MB hard disk space and 256MB RAM recommended For installing on foreign computers especially those running European Windows operating systems change the Regional Settings to English USA when usin
15. with its ability of early exercise bear a higher value in relation to European options when dividends exist When there are no dividends the simple European option equals the simple American option Of course the original Black Scholes model breaks down when dividends exist and when the option is of the American type or when other exotic inputs are included vesting forfeiture blackouts suboptimal exercise behavior Static Inputs Required Stock price Strike price Maturity Risk free Rate Dividend Yield Volatility To replicate this module use the ESO Function ESOBinomialEuropeanCall and ESOGeneralizedBlackScholesCall 18 Basic American Option with Dividends This is the Basic American Option with Dividends where the holder of the stock option has the ability to exercise the option at any time up to and including the option s maturity date This module is calculated using both binomial lattices and a closed form approximation model The results illustrate a ten step binomial recombining lattice for an American call option with a continuous dividend yield The first lattice is the underlying asset lattice where the starting asset value is simulated based on the Volatility and Number of Steps inputs The second lattice is the option valuation lattice Please note that the analysis presented here uses a ten step lattice for illustration purposes only For higher levels of precision use the Super Lattice routine The higher the nu
16. 1 0 26 102 78 32 0 27 108 32 53 0 28 12 0 26 103 04 33 0 27 108 59 54 0 29 13 0 26 103 30 34 0 27 108 86 55 0 29 14 0 26 103 56 35 0 27 109 13 56 0 29 15 0 26 103 82 36 0 27 109 41 57 0 29 16 0 26 104 08 37 0 27 109 68 58 0 29 17 0 26 104 34 38 0 27 109 95 59 0 29 18 0 26 104 60 39 0 27 110 23 60 0 29 19 0 26 104 86 40 0 28 110 50 61 0 29 20 0 26 105 12 41 0 28 110 78 62 0 29 Figure C 1 Example path dependent simulation Note that path dependent simulation can only be used for solving simple European options Simulation cannot be readily used to calculate American options or any other types of options e g vesting employee suboptimal exercise behavior changing volatility and so forth Value 111 06 111 33 111 61 111 89 112 17 11245 112 73 113 01 113 30 113 58 113 86 114 15 114 43 11472 115 01 115 29 115 58 115 87 116 16 116 45 116 74 Figure C 2 illustrates a sample set of results after the simulation run is completed The sample results were obtained by running a simulation of 100 000 trials under the Latin Hypercube option in Crystal Ball with a size of 1 000 at an initial seed of 1 applied on 100 path dependent time steps As can be seen in Figure C 2 the results stemming from all three methods path dependent simulation binomial lattice and Black Scholes model provide identical results at the limit Path Dependent Simulation Sample Results m Input Paramete
17. 11825 111825 111825 63891 63891 63891 63891 Exex px ee 16 Figure 8 Auditing the formulas 17 Basic European Option with Dividends The Basic European Option with Dividends module provides the fair market value of an ESO when the option is a European option with or without dividends This module uses both a Generalized Black Scholes closed form model and binomial lattices to calculate the option value Of course European options mean that the option can only be executed at maturity and not before The module results illustrate a ten step binomial recombining lattice for a European Call Option The first lattice is the underlying asset lattice where the starting asset value is simulated based on the Volatility and Number of Steps inputs The second lattice is the option valuation lattice Please note that the analysis presented here uses a ten step lattice for illustration purposes only For higher levels of precision use the Super Lattice routine The higher the number of lattice steps the higher the level of accuracy For instance the results illustrate that the higher the number of binomial lattice steps the higher the level of precision such that on average at 500 and 1 000 steps the results from the binomial lattice are identical to the Generalized Black Scholes for a simple European Option Note that American options
18. 2 78 32 0 00 0 27 108 32 53 0 00 0 28 114 15 12 0 00 0 26 103 04 33 0 00 0 27 108 59 54 0 00 0 29 114 43 13 0 00 0 26 103 30 34 0 00 0 27 108 86 55 0 00 0 29 114 72 14 0 00 0 26 103 56 35 0 00 0 27 109 13 56 0 00 0 29 115 01 15 0 00 0 26 103 82 36 0 00 0 27 109 41 57 0 00 0 29 115 29 16 0 00 0 26 104 08 37 0 00 0 27 109 68 58 0 00 0 29 115 58 17 0 00 0 26 104 34 38 0 00 0 27 109 95 59 0 00 0 29 115 87 18 0 00 0 26 104 60 39 0 00 0 27 110 23 60 0 00 0 29 116 16 19 0 00 0 26 104 86 40 0 00 0 28 110 50 61 0 00 0 29 116 45 20 0 00 0 26 105 12 41 0 00 0 28 110 78 62 0 00 0 29 116 74 Figure C 2 Results from path dependent simulation To understand the methodology more clearly scrutinize the spreadsheet and its relevant formulas more closely and refer to Dr Johnathan Mun s Real Options Analysis text by Wiley Finance 2002 for the technical details of running path dependent simulations 57 EMPLOYEE STOCK OPTIONS VALUATION TOOLKIT 1 1 SOFTWARE CODES 1 Real Options Im Valuation DR JOHNATHAN MUN 59
19. 48 Appendix A Stochastic 5 52 Summary Mathematical Characteristics of Geometric Brownian Motion esse 53 Appendix B Options Formulas c ccccccccccccsessceesseceenceceseeeseceeaeceeaeeceeeeesaeceeaeeceeeeesaeceeaaeceeneeees 54 Black and Scholes Option Model European Obption eese 54 Generalized Black Scholes Model eee sese seen eene eene nnne tnter 55 Appendix C Path Dependent Simulation esee esee enne tenente enne 56 Glossary of Input Assumptions The following are the input assumptions used in the Employee Stock Options Valuation software version 1 1 The global list is presented here for easy reference Blackout Periods This is the variable that measures the periods when an option cannot be executed usually weeks before and after an earnings announcement by certain senior executives or personnel with fiduciary responsibilities This input is a positive integer associated with specific step numbers in the lattice and multiple blackout periods may exist Forfeiture Rate This is the rate at which the stock options are given up or forfeited each year as a proportion of total grants When an employee leaves or is terminated from a firm he or she is forced to give up or forfeit the options granted Forfeiture rates are established through annual turnover rates or proportion of option cancellations annually This inp
20. 90 Displayed 100 00 110 52 12214 134 99 149 18 a 100 00 110 52 122 14 74 08 81 87 Probability iii 5 25 000 0 46 94 55 19 64 54 75 11 14 08 27 24 5355 55 70 Continue Continue Continue Continue Continue 27 63 33 49 40 30 48 15 gt Infinity Certainty 100 00 4 tInfinity Continue Continue Continue Continue 16 99 21 36 26 65 32 94 40 34 48 92 58 70 69 75 Continue Continue Continue Continue Continue Continue Continue Continue 8 35 11 08 14 61 19 11 24 75 31 66 39 86 Continue Continue Continue Continue Continue Continue Continue 2 50 3 60 547 7 44 10 70 15 39 Figure 12 Monte Carlo simulation with manual custom lattice As seen below the formulas are available for auditing and detailed scrutiny MAX MAX D14 A 12 0 E26 C 9 E28 SD 9 SES 9 Continue Continue Continue Figure 13 Exposed formulas in the manual custom lattice 30 Volatility Calculation Logarithmic Stock Price Returns Approach The Volatility module applies the logarithmic stock price returns approach to calculate the volatility using the individual historical stock closing prices and their corresponding logarithmic returns as illustrated in Figure 14 To estimate the volatility of a stock price enter the historical closing stock prices and select the periodicity of these closing stock prices You may enter your
21. E amp Binomial American Note The Binomial Models in above benchmarks assume a simple call put with 1 000 steps Create audit worksheets awise Common Commands MAX MIN IF AND OR gt lt Common Variables Stock Future Stock Price Strike COMPUTE i i SAVE EXIT Steps Keeping Option Open Figure SLS12 SLS Results of a Call Option accounting for Vesting Forfeiture Suboptimal Behavior and Blackout Periods 46 m American Option Assumptions Stock Price Strike Price Maturity in Years Risk free Rafe 96 Dividends 96 Volatility 96 Suboptimal Exercise Multiple Vesting in Years Forfeiture Rate 96 Results Generalized Black Scholes Additional Assumptions Year Volatility 10 00 100 Step Super Lattice Super Lattice Steps 100 Steps M LE A EL Dima e Please be aware that by applying Year Risk free multiple changing volatilities over time 10 00 5 50 a non recombining lattice is required 10 00 which increases the computation time 10 00 significantly In addition only smaller 10 00 lattice steps may be computed When 10 00 many volatilities over time and many 10 00 lattice steps are required use Monte 10 00 Carlo simulation on the volatilities and 10 00 run the Basic or Advanced Custom 10 00 Option module instead For additional 10 00 5 50 steps use the ESO Funct
22. EMPLOYEE STOCK OPTIONS VALUATION TOOLKIT 1 1 SOFTWARE MANUAL lt Real Options V Valuation DR JOHNATHAN MUN This manual and the software described in it are furnished under license and may only be used or copied in accordance with the terms of the license agreement Information in this document is provided for informational purposes only is subject to change without notice and does not represent a commitment as to merchantability or fitness for a particular purpose by the author No part of this manual may be reproduced or transmitted in any form or by any means electronic or mechanical including photocopying and recording for any purpose without the express written permission of the author Microsoft is a registered trademark of Microsoft Corporation Windows and Windows NT are registered trademarks of Microsoft Corporation Other product names mentioned herein may be trademarks and or registered trademarks of the respective holders Written designed and published in the United States of America To purchase additional copies of this document contact the author at the address below Dr Johnathan Mun JohnathanMun cs com 2005 Dr Johnathan Mun PREFACE Welcome to the Employee Stock Options Valuation software version 1 1 ESO Valuation ESO Valuation embraces the financial options concepts as applied to valuing employee stock options For example when you purchase or obtain a call option you are purchasing or ob
23. Next the Custom Variables List section can be used to input the user s own variables such as Suboptimal Exercise Multiple Forfeitures Stock Price Barriers and so forth up to 15 variables It can also be used to change the values of a variable over time The user must designate at what step in the lattice the variable becomes effective and what the value is See the next set of examples for illustration of how this is applied The Benchmarks section shows several quick calculations including a Black Scholes model for European Call and Put options a Closed Form Partial Differential Equation for American Call and Put options approximation value and the American and European Call and Put options using binomial lattices with 1 000 steps These should only be used as benchmarks so that the user will know how far off the amount of savings or excess costs of the custom option as compared to plain vanilla options The Super Lattice Option Valuation Results box will show the results based on all the relevant inputs after clicking COMPUTE You can also click on LOAD to load a particular model or SAVE to save an existing set of inputs Finally you can also create an Excel audit sheet by selecting Create Audit Worksheet providing the file a relevant name and browsing to the relevant location to save the file Be aware that the audit sheet created is only an illustration and is only 10 steps and all blackout steps have been ignored For instance say
24. Stock Price Relative Returns Natural Logarithm of Stock Price Returns X 125 125 100 1 25 In 125 100 0 2231 95 95 125 0 76 In 95 125 0 2744 105 105 95 1 11 In 105 95 0 1001 155 155 105 1 48 In 155 105 0 3895 146 146 155 0 94 In 146 155 0 0598 The volatility estimate is then calculated as the sample standard deviation of the natural logarithm of the stock price returns X Be aware that the module calculates the periodic volatility as well as the annualized volatility The annualized volatility is used in the options analysis Function s used ESOVolatility amp Figure 15 Analyze report on volatility calculations To manually illustrate the calculations see Figure 16 ni E Stock Price Stock Price Relative Returns m o5 0 100 1 125 125 100 1 25 LN 125 100 0 2231 2 95 95 125 0 76 LN 95 125 0 2744 3 105 105 95 1 11 LN 105 95 0 1001 4 155 155 105 1 48 LN 155 105 0 3895 5 146 146 155 0 94 LN 146 155 0 0598 Figure 16 Manual computation of volatility The volatility estimate is then calculated as volatility Yx x 25 58 n lu where n is the number of X s and x is the average X value The volatility calculated is then annualized by multiplying it by the square root of the number of periods in a year 32 Super Lattice Solver The Super Lattice Solver SLS is a module part of the full version o
25. Super Lattice 36 43 Binomial Super Lattice Steps 100 Steps Y L e OL Main Menu 10 Step Trinomial Super Lattice 37 94 Trinomial Super Lattice Steps 10Steps v 27183 83 245 96 Underlying Stock Price Lattice ses a 100 00 _ 10000 9048 9048 81 87 74 08 Option Valuation Lattice 10000 10000 10000 9048 9048 8187 8187 818 740 7408 6703 6708 6065 6065 5488 5488 Figure SLS9 ESO Toolkit Results of a Call Option accounting for Suboptimal Behavior 43 SLS Example V American Option with Vesting and Suboptimal Exercise Behavior Next we have the ESO with vesting and suboptimal exercise behavior This is simply the extension of the previous two examples Again the result of 8 94 Figure SLS10 is verified using the ESO Toolkit Figure SLS11 Super Lattice Solver Basic Inputs Starting Stock Price 20 Strike Price Ix fio 1000 Use American Option Nonexercise Blackout Steps Bermudan vesting Option Use European Option 05399 f Automatic Example 1 2 10 20 15 as I uw A inputs are annualized rates Risk Free Rate 9 Dividend Rate Maturity Years Volatility Lattice Steps Custom Equations Optional Terminal Node Equation Max Asset Cost 0 Example MAX A
26. adsheets are the Basic Changing Binomial risk free rates and volatilities are allowed to change over time reflecting the algorithms used in the Customized Basic Option module in the Toolkit and Advanced Changing Binomial all inputs are allowed to change over time except stock price strike price maturity and vesting period reflecting the algorithms used in the Customized Advanced Option module in the Toolkit The cells in green are the input cells while the blue cells are the calculated values The worksheet is protected to prevent accidentally changing or deleting a formula However the user can click on any blue cell and see the formulas that generated the value in the cell Figure 8 The results of interest are the Manual and Software values The manual values are those obtained in the manual lattice and are compared to the results from the software s function calls These worksheets are to illustrate the algorithms used to calculate the ESO s value Only 5 and 10 step manual lattices are shown because higher step lattices hundreds or thousands of steps cannot be solved manually and can only be solved in the software However the same algorithm is applied in the software Thus understanding the simple 5 and 10 step 15 lattices are sufficient to understand a 1 000 step lattice as the algorithms applied are identical FORMULA AUDIT l Calculated Stock Price Step Size Strike Price Up Step Size Maturity Down Step Size Risk F
27. can be used The Toolkit only provides 10 changing values over time However the user can create his or her own spreadsheet and change as many inputs as one needs by using the ESO Functions One note of caution is required here When allowing volatilities to change over time a non recombining lattice is required which may take a significant amount of computational time for higher lattice steps It is suggested that if the user requires many volatilities changing over time coupled with a high number of lattice steps Monte Carlo simulation be used to simulate thousands of volatility iterations instead In addition for non integer Years make sure the number of lattice steps chosen are appropriate such that the points of where the input parameters are changing fall on actual lattice nodes and not between nodes Finally for the ESOCustomBinomialBasic function the changing risk free and volatility series are optional inputs and if used will supercede the single risk free and single volatility inputs For the other ESOCustomBinomialCall ESOCustomBinomialHaircut and ESOCustomBinomialPut functions the changing input series are all required inputs Getting Started Guide Installation To install the Employee Stock Options Valuation software ESO Valuation version 1 1 first exit all programs and antivirus software and follow the instructions l Insert the software CD into your CD ROM drive The installation process will automatically
28. derstand the inner workings of valuing employee stock options ESO as well as a helpful presentation tool For the advanced users all of the mathematical and options functions can be accessed directly through the use of Excel functions Clicking on the More Info buttons will provide a quick synopsis of the input variables required to run a particular model Clicking on the name of the module will take the user to the models themselves For the expert users the Manual Custom Lattice provides an alternative to solving financial stock options This module provides the analyst added modeling flexibility and the results are readily accessible for auditing purposes e g the formulas are all visible within Excel See the section on Manual Custom Lattice for details To get started click on the Basic European Option Let us now look at each section in detail The first is the Input Parameters section Figure 2 Here the relevant parameters can be typed in directly or linked in from another spreadsheet This area is characterized by its colored background For all modules a set of sample input parameters exists as a guide 11 Assumptions Stock Price Strike Price S Maturity in Years Risk free Rate 96 Dividends 96 0 0096 Volatility 96 25 0096 Figure 2 Input parameters Assuming all the inputs are correct the ntermediate Calculations section shows the time step up jump size down jump size and risk neutral probabili
29. ds This is the European call calculated using the original Black Scholes model with no dividend payments and exercisable only at expiration Function ESOBlackScholesCall 10 Black Scholes Put Option with No Dividends This is the European put calculated using the original Black Scholes model with no dividend payments and exercisable only at expiration Function ESOBlackScholesPut 11 American Call Option with Dividends Closed Form Approximation Model This is the American call option calculated using the closed form approximation model with an annualized dividend yield in percent and is exercisable ay any time prior to and including the maturity date Note that this is only an approximation model as no exact closed form models exist to calculate the value of an American option Function ESOClosedFormAmericanCall 12 American Put Option with Dividends Closed Form Approximation Model This is the American put option calculated using the closed form approximation model with an annualized dividend yield in percent and is exercisable ay any time prior to and including the maturity date Note that this is only an approximation model as no exact closed form models exist to calculate the value of an American option Function ESOClosedFormAmericanPut 49 13 Customized Basic Binomial Lattice Model Call Options with Vesting Suboptimal Behavior Forfeiture Rate Changing Risk Free Rates and Changing Volatilities This is an Am
30. e Calculations Stock Price Stepping Time dt 1 0000 Strike Price Up Step Size up 1 6487 Down Step Size down 0 6065 Risk neutral Probability prob 42 67 Results Maturity in Years Risk free Rate Dividends 96 Volatility 96 66 21 67 32 67 30 10 Step Lattice Results Generalized Black Scholes 1000 Step Super Lattice ain Men Binomial Super Lattice Steps 1000 Steps 500 Step Super Lattice 67 30 nalyze 2 Trinomial Super Lattice Steps 500 Steps v Underlying Stock Price Lattice Option Valuation Lattice Figure SLS3 ESO Valuation Toolkit Results of a Simple European Call Option 37 SLS Example II American Option Figure SLS4 illustrates an American call option where the American option is selected instead of the European option Notice that of course the values are identical as it is never optimal to exercise a standard plain vanilla call option early if there are no dividends The user may try to input a dividend rate say 3 and see that the values will now differ The results are confirmed in Figure 51 55 using the ESO Valuation Toolkit EJ Rea Options Valuation Inc I n leg Super Lattice Solver m Custom Variables List m Basic Inputs Variable Name Value Starting Stock Price 100 Risk Free Rate 5 00 Strike Price 100 Dividend Rate ic Maturity Years 10 wolatiity 2 so Lattice Steps 1000 All inputs ar
31. e annualized rates Use American Option Nonexercise Blackout Steps Bermudan Vesting Option Use European Option Automatic Example 1 2 10 20 15 Custom Equations Optional Terminal Node Equation Example MAX Asset Cost 0 Intermediate Node Equation Example MAX Asset Cost Intermediate Node Equation for Blackout Periods Benchmark Call Pul Black Scholes European 67 32 27 97 Example 20 Closed Fom American 67 32 37 19 Super Lattice Option Valuation Results Binomial European 67 30 27 96 American Option 6 7 3046 Binomial American 67 30 37 47 Note The Binomial Models in above benchmarks assume a simple call put with 1 000 steps Common Commands MIN IF AND OR gt lt Common Variables Stock Future Stock Price Strike Steps Keeping Option Open Figure SLS4 SLS Results of a Simple American Call Option 38 Basic American with Dividends Assumptions Stock Price Strike Price Maturity in Years Risk free Rate Dividends Volatility m Intermediate Calculations 3 100 00 Stepping Time at 3 100 00 Up Step Size up 10 00 5 00 Down Step Size down Risk neutral Probability prob 1 0000 1 6487 0 6065 42 67 0 00 50 00 Results 500 Step Trinomial Super Lattice 10 Step Lattice Results
32. e may differ from employee to employee using these historical trends one can perform Monte Carlo simulation using Crystal Ball on this exercise pattern Static Inputs Required Stock price Strike price Maturity Risk free Rate Dividend Yield Volatility Suboptimal Exercise Behavior Multiple Vesting To replicate this module use the ESO Function ESOCustomBinomialBasic When using this function set the Forfeiture Rate to zero and leave the Risk free Series and Volatility Series variables empty 22 Changing Volatility Option In order to mirror more closely the changing business environment the Changing Volatility Option allows the analyst to change the stock s Volatility over time For instance a firm may undergo many phases e g a later phase has lower risk uncertainty or variability than an earlier phase as time passes by or structural business changes occur e g mergers and acquisition spin offs divestiture etc and each phase has a different volatility Note that Year 1 s volatility means that it is the rate that is applied between Year 0 and Year 1 Year 2 s volatility means that it is applied between Year 1 and Year 2 and so forth The Generalized Black Scholes and closed form American approximation models are used only as benchmarks to compare the results for a simple European option without changing volatilities Applying a simple Black Scholes or Generalized Black Scholes model using the average volat
33. enever possible future expectations should be used However in practice historical closing stock prices can also be used Use the volatility module to calculate the volatility of historical prices Other methods include using Long term Equity Anticipation Prices or LEAPS implied volatilities from exchange traded options GARCH models and volatilities of market comparables This input is a positive value and is typically between 25 and 100 Special Time Series Inputs When assuming a series of changing inputs over time the input parameters have to be in a particular format For examples of the format you can review the following modules Changing Volatility Changing Risk Free Rates Customized Basic Option Customized Advanced Option or Marketability Discount It is required that the series be in different columns the first column s data are the years and the second column s data are the input parameters The following illustrates the correct format Year Risk Free Rate 1 5 2 5 3 6 4 6 5 6 This means that the risk free rate of 5 is applied for all lattice steps from Year 0 to Year 2 and 6 for all steps from Year 3 to Year 5 That is in the lattice s backward induction process Year 5 Year 4 and Year 3 values on the lattice are discounted at 6 a year to Year 2 while Year 2 and Year 1 values are discounted at 5 a year back to Year 0 to obtain the option value The Years do not have to be integers partial years
34. erican option with a vesting period single suboptimal early exercise behavior multiple single forfeiture rate multiple changing risk free rates and multiple changing volatilities The option starts off as a European option during the vesting period when the option cannot be executed then reverts to an American option at the point of vesting where it can be executed at any time up to and including the maturity period During this executable period the option will be executed if it is in the money and exceeds the suboptimal multiple times the strike price all the while the risk free rates and volatilities are changing over time Function ESOCustomBinomialBasic 14 Fully Customized Binomial Lattice Model Call Option with Vesting and Blackout Periods where all other inputs are changing over time Suboptimal Behavior Multiples Forfeiture Rates Risk Free Rates Dividend Yields and Volatilities This is an American call option with a vesting period where all other inputs are changing over time suboptimal early exercise behavior multiples forfeiture rates risk free rates dividend yields and volatilities The option starts off as a European option during the vesting period when the option cannot be executed then reverts to an American option at the point of vesting where it can be executed at any time up to and including the maturity period During this executable period the option will be executed if it is in the money exceeds the subop
35. erify the results using the ESO Toolkit 1 1 see Figure SLS3 which also provides a value of 67 30 ES Real Options Valuation Inc ie Xx Super Lattice Solver Custom Variables List m Basic Inputs Variable Name Value Starting Stock Price 100 Risk Free Rate 95 5 Strike Price 100 Dividend Rate 0 Maturity Years 10 Volatility 50 Lattice Steps 1000 All inputs are annualized rates Use American Option Nonexercise Blackout Steps Bermudan Vesting Option Use European Option Automatic Example 1 2 10 20 15 m Custom Equations Optional Terminal Node Equation Example MAX Asset Cost 0 Intermediate Node Equation Example MAX Asset Cost AU Intermediate Node Equation For Blackout Periods Benchmaik Call Pul Black Scholes European 57 32 27 97 qn Closed Form American 6732 3718 m Super Lattice Option Valuation Results Binomial European 67 30 27 95 European Option 67 3046 Binomial American 67 30 37 47 Note The Binomial Models in above benchmarks assume a simple call put with 1 000 steps Common Commands MIN IF AND OR gt lt Common Variables Stock Future Stock Price Strike Steps Keeping Option Open Figure SLS2 SLS Results of a Simple European Call Option 36 asic uropean ption wit ividends Assumptions Intermediat
36. f the ESO Valuation Toolkit 1 1 It is a highly powerful and flexible binomial lattice solver and can be used to solve many types of options that might be beyond the scope of this manual This section illustrates some of the sample ESO applications that users will most frequently encounter Figure SLSI illustrates the SLS application The user can access the SLS by clicking on Start Programs Real Options Valuation ESO Valuation Super Lattice Solver The SLS has several sections Basic Inputs Custom Equations Customs Variables List Benchmarks Create Audit Worksheet and Super Lattice Option Valuation Results EJReat Options Valuation Inc Super Lattice Solver Custom Variables List Basic Inputs Variable Name Value Starting Stock Price I zzz Risk Free Rate 95 Ha Strike Price DividendRate 2 Maturity Years i Volatility 9 5 a Lattice Steps All inputs are annualized rates Use American Option Nonexercise Blackout Steps Bermudan esting Option Use European Option Automatic Example 1 2 10 20 15 Custom Equations Optional Terminal Node Equation Example MAX Asset Cost 0 Intermediate Node Equation Example MAX Asset Cost Intermediate Node Equation For Blackout Periods Example Super Lattice Option Valuation Results Enter the relevant inputs and click COMPUTE amp LOAD SAVE EXIT
37. g the software Regional settings can be found by going to Control Panel Regional and Language Options Standard and Formats and choosing English USA Getting Started The Employee Stock Options Valuation software version 1 1 ESO Valuation has three parts The first is the ESO Toolkit which provides a graphical user interface of the models the ESO Functions which provides the user direct access to the valuation functions in Excel and several Excel worksheet templates All three are accessible directly by clicking on Start Programs Real Options Valuation ESO Valuation In addition the ESO Functions can be loaded automatically every time Excel starts To do this start Excel and click on Tools Add Ins Browse and navigate to the directory in which you installed the software to this is typically C Program Files Real Options Valuation ESO Valuation and choose the ESO Functions 1 1 xla file In order to start the software properly make sure that your Excel macro settings are set to Medium or lower That is when in Excel click on Tools Macro Security Security Level Medium When starting the ESO Valuation software click on Enable Macros when and if prompted ESO Toolkit Start the Toolkit by clicking on Start Programs Real Options Valuation ESO Valuation ESO Toolkit The Main Menu of Models Main Menu shows all 12 modules in the ESO Valuation software see Figure 1 In certain modules the option may be s
38. he inputs over time This is the advanced customized option module The first step is to Maturity Years AMERICAN complete the Required inputs section Then choose if you want an Steps N 10 C EUROPEAN American or European option Click on Only Show Formulae if you want to see the actual formulae and not the calculated results Do not check this box under normal circumstances Then decide which type of option you want to calculate i e Basic Option Combination Opions or Customized Options Choose the Basic Option for simple call option or Customized Options for creating your own ala carte options Figure 9 Manual custom lattice After clicking on OK a new spreadsheet will be created Figure 10 This spreadsheet will be created in a new workbook and is protected to prevent accidental tampering In order to unprotect the sheet so that you can run Monte Carlo simulation to view the formulas or to link to and from other spreadsheets simply click on Tools Protection Unprotect Sheet Notice that the results on the new spreadsheet are identical to those generated in the Basic American Option module in Figure 11 Both approaches provide a value of 39 71 28 Customized Stock Options Results UPSIZE DOWNSIZE X UPPROB DOWNPROB PDISCFACTOR OP 1 105170918 0 904837418 0 730949533 0269050467 0 951229425 7 Strike Price SL a ee le LEE EEE aaa 100 00 110 52 12244
39. he option during the vesting period all options are forfeited with a pre vesting forfeiture rate In this example we assume identical pre and post vesting forfeitures so that we can verify the results using the ESO Toolkit Figure SLS13 In certain other cases a different rate may be assumed EJ Reat Options Valuation Inc BAX Super Lattice Solver Custom Variables List Basic Inputs Variable Name Value Starting Step Starting Stock Price 100 Risk Free Rate 35 5 50 Suboptimal 1 80 0 Strike Price 100 Dividend Rate 9 4 00 ForfeiturePost 0 10 0 Maturity Years 10 Volatility 9 5 45 00 ForfeiturePre 10 0 Lattice Steps 100 All inputs are annualized rates Use American Option Nonexercise Blackout Steps Bermudan esting Option Use European Option 539 Automatic Example 1 2 10 20 15 Custom Equations Optional Terminal Node Equation Max Stock Strike 0 Example MAX Asset Cost 0 Intermediate Node Equation IF Stock gt Suboptimal Strike Max Stock Strike 0 IF Stock lt Suboptimal Strike ForfeiturePost DT Max Stock Strike 0 1 ForfeiturePost DT Example MAX Asset Cost Intermediate Node Equation For Blackout Periods Benchmark 1 ForfeiturePre DT Black Scholes European Example Closed Form American Super Lattice Option Valuation Results Binomial European Enter the relevant inputs and click COMPUT
40. hows a sample report that provides more information on the ESO valuation results Stock Option Analysis Toolkit BASIC AMERICAN OPTION WITH DIVIDENDS This is the Basic American Option with Dividends where the holder of the stock option has the ability to exercise the option at any time up to and including the option s maturity date This module is calculated using both binomial lattices and a closed form approximation model The results illustrate a ten step binomial recombining lattice for an American call option with continuous dividends The first lattice is the underlying asset lattice where the starting asset value is simulated based on the Volatility and Number of Steps inputs The second lattice is the option valuation lattice Please note that the analysis presented here only uses a ten step lattice for illustration purposes For higher levels of precision use the Super Lattice routine The higher the number of lattice steps the higher the level of accuracy Note that for stock options whose underlying stock does not pay any dividends the American stock option and the European stock option are identical in value as theoretically it is never optimal to exercise early When there are dividend payments it may become optimal to exercise early and the American option has more value than the European option The results are calculated using the binomial lattice approach as well as dosed form approximation model The Generalized Black Sch
41. ike 0 for the IE and for This means the option is executed or left to expire worthless at termination execute early or keep the option open during the intermediate nodes and keep the option open only and no executions are allowed during the intermediate steps when blackouts or vesting occurs The result is 49 73 see Figure SLS6 which can be corroborated with the ESO Valuation Toolkit see Figure SLS7 EJReat Options Valuation Inc 509 Lattice Solver Custom Variables List Basic Inputs Variable Name Value Starting Stock Price 100 Risk Free Rate 95 5 Strike Price 100 Dividend Rate 3 Maturity Years 10 Volatility 9 5 50 Lattice Steps 100 All inputs are annualized rates Use American Option Nonexercise Blackout Steps Bermudan esting Option Use European Option 9599 Automatic Example 1 2 10 20 15 Custom Equations Optional Terminal Node Equation Max Stock Strike 0 Example MAX Asset Cost 0 Intermediate Node Equation Max Stock Strike 0 Example MAX Asset Cost TTT Intermediate Node Equation For Blackout Periods Benchmark eo Call Black Scholes European 45 42 31 99 pee m Closed Form American 6732 3718 Super Lattice Option Valuation Results Binomial European 45 41 31 98 Bermudan Option 49 7310 amp Binomial American 50 17 40 85 m Note The Binomial Models in abo
42. ility will yield grossly incorrect values if the volatility of the underlying stock changes dramatically e g volatility trends that follow a smile frown or are upward and downward sloping Static Inputs Required Inputs Allowed to Change Over Time Stock price Volatility Strike price Maturity Risk free Rate Dividend Yield Volatility Suboptimal Exercise Behavior Vesting Forfeiture Rates To replicate this module use the ESO Function ESOCustomBinomialBasic When using this function set the Vesting and Forfeiture Rates to zero Suboptimal Exercise Behavior Multiple to 1000 and leave the Risk free Series variable empty 23 Changing Risk Free Rate Option In order to mirror more closely the changing economic environment the Changing Risk Free Rates Option allows the analyst to change the Risk Free Rates over the life of the option typically measured using the U S Treasury securities zero Treasury forward yield curve or some other proxy of a risk free rate or some other governmental security with similar maturities as the option As the yield curve can be upward sloping downward sloping flat or a combination of these this model accommodates the changing rates Note that Year 1 s risk free rate means that it is the rate that is applied between Year 0 and Year 1 Year 2 s risk free rate means that it is applied between Year 1 and Year 2 and so forth The Generalized Black Scholes model is used only as a benchma
43. in the Toolkit can be accessed directly from the user s spreadsheet in Excel That is open an existing or blank Excel spreadsheet and start ESO Valuation Functions by clicking on Start Real Options Valuation ESO 13 Valuation ESO Functions 1 1 Select Enable Macros if prompted When in Excel click on Insert Function in Excel select the All or Financial category and choose from among the 19 options functions that start with the prefix ESO A short description is also provided Figure 6 Insert Function Search for a function Type a brief description of what you want to do and then Go dick Go Or select a category Financial Select a function ESOBinomialAmericanCall ESOBinomialAmericanPut ESOBinomialDown ESOBinomialEuropeanCall ESOBinomialEuropeanPut ESOBinomialProbability ESOBinomialUp ESOBinomialAmericanCall StockPrice StrikePrice Maturity Returns the American call option with dividends using the binomial approach Help on this function Cancel Figure 6 ESO Functions For instance Figure 7 illustrates a user spreadsheet where the ESOCustomBinomialBasic function is used to obtain the option value 31 55 When entering or linking the cells to the function make sure that all required inputs are entered i e remember to scroll down the inputs list by using the vertical scroll bar Using this ESO Functions method the spreadsheet inputs can be linked to and from multiple sources
44. ing the Binomial Super Lattice Approach This American call option with vesting period gives the holder the right to execute a call option at any time after the vesting period until the maturity of the option at a set strike price calculated using the binomial approach with consideration for a dividend rate The option starts off as a European option during the vesting period and reverts to an American option at the date when vesting ends Function ESOBinomialAmericanCall 4 Binomial Lattice Down Jump Step Size This is the calculation used in obtaining the down jump step size on a binomial lattice Function ESOBinomialDown 5 European Call Option Using the Binomial Super Lattice Approach This is the European call calculation performed using a binomial approach and is exercisable only at termination Function ESOBinomialEuropeanCall 48 6 European Put Option Using the Binomial Super Lattice Approach This is the European put calculation performed using a binomial approach and is exercisable only at termination Function ESOBinomialEuropeanPut 7 Binomial Lattice Risk Neutral Probability This is the calculation used in obtaining the risk neutral probability on a binomial lattice Function ESOBinomialProbability 8 Binomial Lattice Up Jump Step Size This is the calculation used in obtaining the up jump step size on a binomial lattice Function ESOBinomialUp 9 Black Scholes Call Option with No Dividen
45. ion Figure SLS13 ESO Toolkit Results of a Call Option accounting for Vesting Forfeiture Suboptimal Behavior and Blackout Periods 47 LIST OF FUNCTIONS These functions are available for use in the full version of the ESO Valuation 1 1 software Once the full version is installed click on Start Programs Real Options Valuation ESO Valuation ESO Functions Select Enable Macros if and when prompted The software will be loaded into Excel and the following models are accessible through Excel by typing them directly in a spreadsheet or by clicking on the equation wizard icon or by selecting Insert Equation and choosing either the Financial or All categories Scroll to the ESO section for a listing of all the models 1 American Call Option Using the Binomial Super Lattice Approach This American call option gives the holder the right to execute a call option at any time up to and including the maturity period at a set strike price calculated using the binomial approach with consideration for a dividend rate Function ESOBinomialAmericanCall 2 American Put Option Using the Binomial Super Lattice Approach This American put option gives the holder the right to execute a put option at any time up to and including the maturity period at a set strike price calculated using the binomial approach with consideration for a dividend rate Function ESOBinomialAmericanPut 3 American Call Option with Vesting Us
46. ion at a future grant date An option s value is derived from another value hence its technical term financial derivative Therefore the option s value is derived from an underlying stock s price movements The stock price used in the calculation is the initial stock price at grant date that is some time in the future This stock price will move in accordance to its volatility either increasing or decreasing going forward into the future This input is a positive value and is fixed and typically range between 10 and 125 Suboptimal Exercise Behavior Multiple This value indicates a specific stock price level suboptimal exercise behavior multiple times the strike price such that if this level is exceeded the holder of the option will exercise the option if it is in the money albeit possibly suboptimally For instance a suboptimal exercise behavior multiple of 1 5 with an initial stock and strike price of 10 indicates that option exercise will take place when the stock price exceeds 15 regardless if it is optimal to do so as long as the option is in the money i e there is a positive return in exercising the option Conversely the option will still be exercised at maturity if it is optimal to do so the option is in the money even if the stock price does not exceed the suboptimal exercise multiple i e when the barrier is set too high This input is a positive value greater than 1 0 The typical range is between 1 5 and 3
47. les 1000 Step Binomial Super Lattice ED Binomial Super Lattice Steps 1000 Steps Y S 500 Step Trinomial Super Lattice 8 94 Main Menu Trinomial Super Lattice Steps 500 Steps ov alyze a i nc 109196 1091 96 2 401 71 Underlying Stock Price Lattice 24365 54 37 54 37 32 97 ae Eu N E 736 O 446 446 446 271 381 71 223 65 127 78 70 32 pom e Som i 5 Co E 8 97 j i Option Valuation Lattice Years 000 100 200 300 400 500 600 700 900 1000 Figure SLS11 ESO Toolkit Results of a Call Option accounting for Vesting and Suboptimal Behavior 45 Example VI American Option with Vesting Suboptimal Exercise Behavior Blackout Periods and Forfeiture Rate This example now incorporates the element of forfeiture into the model Figure SLS12 This means that if the option is vested and the prevailing stock price exceeds the suboptimal threshold above the strike price the option will be summarily and suboptimally executed If vested but not exceeding the threshold the option will be executed only if the post vesting forfeiture occurs but the option is kept open otherwise This means that the intermediate step is a probability weighted average of these occurrences Finally when an employee forfeits t
48. mber of lattice steps the higher the level of accuracy Note that for stock options whose underlying stock does not pay any dividends the American stock option and the European stock option are identical in value as theoretically it is never optimal to exercise early When there are dividend payments it may become optimal to exercise early and the American option has more value than the European option The results are calculated using the binomial lattice approach as well as a closed form approximation model The Generalized Black Scholes model is included as a benchmark as these three values should be similar at the limit with enough steps in the binomial lattice when no dividends exist applied in a European option and where no exotic inputs are included Only when dividends exist will the binomial lattice and closed form approximation models be different than the Generalized Black Scholes Finally for higher levels of dividends the American closed form approximation model is less robust and the binomial lattice approach with high number of steps is more accurate an exact closed form model for American options with dividends does not exist Static Inputs Required Stock price Strike price Maturity Risk free Rate Dividend Yield Volatility To replicate this module use the ESO Function ESOBinomialAmericanCall ESOGeneralizedBlackScholesCall and ESOClosedFormAmericanCall 19 Vesting Requirements Option The Vesting Requiremen
49. me when the option is able to be executed This input is a positive value and 15 typically fixed ranging from 5 to 10 years Risk Free Rate This is the annualized rate of interest or return on an asset that has relatively zero risk Government treasury securities with maturities similar to the option s maturity usually serve as a proxy for the risk free rate This input is a positive value and can be allowed to change over time If using a single fixed rate the U S Treasuries spot rate with a term equivalent to the maturity of the option is sufficient If using a series of changing risk free rates over time the Treasuries implied forward rates have to be used The typical range is from 1 to 7 Strike or Exercise Price This is the price that is required to execute the option in the future Another term for this is the strike price This value dictates the price the option holder has to pay to purchase the stock in the future This is the contractual price at which an option can be executed A call option s strike price means that is the price that the underlying stock can be bought A put option s strike price means that is the price that the underlying stock can be sold This input is positive fixed and typically set exactly at the stock price at grant date such that the option is issued at the money Stock Price This is the initial underlying stock price of the option Usually this is the forecast stock price underlying the opt
50. n accommodate an unlimited number of changing inputs To replicate this module use the ESO Function ESOCustomBinomialCall 26 Marketability Discount Changing Variables Suboptimal Behavior Forfeiture Risk free Rate Volatility Dividends and Blackouts The Marketability Discount module is based on the Customized Advanced Option module where more exotic variables are used and allowed to change over time A marketability discount exists due to the fact that ESOs are non tradable and non marketable that is they cannot be readily bought or sold in an open market This marketability restriction reduces the value of the option and the amount reduced is equal to this marketability discount Static Inputs Required Inputs Allowed to Change Over Time Stock price Risk free Rate Strike price Volatility Maturity Forfeiture Rate Vesting Dividend Yield Multiple Blackout Periods Suboptimal Exercise Behavior Multiple This module calculates a modified barrier put option where many input variables are allowed to change over time All input variables are required except for the Blackout Periods which are optional As usual the Blackout Periods entered are the step number on the lattice where the option cannot be executed even if it is in the money or exceeds the suboptimal exercise threshold For the exotic inputs that are allowed to change over time each variable must have at least one input The ESO Toolkit allows for up to 10 changing inputs o
51. ncial analysts who are comfortable with spreadsheet modeling in Excel and with stock options analysis The software and its associated algorithms were created by Dr Johnathan Mun the author of several books including Valuing Employee Stock Options Under 2004 FAS 123 Requirements Wiley 2004 Real Options Analysis Tools and Techniques for Valuing Strategic Investments and Decisions Wiley 2002 Real Options Analysis Course Business Cases and Software Applications Wiley 2002 and Applied Risk Analysis Moving Beyond Uncertainty Wiley 2003 The software was developed out of his direct consulting and advisory role with the Financial Accounting Standards Board as well as valuation consulting engagements with Fortune 500 firms on applying FAS 123 requirements He can be reached at JohnathanMun Q cs com What you will need to run the software Minimum system requirements A personal computer with a Pentium microprocessor and at least 128 MB RAM VGA or 256 color graphics adapter and monitor with at least 1024 x 768 resolution and a hard disk drive with at least 10 MB free for the software and its associated files Windows XP Windows NT 4 0 Service Pack 6 Workstation or later or Windows 2000 Professional CD ROM drive 4X or faster Excel 2000 or later Adobe Acrobat Reader 3 0 or later for viewing online documentation Recommended system requirements A personal computer with a Pentium III processor 1 GHz or faster processing speed with 256 MB RAM
52. nnualized dividend yield in percent and is exercisable only at expiration This model is based on the original Black Scholes equation but modified to include a dividend rate Function ESOGeneralizedBlackScholesCall 18 Black Scholes Put Option with Dividends This is the European put calculated using the modified Generalized Black Scholes model with an annualized dividend yield in percent and is exercisable only at expiration This model is based on the original Black Scholes equation but modified to include a dividend rate Function ESOGeneralizedBlackScholesPut 19 Volatility Estimate Using the Natural Logarithmic Returns on Cash Flows This model calculates a stock s historical volatility value using the natural logarithmic returns of past closing stock prices and annualized based on the periodicity of the data Function ESOVolatility 51 Appendix A Stochastic Processes A stochastic process is nothing but a mathematically defined equation that can create a series of outcomes over time outcomes that are not deterministic in nature That is an equation or process that does not follow any simple discernible rule such as price will increase X percent every year or revenues will increase by this factor of X plus Y percent A stochastic process is by definition non deterministic and one can insert predefined numbers into a stochastic process equation and obtain different results every time For instance the path of a st
53. ntering 1 3 5 10 if these are the lattice steps where blackout periods occur The user will have to calculate the relevant steps within the lattice where the blackout exists For instance if the blackout exists in years 1 and 3 on a 10 year 10 step lattice then steps 1 3 will be the blackout dates When selecting the Automatic Option you can either enter or leave the blackout vesting period empty but should enter the relevant Terminal Equation TE Intermediate Equation IE and Intermediate Equation during Blackouts or Vesting IEV e Ifyou enter TE only both American and European Options will be computed e If you enter TE and IE only the relevant option will be computed and during the blackout or vesting periods the option valuation will be such that it can only be kept open SLS uses the symbols to designate keeping the option open This is appropriate when it is either an American or European option e Ifyou enter either IE or IEV without TE you will get an error message e If you enter all TE IE and IEV you will get the Bermudan option which accounts for the blackout and vesting periods It is assumed that the SLS user is somewhat familiar with the fundamentals of options valuation The typical American call option requires the TE of Max Stock Strike 0 and an IE of Max Stock Strike Where again is keeping the option open For the European call option the TE is the same but the IE is simply 34
54. ock price is stochastic in nature and one cannot reliably predict the stock price path with any certainty even with a predetermined set of inputs such as a specific growth rate or volatility However the price evolution over time is enveloped in a process that generates these prices The process is fixed and predetermined but the outcomes are not Hence with stochastic simulation we create multiple pathways of prices obtain a statistical sampling of these simulations and make inferences on the potential pathways that the actual price may undertake given the nature and parameters of the stochastic process used to generate the time series The Geometric Brownian Motion which is the most common and prevalently used process due to its simplicity and wide ranging applications is briefly discussed This stochastic process is useful for forecasting stock prices and is the underlying assumption used in the binomial lattices and Black Scholes models 52 Summary Mathematical Characteristics of Geometric Brownian Motion Assume a process X where X X f20 if and only if X is continuous where the starting point is X 0 where X is normally distributed with mean zero and variance one or X e N 0 1 and where each increment in time is independent of each other previous increment and is itself normally distributed with mean zero and variance t such that X X e N 0 t Then the process dX X dt o X dZ follows a Geometric Bro
55. oles model is included as benchmark as these three values should be similar at the limit with enough steps in the binomial lattice when no dividends exist in a European option Only when dividends exist will the binomial lattice and closed form approximation models be different than the Generalized Black Scholes model Finally for higher levels of dividends the American closed form approximation model becomes less robust and the binomial lattice approach with high number of steps is more accurate This is because an exact closed form model for American options with dividends does not exist Based on the analysis it is found that the Generalized Black Scholes provides a value of 39 94 while the binomial lattice using a 10 Step Super Lattice shows a value of 39 71 The American Approximation model yields 39 94 The higher the number of steps in a binomial lattice the higher the level of precision in the results Usually 1 000 steps is sufficient to obtain a highly accurate value The following lists some of the results obtained using the binomial super lattice 10 Steps 39 71 100 Steps 39 92 300 Steps 39 93 500 Steps 39 93 1 000 Steps 39 94 The results from the binomial lattices converge indicating that the binomial results are robust Function s used ESOGeneralizedBlackScholesCall ESOClosedFormAmericanCall Figure 5 Analyze report feature ESO Functions The same 19 mathematical functions used
56. olved using different approaches e g Basic European Option is solved using the binomial lattice approach as well as closed form models such as the Generalized Black Scholes model There is also a section for the user to choose between Auto Calculate and Manual Calculate To prevent Excel from recalculating all modules at once every time an input is entered thereby sometimes taking multiple seconds you can turn the Manual Calculate on However if you turn on manual calculation remember to click on Calculate or hit Ctrl R to recalculate your results otherwise your results may not be updated In addition when you are in any of the calculation modules you can click on Main Menu or hit Ctrl M to return to this main menu 10 Employee Stock Option Valuation Toolkit 4 1 Basic European Option l Moreino For Microsoft Excel 2000 amp XP Basic American Option m Vesting ts Marketability Discount Tes Requirements Lore Info DAE Manual Custom Lattice Vesting Suboptimal Behavior More Info ging Volatility ore Info Changing Volatili EM Volatility Calculation Changing Risk Free Rates More Info Customized Basic Option s More Info Customized Advanced Option More Info ed il and criminal Auto Calc o Manual Calc Figure 1 ESO Toolkit main menu For those starting out in options analysis this ESO Toolkit interface is valuable in trying to un
57. ree Rate Risk Neutral Probability Dividend Yield Volatility Manual Software Suboptimal Exercise Black Scholes 67 32 67 32 Forfeiture Rate Binomial Lattice 46 39 46 39 Vesting Year 5 X r c n S X r c Call Em cd idis dinde nA a ORCI ren uo cdd ca Lattice Steps a zi cT x oT Period a ae ee ae 14841 32 5459 82 5459 82 33358 331155 200855 200855 2008 55 121825 121825 121825 73891 73891 73891 73891 448 17 448 17 448 17 448 17 27183 27183 _ 27183 27183 164 87 1 16487 _ 16487 16487 16487 10000 10000 10000 10000 10000 100 00 6065 amp 0 6065 _ 6065 6065 6065 36 79 B 36 79 mum 3679 3679 2231 f IF AND HS16 Vesting H29 Strike Suboptimal H29 Strike IF AND H 16 gt Vesting H29 lt Strike Suboptimal 1 Forfeiture Stepsize Prob l5 C 1 Prob l52 EXP Riskfree Stepsize Forfeiture Stepsize MAX H29 Strike 0 IF HS 16 lt Vesting 1 Forfeiture Stepsize Prob 150 1 Prob 152 EXP Riskfree Stepsize 1 83 1 83 0 67 14741 32 8901 71 5359 82 5359 82 321155 321155 1908 55 __ 190855 _ 1908 55 1
58. rements The result that should be used is from the binomial lattice Static Inputs Required Stock price Strike price Maturity Risk free Rate Dividend Yield Volatility Vesting This module uses the ESO Function ESOBinomialAmericanVesting Call 20 Suboptimal Exercise Behavior Option The Suboptimal Behavior Option is useful for calculating simple options that will be executed if the future stock price exceeds the Suboptimal Exercise Behavior Multiple times the strike price Typical applications include employee stock options where the employee may execute the option even when it is suboptimal to do so as long as it is in the money ie employees sometimes do not know when it is mathematically or financially optimal to keep the option open or to execute it hence they may exercise suboptimally when the stock price value exceeds a certain threshold multiple of the strike price However at expiration the option is executed if it is in the money regardless of the suboptimal multiple The Suboptimal Exercise Behavior Multiple is calculated from historical data and is calculated as the ratio of the stock price at which employees tend to exercise the option to the original stock price at grant date pre and post termination exercises are excluded As this value may differ from employee to employee using these historical trends one can perform Monte Carlo simulation using Crystal Ball on this exercise pattern Static Inputs Required
59. rk to compare the results for a simple European option without changing risk free rates and by using the average risk free rate in the series of changing rates Static Inputs Required Inputs Allowed to Change Over Time Stock price Risk free Rate Strike price Maturity Risk free Rate Dividend Yield Volatility To replicate this module use the ESO Function ESOCustomBinomialBasic When using this function set the Vesting and Forfeiture Rates to zero Suboptimal Exercise Behavior Multiple to 1000 and leave the Volatility Series variable empty 24 Customized Basic Option Vesting Suboptimal Behavior Forfeiture Changing Risk free and Volatility The Customized Basic Option is useful for calculating stock options that cannot be executed during the vesting period but after the vesting period the option will be executed if the future stock price exceeds the Suboptimal Exercise Behavior Multiple times the initial strike price if and only if the option is not forfeited If the option is forfeited the holder of the option will have to exercise it within some specified period if it is in the money regardless of the suboptimal exercise threshold If the option is at the money or out of the money it is allowed to expire worthless In addition volatilities and risk free rates can be changed over time to mirror more closely the changing business and economic environment Static Inputs Required Inputs Allowed to Change Over Time
60. rs Maturity in Years 5 00 European Option Results American Option Results 4 Volatility 35 0096 Binomial Approach 39 43 Binomial Approach 39 43 Initial Stock Price 100 00 Black Scholes Model 39 43 Black Scholes Model N A Risk free Rate 5 0096 Path Dependent Simulation 39 43 Path Dependent Simulation N A Dividend Rate 0 0096 Path Independent Simulation 39 43 Path Independent Simulation N A Strike Strike 100 00 Generalized Black Scholes 39 43 Path Dependent Simulation SI N A Closed Form Approximation Mode 39 43 m Simulation Calculatior Simulate Value 0 00 Binomial Steps 3000 Steps Y Binomial Steps 3000 Steps Payoff Function 19 47 Time Simulate Stepsize Value Time Simulate Stepsize Value Time Simulate Stepsize Value 0 22 09 0 00 100 00 21 0 00 0 26 105 38 42 0 00 0 28 111 06 1 0 00 0 25 100 25 22 0 00 0 26 105 65 43 0 00 0 28 111 33 2 0 00 0 25 100 50 23 0 00 0 26 105 91 44 0 00 0 28 111 61 3 0 00 0 25 100 75 24 0 00 0 26 106 18 45 0 00 0 28 111 89 4 0 00 0 25 101 00 25 0 00 0 27 106 44 46 0 00 0 28 112 17 5 0 00 0 25 101 26 26 0 00 0 27 106 71 47 0 00 0 28 112 45 6 0 00 0 25 101 51 27 0 00 0 27 106 97 48 0 00 0 28 11273 7 0 00 0 25 101 76 28 0 00 0 27 107 24 49 0 00 0 28 113 01 8 0 00 0 25 102 02 29 0 00 0 27 107 51 50 0 00 0 28 113 30 9 0 00 0 26 102 27 30 0 00 0 27 107 78 51 0 00 0 28 113 58 10 0 00 0 26 102 53 31 0 00 0 27 108 05 52 0 00 0 28 113 86 11 0 00 0 26 10
61. sset Cost 0 Intermediate Node Equation IF Stock gt Suboptimal Strike Max Stock Strike 0 Example MAX Asset Cost Intermediate Node Equation For Blackout Periods Example Super Lattice Option Valuation Results Enter the relevant inputs and click COMPUTE amp Create audit worksheets SE COMPUTE LOAD Figure 51 510 SLS Results of a Call Option accounting for Vesting and Suboptimal Behavior 44 EJReat Options Valuation Inc m n leg Custom Variables List Value 1 10 Variable Name Suboptimal UT Benchmark Black Scholes European Closed Form American Binomial European Binomial American Note The Binomial Models in above benchmarks assume a simple call put with 1 000 steps Common Commands MIN IF AND OR gt lt Common Variables Stock Future Stock Price Strike Steps Keeping Option Open erican pron wi es ing an ubop mal Behavior m Assumptions Intermediate Calculations Stock Price Stepping Time dt Strike Price Up Step Size up Maturity in Years Down Step Size down Risk free Rate 96 Risk neutral Probability prob Dividends 96 volatility 94 Results Suboptimal Exercise Multiple 10 61 70 Step Lattice Results Vesting in Years Generalized Black Scho
62. taining the right but not the obligation to buy a share of stock at a set strike price When the time comes to buy the stock or exercise your option you exercise the option if the prevailing stock price in the market is higher than the strike price of your option Exercising the option means purchasing the stock at the strike price and selling it at the higher market price to make a profit less any transaction costs and premiums paid to obtain the option However if the price is less than the strike price you don t buy the stock and your only losses are the transaction costs and premiums used to obtain the option if any The future is difficult to predict You cannot know for certain whether a specific stock will increase or decrease in value This is the beauty of options you can maximize your gains speculation with unlimited upside while minimizing your losses hedging against the downside by setting the maximum losses as the premium paid on the option The same idea can be applied to employee stock options A firm may provide employees incentives through the granting of stock options The difference here is that employees obtain these stock options for free from the employer The ESO Valuation software provides the mathematical and financial models to value these stock options for the purposes of expensing them per the 2004 proposed Financial Accounting Standards 123 revisions FAS 123 The ESO Valuation software is appropriate for fina
63. timal multiple times the strike price and is not forfeited all the while the risk free rates dividends forfeiture rates suboptimal behavior multiple and volatilities are changing over time Function ESOCustomBinomialCall 15 Marketability Discount with Vesting and Blackout Periods where all other inputs are changing over time Suboptimal Behavior Multiples Forfeiture Rates Risk Free Rates Dividend Yields and Volatilities This is an American modified barrier put option with a vesting period and changing suboptimal early exercise behavior multiples forfeiture rates risk free rates dividend yields and volatilities This model is used primarily for calculating the marketability discount of an ESO due to the fact that ESOs are non tradable and non marketable Function ESOCustomBinomialHaircut 50 16 Fully Customized Binomial Lattice Model Put Option with Vesting and Blackout Periods where all other inputs are changing over time Suboptimal Behavior Multiples Forfeiture Rates Risk Free Rates Dividend Yields and Volatilities This is an American put option with a vesting period where all other inputs are changing over time suboptimal early exercise behavior multiples forfeiture rates risk free rates dividend yields and volatilities Function ESOCustomBinomialPut 17 Black Scholes Call Option with Dividends This is the European call calculated using the modified Generalized Black Scholes model with an a
64. tion Automatic Example 1 2 10 20 15 Custom Equations Optional Terminal Node Equation Max Stock Strike 0 Example MAX Asset Cost 0 Intermediate Node Equation IF Stock gt Suboptimal Strike Max Stock Strike 0 Example MAX Asset Cost Intermediate Node Equation For Blackout Periods Benchmark Call Black Scholes European 39 94 0 59 qo Closed Form American 33 34 3 33 Super Lattice Option Valuation Results Binomial European 39 94 0 59 Custom Option 36 4209 amp Binomial American 39 94 3 45 Note The Binomial Models in above benchmarks assume a simple call put with 1 000 steps Create audit worksheets Brow Common Commands MAX MIN IF AND OR gt lt Common Variables Stock Future Stock Price Strike j LOAD SAVE EXIT Steps Keeping Option Open Figure SLS8 SLS Results of a Call Option accounting for Suboptimal Behavior 42 American Options with Suboptimal Exercise Behavior Assumptions Intermediate Calculations Stock Price Stepping Time at 1 0000 Strike Price 3 Up Step Size up 1 1052 Maturity in Years Down Step Size down 0 9048 Risk free Rate 96 Risk neutral Probability prob 73 0996 Dividends 96 Volatility 9 Results Suboptimal Exercise Multiple 10 Step Lattice Results 38 14 Generalized Black Scholes 39 94 n 100 Step Binomial
65. ts Option is useful for calculating simple American options that cannot be executed during the vesting period This option is essentially a mixed model that is a mix between a European Option during the vesting period where the option cannot be executed until its maturity in this case until the end of the vesting period and an American Option after the vesting period where the option can be executed at any time up to and including the option s maturity date The results illustrate a ten step binomial recombining lattice for an American call option with continuous dividends The first lattice is the underlying asset lattice where the starting asset value is simulated based on the Volatility and Number of Steps inputs The second lattice is the option valuation lattice Please note that the analysis presented here uses a ten step lattice for illustration purposes only For higher levels of precision use the Super Lattice routine The higher the number of lattice steps the higher the level of accuracy The results are calculated using the binomial lattice approach which can account for the vesting requirements In addition a closed form American approximation model and the Generalized Black Scholes model are included as benchmarks as these three values should be similar at the limit with enough steps in the binomial lattice when no dividends and no vesting requirements exist as these two latter models cannot account for the vesting requi
66. ty calculations for a predetermined ten step binomial lattice Figure 3 This section only exists for simple options For more complex options with exotic inputs and changing inputs the intermediate calculations are not shown Intermediate Calculations Stepping Time dt Up Step Size up Down Step Size down Risk neutral Probability prob Figure 3 Intermediate calculations The resulting ESO value calculated using the ten step binomial approach is shown in the Results section as seen in Figure 4 Results 10 Step Lattice Results Generalized Black Scholes 10 Step Super Lattice 12 09 Super Lattice Steps 10 Steps Figure 4 ESO valuation results The drop down box seen in Figure 4 beside the Super Lattice Steps provides the user with a choice to change the number of steps to perform using a binomial lattice For instance the greater the number of steps the more granular the lattice becomes and the higher the accuracy of the lattice results Manually creating a binomial lattice with 1 000 steps may take years to calculate as compared to less than a few seconds using the software To obtain more choices in number of steps use the ESO Functions instead 12 Make sure to hit the Calculate button or Ctrl R if you turned on Manual Calculate on the Main Index page after selecting the relevant steps for the Super Lattice In addition each module has an Analyze report button As an example Figure 5 s
67. ut is a positive value and may be allowed to change over time and typically range from 2 to 20 Dividend Rate or Yield in Percent This is the dividend rate or yield that is paid by the underlying stock For non dividend paying stocks leave it as zero The dividend yield is typically the total dividend payments computed as a percent of the stock price that is paid out over the course of a year A dividend rate will usually reduce the value of the call option as on the ex dividend date the stock price drops by approximately the dividend rate making the call option less valuable Another way to see this is that the holder of the option as opposed to the stock does not get the dividend payments whereas the stock holder does making the dividend payment an opportunity cost of holding the option reducing its value This input has to either be zero or a positive value and can be allowed to change over time and typically range from 0 to 10 Expiration or Maturity in Years This is the number of years available to exercise the option before it expires If the expiration is in months then it should be converted to a percentage of a year i e 3 months is 0 25 years This is also known as the maturity date of the option before it expires Sometimes an adjustment is made for the number of trading days for instance a 90 trading day expiration is the same as 90 250 or 0 36 years Do not confuse this with the vesting period which specifies the ti
68. ve benchmarks assume a simple call put with 1 000 steps I Create audit worksheets Browse Common Commands MIN IF AND OR gt lt Common Variables Stock Future Stock Price Strike COMPUTE LOAD SAVE EXIT Steps Keeping Option Open Figure SLS6 SLS Results of a Vesting Call Option 40 erican on es ng sou rements Assumptions AI Intermediate Calculations Stock Price 100 00 Stepping Time at Strike Price 100 00 Up Step Size up Maturity in Years Down Step Size down Risk Free Rate Risk Neutral Probability prob 39 69 Dividends 96 Volatility Results 70 Step Lattice Results Generalized Black Scholes American Closed Form Approx 100 Step Binomial Super Lattice Binomial Super Lattice Steps 100 Steps 10 Step Trinomial Super Lattice 44 95 Trinomial Super Lattice Steps 10Steps v 9007 71 545982 331155 331155 200855 1200855 Vesting in Years 5459 82 2008 55 Underlying Stock Price Lattice O i o 1 Vesting Calculation OS Ps aero querere aaepe 535982 535982 321155 321155 190855 190855 190855 1118 25 111825 111825 63891 63891 63891 __ 63891 ee NNNM 18320 181290 17674 17183 17183
69. ver time while the ESO Functions can accommodate an unlimited number of changing inputs To replicate this module use the ESO Function ESOCustomBinomialHaircut 27 Manual Custom Lattice The Manual Custom Lattice module is a powerful alternative to creating and solving binomial lattices Figure 9 The added advantages of using this module include e The ability to run Monte Carlo simulations e The ability to view the mathematical formulae in the lattices e The ability to audit and understand the results by following the formulas e The ability to link to and from other spreadsheets e The ability to customize and change the inputs over time To illustrate the power of the Manual Custom Lattice start the module and enter the following parameters for a simple option Only show formulae Stock Option Analysis Toolkit EJ Required Inputs Basic Option The Advanced Custom Lattice module is a powerful alternative to creating and solving binomial lattices The added advantages of using this module include Stock Price 100 Strike Price 100 The ability to run Monte Carlo simulations The ability to view the mathematical formulae in the lattices Volatility 5 10 E j i Customized Options The ability to audit and understand the results by following the formula logic Risk free The ability to link to and from other spreadsheets and Dividend 0 The ability to customize and change t
70. wnian Motion where a is a drift parameter o the volatility measure dZ amp X Adt dX T such that In EJ N u o or X and dX lognormally distributed If at time zero X 0 0 then the expected value of the process X at any time f is such that E X tr 2 X e and the variance of the process X at time f is V X 0 e 1 In the continuous case where there is a drift parameter the expected value then becomes X oera 1 dt X r ay 0 0 53 Appendix B Options Formulas Black and Scholes Option Model European Option This is the famous Nobel Prize winning Black Scholes model without any dividend payments It is the European option version where the option can only be executed at expiration and not before Although it is simple enough to use care should be taken in its input variable assumptions especially that of volatility which is usually difficult to estimate However the Black Scholes model is useful in generating ballpark estimates of the true ESO fair market value especially for more generic type calls and puts For more complex ESO valuations the customized binomial lattices are required Definitions of Variables S stock price at grant date X contractual strike price r risk free rate 96 T time to maturity or expiration years o annualized volatility 96 o cumulative standard normal distribution Computation 2 12 Call So In S X 7 0 2 Xe To In
71. you run a 1 000 step lattice with a vesting period of 1 399 it would take several hundred pages to print out a 1 000 step lattice and if the software only provides the first 10 steps then the value is zero if the vesting or blackout periods are included Hence a brand new 10 step lattice is created in the audit sheet using the relevant inputs the user provides while all blackout steps have been ignored The following are six examples of how the SLS can be used You can follow along by entering the values manually or starting SLS and Loading the relevant SLS files These SLS files are located in the directory where you installed the software Typically the installation directory is located at C Program Files Real Options Valuation ESO Valuation Although the simple examples illustrated next using the SLS are verified using the ESO Toolkit in reality more advanced problems and highly customized option types can only be solved using the SLS and not through the use of the ESO Toolkit 35 SLS Example I European Option A simple European call option is computed here using the SLS see Figure SLS2 The starting stock price is 100 and the strike price is 100 with a 10 year maturity The annualized risk free rate of return is 5 and the historical comparable or future expected annualized volatility is 50 A 1 000 step binomial lattice is run and the results indicate a value of 67 3046 equivalent to the benchmark values You can v

SOFTWARE MANUAL - Real Options Valuation, Inc.

Contents

Download Pdf Manuals

Related Search

Related Contents