Principles of Finance with Excel (Основы финансов c Excel)

Подождите немного. Документ загружается.

PFE Chapter 25, The Black-Scholes formula page 30

Exercises

1. Use the Black-Scholes model to price the following:

•

A call option on a stock whose current price is 50, with exercise price X = 50, T = 0.5, r

= 10%,

= 25%.

•

A put option with the same parameters.

2. A call option on a stock is priced at $5.35. The option has an exercise price of X = $40. The

current stock price S

= $33, the option’s time to maturity is 6 months, and the interest rate r =

6%. Use the Black-Scholes model to determine the implied volatility, the

used to price the

option. (Excel hint: use

Goal Seek.)

3. A put option on a stock is priced at $5. The option has an exercise price of X = $25. The

stock’s current price is S

= $25, the option’s time to maturity is 1 year, and the interest rate is r

= 5%. Use the Black-Scholes model to determine the option’s implied volatility, the

used to

price the option. (Excel hint: use

Solver)

4. A call option with ½ year to maturity is written on a stock whose current price is $40. The

option’s exercise price is $38, the interest rate is 4%, and the stock’s volatility is 30%.

4.a. Find the call option price using the Black-Scholes model.

4.b. Make a table showing the option’s price for volatilities ranging from 10%, 20%, …,

60%. (Excel hint: by far the easiest way to do this is to use

Data Table, explained in

Chapter 28.)

PFE Chapter 25, The Black-Scholes formula page 31

5. A put option with ½ year to maturity is written on a stock whose current price is $40. The

option’s exercise price is $38, the interest rate is 4%, and the stock’s volatility is 30%.

5.a. Find the put option price using the Black-Scholes model.

5.b. Make a table showing the option’s price for maturities ranging from T = 0.2, 0.4, …,

2.0. (Excel hint: by far the easiest way to do this is to use

Data Table, explained in

Chapter 28.)

6. Use the data from exercise 1 and

Data|Table to produce graphs that show:

• The sensitivity of the Black-Scholes call price to changes in the initial stock price S

•

The sensitivity of the Black-Scholes put price to changes in

•

The sensitivity of the Black-Scholes call price to changes in the time to maturity T.

•

The sensitivity of the Black-Scholes call price to changes in the interest rate r.

•

The sensitivity of the put price to changes in the exercise price X.

7. Produce a graph comparing a call’s intrinsic value (defined as Max(S

- X,0) ) and its Black-

Scholes price. From this graph you should be able to deduce that it is never optimal to exercise

early a call priced by the Black-Scholes.

8. Produce a graph comparing a put’s intrinsic value (= Max(X-S

,0) ) and its Black-Scholes

price. From this graph you should be able to deduce that it is may be optimal to exercise early a

put priced by the Black-Scholes formula.

PFE Chapter 25, The Black-Scholes formula page 32

9. Use the Excel Solver to find the stock price for which there is the maximum difference

between the Black-Scholes call option price and the option’s intrinsic value. Use the following

values: S

= 45, X = 45, T = 1,

= 40%, r = 8%.

10. Repeat the MSFT exercise in the text for the March 2002 options:

Note that you can use the Black-Scholes formula to calculate the call option premium as a

percentage of the exercise price in terms of S

/X:

() () () ()

()

01 2 1 2

ln( / ) 2

rT rT

C SNd Xe Nd Nd e Nd

where

SX r T

dd T

−−

=− ⇒= −

=−

Implement this in a spreadsheet.

Note that you can also calculate the Black-Scholes put option premium as a percentage of the

exercise price in terms of S

/X:

PFE Chapter 25, The Black-Scholes formula page 33

() () () ()

()

01 2 2 1

ln( / ) 2

rT rT

P SNd XeNd eNd Nd

where

SX r T

dd T

−−

=− − + − ⇒ = − − −

=−

Implement this in a spreadsheet. Find the ratio of S

/X for which C/X and P/X cross when T =

0.5, σ = 25%, r = 10%. (You can use a graph or you can use Excel’s Solver.) Note that this

crossing point is affected by the interest rate and the option maturity, but not by σ.

11. As Shown in Chapter 21 the call option value is always greater than its immediate exercise

value (S

-K) for S

>K. However, the value of the European put is sometimes less that its

intrinsic value (K- S

) for S

< K. Use the put option pricing model to find such an example.

12. The probability that a European call option on the stock will be exercised is N(d

) (same

expression as in Black-Scholes option pricing formula). What is the probability that a European

call option on a stock with an exercise price of $40 and a maturity date in six months will be

exercised? The current stock price is at $38, the interest rate is at 5%, stock return volatility is at

25%.

13. A stock price is currently $50 and the risk-free interest rate is 5%. Use the Black-Scholes

model to translate the following table of European call options on the stock into a table of

implied volatilities, assuming no dividends (Excel hint: use

Solver)

