MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

• What are call and put options?

• What is the difference between an American and a European option?

• As a function of the stock price on the exercise date, what do the payoffs look like for European calls and puts?

• What parameters determine the value of an option?

• How can I estimate the volatility of a stock based on historical data?

• How can I use Excel to implement the Black-Scholes formula?

• How do changes in key parameters change the value of a call or put option?

• How can I use the Black-Scholes formula to estimate a stock’s volatility?

• I don’t want somebody changing my neat option-pricing formulas. How can I protect the formulas in my worksheet so that nobody can change them?

• How can I use option pricing to help my company make better investment decisions?

During the early 1970s, economists Fischer Black, Myron Scholes, and Robert Merton derived the Black-Scholes option-pricing formula, which enables us to derive a value for a European call or put option. Scholes and Merton were awarded the 1997 Nobel Prize in Economics for their efforts. (Black died before 1997; Nobel prizes are not awarded posthumously.) The work of these economists revolutionized corporate finance. In this chapter, I’ll introduce you to their important work.

• What are call and put options?

• A call option gives the owner of the option the right to buy a share of stock for a price called the exercise price. A put option gives the owner of the option the right to sell a share of stock for the exercise price.

• What is the difference between an American and a European option?

• An American option can be exercised on or before a date known as the exercise date (often referred to as the expiration date). A European option can be exercised only on the exercise date.

• As a function of the stock price on the exercise date, what do the payoffs look like for European calls and puts?

• Let’s look at cash flows from a six-month European call option on shares of Microsoft with an exercise price of $110. Let P equal the price of Microsoft stock in six months. The payoff from a call option on these shares is $0 if P≤110 and P–110 if P>110. With a value of P below $110, we would not exercise the option. If P is larger than $110, we would exercise the option to buy stock for $110 and immediately sell the stock for P, thereby earning a profit of P–110. Figure 65-1 shows the payoff from this call option. In short, a call option pays $1 for every dollar by which the stock price exceeds the exercise price. The payoff for this call option can be written as Max(0,P–110). Notice that the call option graph in Figure 65-1 has a slope 0 for P smaller than the exercise price. Its slope is 1 for a value of P greater than the exercise price.

  Figure 65-1: Cash flows from a call option

• We can show that if a stock pays no dividends, it is never optimal to exercise an American call option early. Therefore, for a non-dividend-paying stock, an American and a European call option both have the same value.

• Now let’s look at cash flows from a six-month European put option on shares of Microsoft with an exercise price of $110. Let P equal the price of Microsoft in six months. The payoff from the put option is $0 if P≥110 and P–110 if P<110. For a value of P below $110, we would buy a share of stock for P and immediately sell the stock for $110. This yields a profit of 110–P. If P is larger than $110, it would not pay to buy the stock for P and sell it for $110, so we would not exercise our option to sell the stock for $110.

• Figure 65-2 displays the payoff from this put option. In short, a put option pays us $1 for each dollar by which the stock price is below the exercise price. A put payoff can be written as Max(0,110–P). Note that the slope of the put payoff is –1 for P less than the exercise price, and the slope of the put payoff is 0 for a value of P greater than the exercise price.

  Figure 65-2: Cash flows from a put option

• An American put option can be exercised early, so the cash flows from an American put option cannot be determined without knowledge of the stock price at the times before the expiration date.

• What parameters determine the value of an option?

• In their derivation of the Black-Scholes option-pricing model, Black, Scholes, and Merton showed that the value of a call or put option depends on the following parameters:

  • Current stock price.

  • Option’s exercise price.

  • Time (in years) until the option expires (referred to as the option’s duration).

  • Interest rate (per year on a compounded basis) on a risk-free investment (usually T-Bills) throughout the duration of the investment. This rate is called the risk-free rate. For example, if three-month T-Bills are paying 5 percent, the risk-free rate is computed as LN(1+0.05). (Calculating the logarithm transforms a simple interest rate into a compounded rate.) Compound interest simply means that at every instant, you are earning interest on your interest.

  • Annual rate (as a percentage of the stock price) at which dividends are paid. If a stock pays 2 percent of its value each year in dividends, the dividend rate is 0.02.

  • Stock volatility (measured on an annual basis). An annual volatility of, for example, 30 percent means that (approximately) the standard deviation of the annual percentage changes in the stock’s price is expected to be around 30 percent. During the Internet bubble of the late 1990s, the volatility of many Internet stocks exceeded 100 percent. I’ll show you two ways to estimate this important parameter.

• How can I estimate the volatility of a stock based on historical data?

• To estimate the volatility of a stock based on data about the stock’s monthly returns, we can proceed as follows:

  • Determine the monthly return on the stock for a period of several years.

  • Determine for each month LN(1+monthly return).

  • Determine the standard deviation of LN(1+monthly return). This calculation gives us the monthly volatility.

  • Multiply the monthly volatility by to convert monthly volatility to an annual volatility.

• This procedure is illustrated in the file Dellvol.xlsx, in which I estimate the annual volatility of Dell stock using monthly prices from the period August 1988 through May 2001. (See Figure 65-3, in which I’ve hidden several rows of data.)

  Figure 65-3: Computing the historical volatility for Dell

• Copying from cell C2 to C3:C154 the formula (B2–B3)/B3 computes each month’s return on Dell stock. Then copying from D2 to D3:D154 the formula 1+C2 computes for each month 1+month’s return. Next I compute LN(1+ month’s return) for each month by copying from E2 to E3:E154 the formula LN(D2), and I compute the monthly volatility in cell H3 with the formula STDEV(E2:E154). Finally I compute an estimate of Dell’s annual volatility with the formula SQRT(12)*H3. Dell’s annual volatility is estimated to be 57.8 percent.

• How can I use Excel to implement the Black-Scholes formula?

• To apply the Black-Scholes formula in Microsoft Office Excel 2007, we need input values for the following parameters:

  • S=Today’s stock price

  • t=Duration of the option (in years)

  • X=Exercise price

  • r=Annual risk-free rate (This rate is assumed to be continuously compounded.)

  • σ=Annual volatility of stock

  • y=Percentage of stock value paid annually in dividends

• Given these input values, the Black-Scholes price for a European call option can be computed as follows:

• Define

• and

• Then the call price C is given by

• Here, N(x) is the probability that a normal random variable with a mean of 0 and a σ equal to 1 is less than or equal to x. For example, N(–1)=.16,N(0)=.5, N(1)=.84, and N(1.96)=.975. A normal random variable with a mean of 0 and a standard deviation of 1 is called a standard normal. The cumulative normal probability can be computed in Excel with the NORMSDIST function. Entering NORMSDIST(x) returns the probability that a standard normal random variable is less than or equal to x. For example, entering the formula NORMSDIST(–1) in a cell will yield 0.16, which indicates that a normal random variable with a mean of 0 and a standard deviation of 1 has a 16 percent chance of assuming a value less than –1.

• The price of a European put P may be written as

• In the file named Bstemp.xlsx (see Figure 65-4), I’ve created a template that computes the value for a European call or put option. Enter the parameter values in B5:B10 and read the value of a European call in D13 and a European put in D14.

  Figure 65-4: Valuing European calls and puts

  Note

  Valuing American options is beyond the scope of this book. Interested readers should refer to Luenberger’s excellent textbook.

• As an example, suppose that Cisco stock sells for $20 today and that we’ve been issued a seven-year European call option. Assume that the annual volatility of Cisco stock is 50 percent, and the risk-free rate during the seven-year period is estimated at 5 percent per year. Compounded, this translates to LN(1+.05)=.04879. Cisco does not pay dividends, so the annual dividend rate is 0. We find the value of the call option to be $10.64. A seven-year put option with an exercise price of $24 would be worth $7.69.

• How do changes in key parameters change the value of a call or put option?

