The Mathematics of Money

216 Chapter 5 Spreadsheets

Since Tom’s starting balance was $5,000, we will enter 5000 in cell B2. The interest he

earned in the fi rst year can be found by using I 
 PRT 
 $5,000(0.07)(1) 
 $350. However,

rather than just enter 350 in C2, we will use a formula. “
B2*.07*1” would work to let

the spreadsheet program do the calculation. However, as discussed above, we need to

include an instruction in our formula so that the value will be rounded appropriately. The

formula we need then is “
ROUND(B2*.07*1,2)”.

The ending balance for the fi rst year should be $5,000  $350 
 $5,350, but once again,

we’ll let the spreadsheet do the work. Since the ending balance is the starting balance plus

the interest earned, entering the formula “
B2C2” in cell D2 will do the trick. We don’t

need to include rounding instructions in this formula, since the numbers we are adding will

already be in dollars and cents terms. However, as we discussed above, it doesn’t hurt to

round it either, so “
ROUND(B2C2,2)” would also be acceptable.

Your spreadsheet should now look like this:

1

2

3

4

5

A B C D

Time

1 $5,000.00 $350.00 $5,350.00

2

3

4

Starting Balance Interest Earned Ending Balance

Now let’s look at year 2. The starting balance for year 2 is the same as the ending balance

for year 1, so in cell B3 we can use the simple formula “
D2”. Interest is calculated the

same way in year 2 as in year 1, and so we can just copy cell C2 and paste it into C3. Simi-

larly, we want D3 to do the same thing as D2 did, and so we can just copy and paste cell D2

into D3. The result should be:

1

2

3

4

5

A B C D

Time

1 $5,000.00 $350.00 $5,350.00

2 $5,350.00 $374.50 $5,724.50

3

4

Starting Balance Interest Earned Ending Balance

In each of the remaining rows, we want essentially the same thing as we have in row 3. Since

the formulas will adjust the cell references automatically, we can complete our table simple

by highlighting cells A3 through D4, copying them, then highlighting the range of cells from

A4 down to D5, and then pasting.

The fi nal result should look like this:

1

2

3

4

5

A B C D

Time

1 $5,000.00 $350.00 $5,350.00

$5,350.00 $374.50 $5,724.50

$5,724.50 $400.72 $6,125.22

$6,125.22 $428.77 $6,553.99

2

3

4

Starting Balance Interest Earned Ending Balance

If we wanted to have the account run for a longer period of time, we would only need to

copy the formulas from the last row into as many rows as we like. Doing this, we can create

a table for 40 years as easily as 4. In fact, now that we mention it...