Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE Chapter 30, Data tables page 1
CHAPTER 30, DATA TABLES
*
This version: February 8, 2004
Chapter contents
Overview......................................................................................................................................... 2
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 ....................................................................................................................................... 13
*
This is a preliminary draft of a chapter of Principles of Finance with Excel. © 2001 – 2004 Simon Benninga
(benninga@wharton.upenn.edu
).
PFE Chapter 30, Data tables page 2
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!
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
PFE Chapter 30, Data tables page 3
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:
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 Row input cell to Column input cell and indicate where in the
original example the interest rate occurs:
PFE Chapter 30, Data tables page 4
When you press OK you get the result:
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% 100
10% 259.3742
20% 619.1736
30% 1378.585
40% 2892.547
50% 5766.504
60% 10995.12
DATA TABLE EXAMPLE
30.2. Summary: How to do a one-dimensional data table
• Create an initial example
• Set up a range with:
PFE Chapter 30, Data tables page 5
o Some variable in the initial example that will be changed (like the interest
rate in the above example)
o A reference to the initial example (like the =B5 in the above). Note that
you will always have a blank cell next to this reference. Note the blank
cells when the variable is in a column:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
AB C D
Interest rate 15%
Initial deposit 100
Years 10
Future value $404.56 <-- =B3*(1+B2)^B4
Interest rate
$404.56 <-- =B5
0%
5%
10%
15%
20%
25%
30%
DATA TABLE EXAMPLE
Blank cell when variable is in column
Here’s the blank cell when the variable is in a row:
6
7
8
9
10
11
12
13
EFGH I JKL
0% 5% 10% 15% 20% 25% 30%
$404.56
=B5
Blank cell when variable is in row
• Bring up the Data|Table command and indicate in the dialog box:
o Whether the variable is in a column or a row
o Where in the initial example the variable occurs:
PFE Chapter 30, Data tables page 6
Variable in column Variable in row
Either way the result will be a sensitivity table:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
AB C DEFGHIJKL
Interest rate 15%
Initial deposit 100
Years 10
Future value $404.56 <-- =B3*(1+B2)^B4
Interest rate
$404.56 <-- =B5 0% 5% 10% 15% 20% 25% 30%
0% 100 $404.56 100 162.8895 259.3742 404.5558 619.1736 931.3226 1378.585
5% 162.8895
10% 259.3742
15% 404.5558
20% 619.1736
25% 931.3226
30% 1378.585
DATA TABLE EXAMPLE
Blank cell when variable is in column
=B5
Blank cell when variable is in row
PFE Chapter 30, Data tables page 7
30.3. Some notes on data tables
Data tables are dynamic
You can change either your initial example or the variables and the table will adjust.
Here’s an example where we’ve changed the interest rates we want to vary (compare to the
previous example):
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% 100
10% 259.3742
20% 619.1736
30% 1378.585
40% 2892.547
50% 5766.504
60% 10995.12
DATA TABLE EXAMPLE
Here’s another example: We change the function we’re calculating, putting
=FV(B2,B4,-B3,,1) in cell B5, as explained in Chapter 1, this function calculates the future value
of 10 annual $100 deposits starting today and accumulating interest at 15% for 10 years.
1
Note
that we’ve also changed the text in cell A5 from “initial deposit” to “annual deposit” to reflect
what’s now happening.
1
As we also explained in Chapters 1 and 29, we put the minus sign before B3 because otherwise—for reasons
beyond logic—Excel produces a negative future value. Note that if we had typed FV(B2,B4,-B3) the assumption is
that there are 10 deposits starting one year from now.
PFE Chapter 30, Data tables page 8
When we press OK, both the example and the data table update:
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 $2,334.93 <-- =FV(B2,B4,-B3,,1)
Interest rate
2334.928 <-- =B5
0% 1000
10% 1753.117
20% 3115.042
30% 5540.535
40% 9773.913
50% 16999.51
60% 29053.64
DATA TABLE EXAMPLE
You can only erase the whole table but you cannot erase part of a table
If you try to erase part of a data table, you’ll get an error message:
PFE Chapter 30, Data tables page 9
You can hide the cell header but not erase it
The formula at the top of the table’s second column (cell B9 in our case, containing the
reference to cell B5) is called the “column header.” This formula controls what the data table
calculates. If you want to print a table, you often want to hide the column header. In the
example below, we’ve put the cursor on cell B9. We then use the command Format|Cells and
go to Number|Custom. Typing a semicolon in the Type box hides the cell:
PFE Chapter 30, Data tables page 10
Here’s the result:
8
9
10
11
12
13
14
15
16
AB C
Interest rate
<-- =B5
0% 1000
10% 1753.117
20% 3115.042
30% 5540.535
40% 9773.913
50% 16999.51
60% 29053.64