• In general, the effect of changing an input parameter on the value of a call or put is given in the following table:

  Open table as spreadsheet

  Parameter	European call	European put	American call	American put
  Stock price	+	-	+	-
  Exercise price	-	+	-	+
  Time to expiration	?	?	+	+
  Volatility	+	+	+	+
  Risk-free rate	+	-	+	-
  Dividends	-	+	-	+

  • An increase in today’s stock price always increases the value of a call and decreases the value of a put.

  • An increase in the exercise price always increases the value of a put and decreases the value of a call.

  • An increase in the duration of an option always increases the value of an American option. In the presence of dividends, an increase in the duration of an option can either increase or decrease the value of a European option.

  • An increase in volatility always increases option value.

  • An increase in the risk-free rate increases the value of a call because higher rates tend to increase the growth rate of the stock price (which is good for the call). This situation more than cancels out the fact that the option payoff is worth less as a result of the higher interest rate. An increase in the risk-free rate always decreases the value of a put because the higher growth rate of the stock tends to hurt the put, as does the fact that future payoffs from the put are worth less. Again, this assumes that interest rates do not affect current stock prices, but they do.

  • Dividends tend to reduce the growth rate of a stock price, so increased dividends reduce the value of a call and increase the value of a put.

• Using one-way and two-way data tables (see Chapter 15, “Sensitivity Analysis with Data Tables,” for details about how to work with data tables), we can, if we want, explore the specific effects of parameter changes on the value of calls and puts.

• How can I use the Black-Scholes formula to estimate a stock’s volatility?

• Earlier in this chapter, I showed how to use historical data to estimate a stock’s annual volatility. The problem with a historical volatility estimate is that the analysis looks backward. What we really want is an estimate of a stock’s volatility looking forward. The implied volatility approach simply estimates a stock’s volatility as the volatility value that will make the Black-Scholes price match the option’s market price. In short, implied volatility extracts the volatility value implied by the option’s market price.

• We can easily use the Goal Seek command and our input parameters to compute an implied volatility. On July 22, 2003, Cisco was selling for $18.43. An October 2003 call option with a $17.50 exercise price was selling for $1.85. This option expires on October 18 (89 days in the future). Thus, the option has a duration of 89/365=0.2438 years. Cisco does not expect to pay dividends, and we assume a T-Bill rate of 5 percent and a corresponding risk-free rate of LN(1+.05)=0.04879. To determine the volatility for Cisco implied by this option price, we enter the relevant parameters in cells B5:B10 of the file Ciscoimpvol.xlsx, which is shown in Figure 65-5.

  Figure 65-5: Using implied volatility to estimate Cisco’s volatility.

• Next we use Goal Seek (see Figure 65-6) to determine the volatility (the value in cell B10) that makes the call price (the formula in D13) hit a value of $1.85.

  Figure 65-6: Goal Seek settings to find implied volatility

• We find that this option implies an annual volatility for Cisco of 34 percent, as you can see in Figure 65-5.

  Note

  The Web site at http://www.Ivolatility.com provides an estimate of the volatility of any stock, either historical or implied.

• I don’t want somebody changing my neat option-pricing formulas. How can I protect the formulas in my worksheet so that nobody can change them?

• I’m sure that you have often sent your worksheets to people who then change your carefully constructed formulas. Sometimes you want to protect your worksheets so that another user can only enter input data but not modify the worksheet’s formulas. As an example, I’ll show you how to protect all the formulas in our Black-Scholes template (see the file Bstemp.xlsx).

• We begin by unlocking all of the cells in the worksheet. Then we will lock the cells we want to “protect.” First click the gray box in the upper-left corner of the worksheet where the row and column headings intersect (next to the A and the 1). When you click this box, any format changes you make will affect the entire worksheet. For example, if you select a bold format after clicking this box, all of the cells in the worksheet will use the bold format.

