Page 347 - Excel 2007 Bible

21_044039 ch16.qxp 11/21/06 11:07 AM Page 304
Part II
Working with Formulas and Functions
If the investment term is less than one year, the simple interest rate is adjusted accordingly, based on the
term. For example, $1,000 invested in a six-month CD that pays 5 percent simple annual interest earns $25
when the CD matures. In this case, the annual interest rate multiplies by 6/12.
Figure 16.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 16.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 the fact that interest is 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 5 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 16.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 for-
mula in C10 is
=C9+B10
At the end of the 12-month term, the CD balance is $1,051.16. In other words, monthly compounding
results in an additional $1.16 (compared to simple interest).

304