Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE Chapter 30, Data tables page 11
30.4. Two dimensional data tables
In the example below we return to the FV example discussed above. We want to vary
our initial example with respect to both the interest rate and the initial deposit. The data table is
set up in cells B9:H15:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ABCDEFGHI
Interest rate 15%
Annual deposit 100
Years 10
Future value $2,334.93 <-- =FV(B2,B4,-B3,,1)
Two-dimensional table, showing sensitivity of future value to both interest rate and deposit size
$2,334.93 0% 5% 10% 15% 20% 25%
50
100
150
200
250
300
DATA TABLE EXAMPLE
=B5
This time we indicate in the Data|Table command that there are two variables:
This creates a two-dimensional table:
PFE Chapter 30, Data tables page 12
9
10
11
12
13
14
15
BCDEFGH
$2,334.93 0% 5% 10% 15% 20% 25%
50 500.00 660.34 876.56 1,167.46 1,557.52 2,078.31
100 1,000.00 1,320.68 1,753.12 2,334.93 3,115.04 4,156.61
150 1,500.00 1,981.02 2,629.68 3,502.39 4,672.56 6,234.92
200 2,000.00 2,641.36 3,506.23 4,669.86 6,230.08 8,313.23
250 2,500.00 3,301.70 4,382.79 5,837.32 7,787.60 10,391.53
300 3,000.00 3,962.04 5,259.35 7,004.78 9,345.13 12,469.84
PFE Chapter 30, Data tables page 13
Exercises
1. The spreadsheet below shows the value of the function
(
)
2
316fx x x
=
+− for x=3. Create
the indicated data table and use it to graph the function in the range (-10,14).
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
ABCD
x3
f(x) 2 <-- =B3^2+3*B3-16
Data table
2 <-- =B4
-10
-8
-6
-4
-2
0
2
4
6
8
10
12
14
2. The example below calculates the NPV and IRR for an investment.
2.a. Create a one-dimensional data table showing the sensitivity of the NPV and IRR to
the year-1 cash flow (currently $10,000). Use a range of $9,000 - $12,000 in increments
of $500.
2.b. Create a two-dimension data table showing the sensitivity of NPV to the year-1 cash
flow and to the discount rate. Use the same range for the cash flow as above and use
discount rates from 8% to 20%, with increments of 2%.
PFE Chapter 30, Data tables page 14
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
ABCDE
Discount rate 15%
Cost 50,000
Cash flow growth 6%
Year Cash flow
0 (50,000.00) <-- =-B4
1 10,000.00
2 10,600.00 <-- =B9*(1+$B$5)
3 11,236.00 <-- =B10*(1+$B$5)
4 11,910.16
5 12,624.77
6 13,382.26
7 14,185.19
8 15,036.30
9 15,938.48
10 16,894.79
NPV 11,925.54 <-- =NPV(B3,B9:B18)+B8
IRR 20.41% <-- =IRR(B8:B18)
3. Project A and Project B cash flows are given in the spreadsheet below. Recreate the Data
Table in cells A21:C37 and create the graph. Notice that the Data Table headers in cells
B21:C21 have been hidden (see Section 30.3 for details on how to do this).
What is the crossover point of the two lines? (You can use the data table to do this, but
you can also refer to Chapter 3 for a better solution.)
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
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
1 220 300
2 220 300
3 220 300
4 220 300
5 220 300
6 220 100
7 220 100
8 220 100
9 220 100
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 16
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 22 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:
5.a. Using the parameters shown below, what are the call and put prices given
σ
= 10%,
15%, 20%, … , 80%?
PFE Chapter 30, Data tables page 17
5.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 30, Data tables page 1
CHAPTER 30, DATA TABLES
*
slight bug fix: July 12, 2003
Chapter contents
Overview......................................................................................................................................... 1
30.1. A simple example ................................................................................................................. 2
30.2. Summary: How to do a one-dimensional data table ............................................................ 4
30.3. Some notes on data tables..................................................................................................... 7
30.4. Two dimensional data tables............................................................................................... 11
EXERCISES ................................................................................................................................. 12
Overview
Data tables are Excel’s most sophisticated way of doing sensitivity analysis. They are a
bit tricky to implement, but the effort of learning them is well worth it!
*
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 30, Data tables page 2
30.1. A simple example
If we deposit $100 today and leave it in a bank drawing 15% interest for 10 years, what
will be its future value? As the example below shows, the answer is $404.56:
1
2
3
4
5
AB C
Interest rate 15%
Initial deposit 100
Years 10
Future value $404.56 <-- =B3*(1+B2)^B4
DATA TABLE EXAMPLE
Now suppose we want show the sensitivity of the future value to the interest rate. In cells
A10:A16 we have put interest rates varying from 0% to 60%, and in cell B9 we have put =B5,
which refers to the initial calculation of the future value.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
AB C
Interest rate 15%
Initial deposit 100
Years 10
Future value $404.56 <-- =B3*(1+B2)^B4
Interest rate
$404.56 <-- =B5
0%
10%
20%
30%
40%
50%
60%
DATA TABLE EXAMPLE
To use the data table technique we mark the range A9:B16 and then use the command
Data|Table. Here’s the way the screen looks at this point:
PFE Chapter 30, Data tables page 3
The dialog box
asks whether the parameter to be varied is in a row or a
column of the marked table. In our case, the interest rate to be varied is in column A of the table,
so we move the cursor from R
ow input cell to Column input cell and indicate where in the
original example the interest rate occurs: