Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
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
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
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.
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.
PFE Chapter 30, Data tables page 13
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%.
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.)