Excel 2010 Bible

Part II: Working with Formulas and Functions

344

Figure 15.10 shows a worksheet set up to make simple interest calculations. The formula in cell B7,

shown here, calculates the interest due at the end of the term:

=B3*B4*B5

The formula in B8 simply adds the interest to the original investment amount.

FIGURE 15.10

This worksheet calculates simple interest payments.

Calculating compound interest ......................................................................

Most fixed-term investments pay interest by using some type of compound interest calculation.

Compound interest refers to interest credited to the investment balance, and the investment then

earns interest on the interest.

For example, suppose that you deposit $1,000 into a bank CD that pays 3 percent annual interest

rate, compounded monthly. Each month, the interest is calculated on the balance, and that

amount is credited to your account. The next month’s interest calculation will be based on a higher

amount because it also includes the previous month’s interest payment. One way to calculate the

final investment amount involves a series of formulas (see Figure 15.11).

Column B contains formulas to calculate the interest for one month. For example, the formula in

B10 is

=C9*($B$5*(1/12))

The formulas in column C simply add the monthly interest amount to the balance. For example,

the formula in C10 is

=C9+B10

At the end of the 12-month term, the CD balance is $1,030.42. In other words, monthly com-

pounding results in an additional $0.42 (compared with simple interest).