Page 339 - Excel 2007 Bible

21_044039 ch16.qxp 11/21/06 11:07 AM Page 296
Part II
Working with Formulas and Functions
You should note that the sum of B10 and B11 is equal to the total loan payment calculated in cell B6.
However, the relative proportion of principal and interest amounts varies with the payment period. (An
increasingly larger proportion of the payment is applied toward principal as the loan progresses.) Figure
16.2 shows this graphically.
FIGURE 16.2
This chart shows the relative interest and principal amounts for the payment periods of a loan.
The workbook described in this section is available on the companion CD-ROM. The file is
ON the CD-ROM named loan payment.xlsx.
ON the CD-ROM
Credit-card payments
Do you ever wonder how long it would take to pay off a credit-card balance if you make the minimum pay-
ment amount each month? Figure 16.3 shows a worksheet set up to make this type of calculation.
The workbook shown in Figure 16.3 is available on the companion CD-ROM. The file is named
ON the CD-ROM credit card payments.xlsx.
ON the CD-ROM
Range B1:B5 stores input values. In this example, the credit card has a balance of $1,000, and the lender
charges 18.25 percent annual percentage rate (APR). The minimum payment is 2.00 percent (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 greater than or equal to the value in cell B4. For
example, you may choose to pay $50 per month.
Range B7:B9 holds formulas that perform various calculations. The formula in cell B7, which follows, calcu-
lates 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

296