Free PDF eBook | FileMount.com

More PDF Content

A Tutorial on Using EXCEL and EXCEL Add-ins to Value Real Options By Wayne L. Winston Professor of Decision Sciences
Page 1
A Tutorial on Using EXCEL and EXCEL Add-ins to Value Real Options By Wayne L. Winston Professor of Decision Sciences Kelley School of Business Bloomington IN, 47405 April 15, 1999
Page 2
2 Section 1-Introduction EXCEL and EXCEL add-ins can greatly simplify the solution of many real option problems. We will give several examples of how EXCEL and the add-ins @RISK (a simulation add-in) and RISKOPTIMIZER (a simulation and optimization add-in) can be used to quickly and easily solve many real options problems. We then conclude with a new, easily implementable approach to option pricing that does not require the underlying asset to be a Lognormal random variable. In Section 2 we begin we a brief discussion of the risk neutral valuation approach. In Section 3 we introduce the Lognormal random variable and show how Lognormal parameters can easily be estimated from historical data or implied volatility. We also show how to use @RISK to simulate a Lognormal random variable. Section 4 illustrates the use of simulation to price a European put (the equivalent of an abandonment option). Section 5 shows how the simulation approach simplifies the valuation of many European real options such as expansion, contraction and abandonment that are discussed in Trigerorgis (1996). Section 6 shows how to price American options with Excel. Optimal exercise points are highlighted using EXCEL’s Conditional Formatting Feature. Section 6 also shows how simulation can be used to obtain the distribution of cash flows for an American option. Sections 7 and 8 show how to use the EXCEL-add in RISKOPTIMIZER to price more complex American options, including an option to start up and close a gold mine. In Section 9 we show how RISKOPTIMIZER can be used to model the decision to enter a new market. Basically, this example shows how a decision tree involving continuous random variables such as market share and market growth may be modeled via simulation. The material in Sections 2-9 has appeared in Winston (1998) and Winston (1999). In Section 10 we show the ideas of Luenberger (1997) can be extended to easily price financial and real options for which the underlying asset follows any distribution. In particular, the distribution of the underlying asset may be obtained from historical data or from simulation. In Section 11 we show how to value an option to cancel an order for airplanes. Section 2-The Risk Neutral Approach to Option Pricing A European option on a stock gives the owner of the option the right to buy (if the option is a call option) or sell (if the option is a put option) one share of stock for a particular price (the exercise price) on a particular date (the exercise date). An American option allows you buy or sell the stock at any date between the present and the exercise date. Options are usually priced by arbitrage arguments. For example, suppose 3 months from now a stock will sell for either $18 (bad state) or $22 (good state). The stock currently sells for $20 and we own a 3-month European call option with an exercise price of $21. The risk free rate is 12% per year. Three months from now the option is worth $1 (in good state) or $0 (in bad state). If we create a portfolio that is long .25
Page 3
3 shares of stock and short 1 call we will show that in both Good and Bad states this portfolio yields $4.50. State Portfolio Value Good .25($22) –(1)($1) = $4.50. Bad .25($18) –(1)($0) = $4.50.