Microsoft Office Tutorials and References

In Depth Information

month to pay off the balance more quickly. However, paying $10 per month isn't sufficient, and the formulas re-

turn an error.

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

Figure 11-3:
This worksheet calculates the number of payments required to pay off a credit card balance by

paying the minimum payment amount each month.

In this example, it would take about 123 months (more than ten years) to pay off the credit card balance if the

borrower made only the minimum monthly payment. The total interest paid on the $1,000 loan would be

$1,468.42. This calculation assumes, of course, that no additional charges are made on the account. This ex-

ample may help explain why you receive so many credit card solicitations in the mail.

Figure 11-4 shows some additional calculations for the credit card example. For example, if you want to pay off

the credit card in 12 months, you need to make monthly payments of $93.23. (This amount results in total pay-

ments of $1,118.81 with total interest of $118.81.) The formula in B13 is

=PMT($B$2/12,A13,-$B$1)