Excel 2010 Bible

Part II: Working with Formulas and Functions

334

FIGURE 15.1

Using the PMT function to calculate a periodic loan payment amount.

Notice that the first argument is an expression that calculates the periodic interest rate by using the

annual interest rate and the payment period. Therefore, if payments are made quarterly on a three-

year loan, the payment period is 3, the number of periods is 12, and the periodic interest rate

would be calculated as the annual interest rate multiplied by 3/12.

In the worksheet in Figure 15.1, range A9:B11 is set up to calculate the principal and interest

amount for a particular payment period. Cell B9 contains the payment period used by the formulas

in B10:B11. (The payment period must be less than or equal to the value in cell B4.)

The formula in cell B10, shown here, calculates the amount of the payment that goes toward prin-

cipal for the payment period in cell B9:

=PPMT(B2*(B3/12),B9,B4,-B1)

The following formula, in cell B11, calculates the amount of the payment that goes toward interest

for the payment period in cell B9:

=IPMT(B2*(B3/12),B9,B4,-B1)

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 15.2 shows the principal and interest portions graphically.

Credit card payments ..............................................................................................

Do you ever wonder how long it would take to pay off a credit card balance if you make the mini-

mum payment amount each month? Figure 15.3 shows a worksheet set up to make this type of

calculation.