Page 340 - Excel 2007 Bible

21_044039 ch16.qxp 11/21/06 11:07 AM Page 297
Creating Formulas for Financial Applications
FIGURE 16.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 95 months (more than seven years) to pay off the credit-card balance if
the borrower made the minimum monthly payment. The total interest paid on the $1,000 loan would be
$893.29. This calculation assumes, of course, that no additional charges are made on the account. This
example may help explain why you receive so many credit-card solicitations in the mail.
Figure 16.4 shows some additional calculations for the credit-card example. For example, if you want to 16
pay off the credit card in 12 months, you need to make monthly payments of $91.80. (This amount results
in total payments of $1,101.59 and total interest of $101.59.) The formula in B13 is
=PMT($B$2/12,A13,-$B$1)
FIGURE 16.4
Column B shows the payment required to pay off the credit-card balance for various payoff periods.

Creating a loan amortization schedule
A loan amortization schedule is a table of values that shows various pieces of information for each payment
period of a loan. Figure 16.5 shows a worksheet that uses formulas to calculate an amortization schedule.

297