Microsoft Office Tutorials and References

In Depth Information

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

Figure 11-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 in-

terest 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 three-twelfths.

The worksheet in Figure 11-1 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 principal 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 pay-

ment period in cell B9:

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

The sum of B10 and B11 is equal to the total loan payment calculated in cell B6. However, the relative propor-

tion 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 11-2 shows the principal and interest por-

tions graphically.