• After selecting the entire worksheet, click the Font dialog box launcher (the small arrow) on the Home tab. This displays the Format Cells dialog box, shown in Figure 65-7. On the Protection tab, clear the Locked box as shown in Figure 65-7, and then click OK. Now all cells in the worksheet are unlocked, which means that even if the worksheet is protected, we can still access these cells.

  Figure 65-7: Format Cells dialog box

• Next select all the formulas in the worksheet. To do this, press F5, which opens the Go To dialog box. Click Special, select Formulas, and click OK. Click the Font dialog box launcher again, and on the Protection tab, check the Locked box. Checking this box “locks” all our formulas.

• Now we can protect our worksheet, which will prevent a user from changing our formulas. Click Protect Sheet in the Changes group on the Review tab. In the Protect Sheet dia-log box, check the Select Unlocked Cells box, as shown in Figure 65-8. This option will allow users of our template to select unlocked cells, but our formulas will be off-limits.

  Figure 65-8: Allowing user to access unlocked cells

• Now, when you click any formula, you cannot see or change its contents. Go ahead and try to mess up a formula! The final result of protecting this workbook is saved in the file Bstempprotected.xlsx.

• How can I use option pricing to help my company make better investment decisions?

• Option pricing can be used to improve a company’s capital budgeting or financial decision-making process. The use of option pricing to evaluate actual investment projects is called real options. The idea of real options is credited to Judy Lewent, the chief financial officer of Merck. Essentially, real options let you put an explicit value on managerial flexibility, which is often missed by traditional capital budgeting. The following two examples illustrate the concepts of real options.

  Note

  Refer to Luenberger’s book for a more detailed discussion of real options.

• Let’s say that we own an oil well. Today, our best guess is that the oil in the well is worth $50 million. In five years (if we own the well), we will make a decision to develop the oil well, at a cost of $70 million. A wildcatter is willing to buy the well today for $10 million. Should we sell the well?

• Traditional capital budgeting says that the well is worthless because the cost to develop it is more than the value of the oil in the well. But wait; in five years time, the value of the oil in the well will be different because many things (such as the global oil price) might change. There’s a chance that the oil will be worth at least $70 million in five years. If the oil is worth $80 million in five years, developing the well in five years would return $10 million.

• Essentially, we own a five-year European call option on this well, because our payoff from the well in five years is the same as the payoff on a European call option with a stock price of $50 million, an exercise price of $70 million, and a duration of five years. We can assume an annual volatility similar to the volatility of a typical oil company stock (for example, 30 percent). We will use a T-Bill rate of 5 percent, corresponding to a risk-free rate of 4.879 percent. In the file Oilwell.xlsx (see Figure 65-9), we find the value of this call option is $11.47 million, which means that we should not sell the well for $10 million.

  Figure 65-9: Oil well real options

• Of course, we do not know the actual volatility for this oil well. Therefore, we can use a one-way data table to determine how the value of the option depends on our volatility estimate (see Figure 65-9). From the data table, we find that as long as the oil well’s volatility is at least 27 percent, our oil well “option” is worth more than $10 million.

• As a second example, consider a biotech drug company that is developing a drug for a major pharmaceutical firm. The biotech company currently believes the value of the drug is $50 million. Of course, the value of the drug might drop over time. To protect against a price drop, the biotech company wants to receive a guaranteed payment of $50 million in five years. If an insurance company wants to underwrite this liability, what is a fair price to charge?

• Essentially, the biotech company is asking for a payment of $1 million in five years for each $1 million by which the value of the drug in five years is below $50 million. This is equivalent to a five-year put option on the value of the drug. Assuming a T-Bill rate is 5 percent and the annual volatility on comparable drug stocks is 40 percent (see the file Drugabandon.xlsx, shown in Figure 65-10), the value of this option is $10.51 million. This type of option is often referred to as an abandonment option, but it is equivalent to a put option. (We have also included a one- way data table to show how the value of the abandonment option depends on the assumed volatility, ranging from 30 to 45 percent of the drug’s value.)

  Figure 65-10: Calculating an abandonment option