Are the option prices consistent with Black-Scholes?

PFE Chapter 25, The Black-Scholes formula page 34

Exercise Price($)/Maturity (months)

EXERCISE

PRICE($)/MATURITY

(MONTHS)

3 6 9

7 8.3 10.5

3.7 5.2 7.5

1.6 2 .9 5 .1

14. A put option with 1 year to maturity is written on a stock. The current underlying stock

price is $20. The option’s exercise price is $18, the interest rate is 3.74%, and the stock’s

volatility is 32.7%. The price of a call option written on the same stock with the same exercise

price and time to maturity is $4.3 Use Black and Scholes model to determine: Does put-call

parity hold?

15. The stock price of ABC-Corp is currently S

= $50. What is the price of a European call

option which expires in 2 months and which has a exercise price of $60? Assume the yearly

interest rate is 5.5%, and the monthly volatility of the stock prices is 7.8%.

16. The price of a share of ABC-Corp stock is currently S

= $55. Assume that the yearly

interest 2%, and that the stock’s volatility is 0.4.

16.a. Determine the prices of European call and put options with a exercise price of $55,

and expiration in three months.

16.a. Verify put-call parity.

17. A one month European call option is currently selling for $3.90. The exercise price of the

option is $40, and the current stock price is S

= $43. The monthly interest rate is 0.5% and the

PFE Chapter 25, The Black-Scholes formula page 35

monthly volatility of the stock return is at 7%. Does this price present an opportunity for

arbitrage, according to B&S?

18. Consider an option trading on a stock with a year to maturity. The implied volatility of the

option at the opening is 25% and at closing 22%. Assume that the stock prices hasn’t changed,

what do you conclude about the price; has it increased or decreased?

19. If the volatility of a stock is 30% and assuming 250 trading days a year, what is the standard

deviation of the return in one trading day?

PFE Chapter 27, Excel introduction page 1

CHAPTER 27: INTRODUCTION TO EXCEL

this version: March 2003

Chapter contents

Introduction..................................................................................................................................... 1

27.1. Getting started....................................................................................................................... 2

27.2. Formatting the numbers ........................................................................................................ 7

27.3. Absolute copying—building a more sophisticated model.................................................... 9

27.4. Saving the spreadsheet........................................................................................................ 16

27.5. Your first Excel graph......................................................................................................... 19

27.6. Initial settings...................................................................................................................... 22

27.7. Using a function.................................................................................................................. 25

27.8. Printing................................................................................................................................ 29

Exercises ....................................................................................................................................... 32

Introduction

This chapter introduces you to Excel and shows you how to do the most important initial

operations. Excel is not difficult to learn to use, provided you’re willing make many mistakes

along the way, and you take an occasional look at the online Help (press function key F1).

Notice: This is a preliminary draft of a chapter of Principles of Finance by Simon Benninga

(benninga@wharton.upenn.edu

). Check with the author before distributing this draft (though

you will probably get permission). Make sure the material is updated before distributing it. All

the material is copyright and the rights belong to the author.

PFE Chapter 27, Excel introduction page 2

Contents of this chapter

• Turning Excel on

• Saving, creating a new directory

• Copying—relative versus absolute

• Formatting numbers

• Making a graph

• Fiddling with the default settings for Excel

• Using a few functions

• Printing

27.1. Getting started

Ok, you’ve started your computer and pressed on the Excel icon on your desktop (or

maybe it’s not on your desktop—maybe you got there through the button ... ). You’re

facing a blank spreadsheet, and you want to play. Let’s write a spreadsheet describing how

$1000 deposited in the bank at 15% will grow over time:

AB C

Year Bank balance

0 1000

COMPOUND INTEREST

PFE Chapter 27, Excel introduction page 3

After you’ve finished typing in the above, put the cursor in cell B4. We’re going to make

a formula that describes how much money will be in the bank at the end of year 1. When you’re

in cell B4, type in the following formula and then hit [Enter] (no spaces, please!):

=B3*(1+15%)

Here’s what the spreadsheet should look like:

AB C

Year Bank balance

0 1000

1 1150

COMPOUND INTEREST

If you put the cursor on cell B4 and look at the formula bar (next to the f

symbol), you’ll

see what you’ve written in the spreadsheet:

PFE Chapter 27, Excel introduction page 4

Copying the formula

So, if you deposit $1,000 in the bank today and the bank gives you 15% interest, you’ll

have $1,150 at the end of year 1. If you’ve read Chapter 1 of this book, you know that at the end

of year 2 you’ll have

1,150*(1 15%)+ in the bank. Instead of typing in this formula, we’ll use

Excel’s copy ability to put it in cell B5:

• The lower right-hand corner of the frame around cell B4 has a little black square; we call

this the “handle” of the cell.

• Put the cursor on the handle of cell B4. Press on the left mouse button, and drag down

until you get to cell B13. At this point your spreadsheet will look like this: