Excel 2010 Bible

Chapter 15: Creating Formulas for Financial Applications

335

FIGURE 15.2

This chart shows the relative interest and principal amounts for the payment periods of a loan.

On the CD
The workbook shown in Figure 15.3 is available on the companion CD-ROM. The file is named credit card
payments.xlsx.

Range B1:B5 stores input values. In this example, the credit card has a balance of $1,000, and the

lender charges 21.25 percent annual percentage rate (APR). The minimum payment is 2.00 per-

cent (typical of many credit card lenders). Therefore, the minimum payment amount for this

example is $20. You can enter a different payment amount in cell B5, but it must be large enough

to pay off the loan. For example, you may choose to pay $50 per month to pay off the balance

more quickly. However, paying $10 per month isn’t sufficient, and the formulas return an error.

Range B7:B9 holds formulas that perform various calculations. The formula in cell B7, which fol-

lows, calculates the number of months required to pay off the balance:

=NPER(B2/12,B5,-B1,0)

The formula in B8 calculates the total amount you will pay. This formula is

=B7*B5

The formula in cell B9 calculates the total interest paid:

=B8-B1