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. Search JabSto ::

Custom Search