Excel 2010 Bible

Part II: Working with Formulas and Functions

346

Cell B9 contains the following formula that calculates the periodic interest rate. This value is the

interest rate used for each compounding period.

=B5*(1/B6)

The formula in cell B10 uses the FV function to calculate the value of the investment at the end of

the term. The formula is

=FV(B9,B6*B7,,-B4)

The first argument for the FV function is the periodic interest rate, which is calculated in cell B9.

The second argument represents the total number of compounding periods. The third argument

(pmt) is omitted, and the fourth argument is the original investment amount (expressed as a nega-

tive value).

The total interest is calculated with a simple formula in cell B11:

=B10-B4

Another formula, in cell B13, calculates the annual yield on the investment:

=(B11/B4)/B7

For example, suppose that you deposit $5,000 into a three-year CD with a 4.25 percent annual

interest rate compounded quarterly. In this case, the investment has four compounding periods

per year, so you enter 4 into cell B6. The term is three years, so you enter 3 into cell B7. The for-

mula in B10 returns $5,676.11.

Perhaps you want to see how this rate stacks up against a competitor’s account that offers daily

compounding. Figure 15.13 shows a calculation with daily compounding, using a $5,000 invest-

ment (compare this with Figure 15.12). As you can see, the difference is very small ($679.88 ver-

sus $676.11). Over a period of three years, the account with daily compounding earns a total of

$3.77 more interest. In terms of annual yield, quarterly compounding earns 4.51%, and daily com-

pounding earns 4.53%.

Calculating interest with continuous compounding .......................................

The term continuous compounding refers to interest that is accumulated continuously. In other

words, the investment has an infinite number of compounding periods per year. The following for-

mula calculates the future value of a $5,000 investment at 4.25 percent compounded continuously

for three years:

=5000*EXP(4.25%*3)

The formula returns $5,679.92, which is an additional $0.04 compared with daily compounding.