CHAPTER 30, DATA TABLES *

CHAPTER 30, DATA TABLES

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.

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 A B 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 A B 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

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

When you press OK you get the result: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 A B 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:

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 A B 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 E F G H I J K L 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

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 A B C D E F G H I J K L 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

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 A B 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.

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 A B 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

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:

Here’s the result: 8 9 10 11 12 13 14 15 16 A B C Interest rate <-- =B5 0% 1000 10% 1753.117 20% 3115.042 30% 5540.535 40% 9773.913 50% 16999.51 60% 29053.64

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 A B C D E F G H I 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:

9 10 11 12 13 14 15 B C D E F G H $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 _{f x}

( )

3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 A B C D x 3 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.

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 A B C D E 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.)

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 A B C D E F G H I 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:

(

)

PV factor for annuity of for N years

r

= =

+

∑

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 A B C D E F G H I J K r 9% T 5 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%?

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 A B 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

d1 0.6303 <-- (LN(S/X)+(r+0.5*sigma^2)*T)/(sigma*SQRT(T))

d2 0.3828 <-- d1-sigma*SQRT(T)

N(d1) 0.7357 <-- Uses formula NormSDist(d1)

N(d2) 0.6491 <-- Uses formula NormSDist(d2)

Call price 16.32 <-- S*N(d1)-X*exp(-r*T)*N(d2)