A Tutorial on Using EXCEL and EXCEL Add-ins to Value Real Options By ...

A Tutorial on Using EXCEL and EXCEL Add-ins to Value Real Options

April 15, 1999

By Wayne L. Winston Professor of Decision Sciences Kelley School of Business Bloomington IN, 47405

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

2

shares of stock and short 1 call we will show that in both Good and Bad states this portfolio yields $4.50.

State Good Bad

Portfolio Value .25($22) ?(1)($1) = $4.50. .25($18) ?(1)($0) = $4.50.

Note that in the Good state the option is worth $1 because we can exercise the option and buy the stock for $20 and immediately sell the stock for $21. In the Bad state the option is worthless because there is no benefit to be gained by buying the stock for $20 when the current price is $18!

Since this portfolio yields $4.50 for certain three months from now its value today must equal the NPV (discounted at the risk-free rate) of $4.50 received three months from now. This is just

4.5e-.25(.12) =$4.37. This implies that

.25(today's stock price) ? (today's option price) = $4.37 or

5 ? (today's option price) = $4.37 or

(today's option price) = $0.63.

This pricing approach is called arbitrage pricing. The argument works independent of a person's risk preferences. Therefore we may use the following approach to price derivatives;

1. In a world where everyone is risk neutral arbitrage pricing is valid. 2. In a risk neutral world all assets must grow on average at risk free rate. 3. In a risk neutral world any asset (including an option) is worth the expected value of

its discounted cash flows. 4. Set up a risk neutral world in which all stocks grow at risk free rate and use @ RISK

(or binomial tree; see Chapters 6-58) to determine expected discounted cash flows from option. 5. Since the arbitrage pricing method gives the correct price in all worlds it yields the correct price in a risk neutral world. Therefore if we use the above method to find a derivative's price in a risk neutral world we have found the right price for our complicated non risk-neutral world!

It is important to note that actual growth rate of a stock is irrelevant to pricing a derivative. Information about a stock's future growth rate is imbedded in today's stock price.

3

Example of Risk Neutral Approach Let's find the call option value of $0.63 using the risk neutral approach. Let p be probability (in the risk neutral world) that in 3 months stock price is $22. Then 1 ? p is probability that stock price 3 months from now is $18. If stock grows on average at risk free rate we must have p($22) + (1-p)($18) = 20* e(.25)(.12)= $20.61 or

4p = 2.61

or

p = .65.

Now value option as expected discounted value of its cash flows: e-.25(.12)(.65($1) + .35($0)) = $0.63.

For further discussion of the risk neutral approach we refer the reader to Hull (1997).

Section 3-The Lognormal Model of Stock Prices

The Lognormal model for asset value (or stock price) assumes that in a small time t the stock price changes by an amount that is normally distributed with

S tan dard Deviation = S t Mean = ?St Here S = current stock price.

? may be thought of as the instantaneous rate of return on the stock. By the way, this model leads to really "jumpy" changes in stock prices (like real life). This is because during a small period of time the standard deviation of the stock's movement will greatly exceed the mean of a stock's movement. This follows because for small t , t will be much larger than t.

In a small time t the natural logarithm (Ln (S)) of the current stock price will change (by Ito's Lemma, see Hull (1997)) by an amount that is normally distributed with Mean = (? - .52)t

S tan dard Deviation = t

4

Let St = stock price at time t. In Chapter 11 of Hull (1997) it is shown that at time t Ln St is normally distributed with

Mean = Ln S0 + (? - .52) t and

S tan dard Deviation = t

We refer to (? - .52) as the continuously compounded rate of return on the stock. Note the continuously compounded rate of return on S is less than instantaneous return. Since Ln St follows a normal random variable we say that St is a Lognormal random variable.

To simulate St we get Ln (St) by entering in @RISK the formula

= LN (S0 ) + (? - .5 2 )t + t RISKNORMAL(0,1) Therefore to get St we must take the antilog of this equation and get

St

=

S e( ? -.5 2 )t + 0

tRISKNORMAL(0,1) (2.1).

Risk Neutral Valuation

To apply the risk neutral valuation approach of Section 2 we assume the asset grows at instantaneous rate r. Then the value of a derivative is simply the expected discounted (at risk free rate) value of cash flows. We will often apply this approach. We can estimate volatility by implied volatility (see Section 3) or historical volatility (see below)

Historical Estimation of Mean and Volatility of Stock Return If we average values of

Ln St St -1

we obtain an estimate of (? - .52). If we take the standard deviation of

Ln St St -1

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download