Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE Chapter 30, Data tables page 14
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
ABC D EFGHI
Discount rate 15%
Year
Project
A
cash flow
Project B
cash flow
0 -1,000 -1,000
1220300
2220300
3220300
4220300
5220300
6220100
7220100
8220100
9220100
10 220 100
NPV 104.13 172.31 <-- =NPV($B$2,C6:C15)+C5
IRR 17.68% 20.64% <-- =IRR(C5:C15)
NPV
A
NPV B
<-- The data table headers have been hidden; see Chapter 30 for details
0% 1,200.00 1,000.00
2% 976.17 840.95
4% 784.40 701.45
6% 619.22 578.48
8% 476.22 469.55
10% 351.80 372.61
12% 243.05 285.98
14% 147.55 208.23
16% 63.31 138.18
18% -11.30 74.84
20% -77.66 17.37
22% -136.90 -34.95
24% -189.99 -82.74
26% -237.74 -126.51
28% -280.84 -166.71
30% -319.86 -203.73
TWO INVESTMENTS AND THEIR NPVs
-400.00
-200.00
0.00
200.00
400.00
600.00
800.00
1,000.00
1,200.00
1,400.00
-3% 2% 7% 12% 17% 22%
NPV A
NPV B
4. Finance texts always have tables which give the present value factor for an annuity:
()
1
1
$1
1
N
t
t
PV factor for annuity of for N years
r
=
=
+
∑
.
As illustrated below in Excel these present value factors are created with the
PV function:
PFE Chapter 30, Data tables page 15
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
ABCDEFGHIJK
r9%
T5
PV factor 3.8897 <-- =PV(B3,B4,-1)
Number of
periods
PRESENT VALUE OF AN ANNUITY OF $1 FOR N PERIODS
1% 2% 3% 4% 5% 6% 7% 8% 9% 10%
1
2
3
4
5
6
7
8
9
10
ANNUITY TABLE
Use
Data Table to create the table in the template above.
5. (Do this example only if you’ve studied Chapter 24 on option pricing.) The Black-Scholes
option pricing model, defined in Chapter 24, prices call and put options based on 5 parameters:
•
S, the stock price today
•
X, the option’s exercise price (also called the option’s strike price)
•
T, the option’s expiration date
•
r, the interest rate
•
σ
(“Sigma”), the riskiness of the stock
These inputs and the resulting call and put prices are highlighted below.
Your assignment
: Use Data Table to create tables showing the sensitivity of the call and
put prices to the various inputs. Here are some suggestions:
a. Using the parameters shown below, what are the call and put prices given
σ
= 10%,
15%, 20%, … , 80%?
PFE Chapter 30, Data tables page 16
b. Using the parameters shown below, what are the call and put prices when T = 0.1, 0.2,
0.3, … , 1?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
AB C
The Black-Scholes Option-Pricing Formula
S 100 Current stock price
X 90 Exercise price
T 0.50000 Time to maturity of option (in years)
r 4.00% Risk-free rate of interest
Sigma 35% Stock volatility
d
1
0.6303 <-- (LN(S/X)+(r+0.5*sigma^2)*T)/(sigma*SQRT(T))
d
2
0.3828
<-- d
1
-sigma*SQRT(T)
N(d
1
)
0.7357
<-- Uses formula NormSDist(d
1
)
N(d
2
)
0.6491
<-- Uses formula NormSDist(d
2
)
Call price 16.32
<-- S*N(d
1
)-X*exp(-r*T)*N(d
2
)
Put price 4.53 <-- call price - S + X*Exp(-r*T): by Put-Call parity
PFE Chapter 32, Goal Seek and Solver page 1
CHAPTER 32: USING GOAL SEEK AND SOLVER
*
slight bug fix: July 12, 2003
Chapter contents
Introduction..................................................................................................................................... 1
32.1. Installing Solver.................................................................................................................... 2
32.2. Using Goal Seek and Solver: a simple example .................................................................. 4
32.3. What’s the difference between Solver and Goal Seek? ........................................................ 7
EXERCISES ................................................................................................................................... 9
Introduction
Goal Seek and Solver are Excel tools that to produce targeted results from your models
(the technical jargon is “calibrate your model”). If this sentence sounds a bit dense, read on—
you’ll see that these tools are extremely useful.
Although Solver is a much more sophisticated tool than Goal Seek, we won’t use many
of its more advanced capabilities. For our purposes, Goal Seek and Solver are thus largely
interchangeable—they can both do most of the financial tasks that we require, and they are not
*
Notice: This is a preliminary draft of a chapter of Principles of Finance with Excel 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 32, Goal Seek and Solver page 2
difficult to use. When you get used to them, you’ll probably find that Solver is preferable,
because it “remembers” its arguments (at this stage you won’t understand this, but read on).
32.1. Installing Solver
Both of these tools come with the standard Excel package, but Solver has to be installed.
If it is not on your computer, do the following:
Open Excel and go to Tools|AddIns:
PFE Chapter 32, Goal Seek and Solver page 3
After clicking AddIns, you’ll get a drop-down menu; scroll down to Solver Add-in and click the
box. That should do it.
PFE Chapter 32, Goal Seek and Solver page 4
32.2. Using Goal Seek and Solver: a simple example
We’ll start with a high-school algebra example: Suppose we’re trying to graph the
equation
32
23121yx x x=+ −+ . We can do this in Excel as follows:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
AB C DEF G
x 5.166147
y 21.00001 <-- =-B2^3+2*B2^2-3*B2+121
xy
-9 1039 <-- =-E4^3+2*E4^2-3*E4+121
-8 785 <-- =-E5^3+2*E5^2-3*E5+121
-7 583 <-- =-E6^3+2*E6^2-3*E6+121
-6 427 <-- =-E7^3+2*E7^2-3*E7+121
-5 311
-4 229
-3 175
-2 143
-1 127
0121
1119
2115
3103
477
531
6-41
7-145
8-287
9-473
10 -709
11 -1001
Table
SIMPLE EXAMPLE
-1500
-1000
-500
0
500
1000
1500
-10 -5 0 5 10 15
PFE Chapter 32, Goal Seek and Solver page 5
Notice that we’ve put the function in twice: In cells B2:B3, we’ve got a simple example
of the function (one value of
x and its corresponding y value); in the table to the right, we’ve got
the table for the graph (many values of
x and many values of y).
Now we want to find the
x such that the corresponding y is 21. You can tell from the
table that the value will be somewhere between 5 and 6. To solve for it, we go to the Excel
command
Tools|Goal Seek. This brings up a dialogue box, which we fill in as below:
Hitting the
OK box indicates that the answer is approximately 5.150067:
Hitting
OK again, accepts this answer:
1
2
3
AB C
x 5.166147
y 21.00001 <-- =-B2^3+2*B2^2-3*B2+121
SIMPLE EXAMPLE
PFE Chapter 32, Goal Seek and Solver page 6
Doing the same thing with Solver
We can do the same calculation with Solver. On the same spreadsheet, we go to the
command
Tools|Solver. This brings up a dialogue box which we fill in as follows (note that we
changed the question a bit—this time we’re asking for the
x value which gives a y = -58):
Hitting
Solve gives the answer:
Hitting
OK accepts the answer.
PFE Chapter 32, Goal Seek and Solver page 7
32.3. What’s the difference between Solver and Goal Seek?
Solver and Goal Seek serve much the same purpose. Nevertheless, there are several
differences between them.
Solver remembers, Goal Seek forgets
Suppose you’ve got another question: For which
x will y=158? If you use Goal Seek to
answer this question, you’ll have to re-enter all the values into the dialogue box. But if you use
Solver, you’ll see that it comes up with the previous set of values—you only have to change the
entry into the
Value of box:
This “memory” of
Solver carries over even if you save the file and reopen it later.