Page 338 - Excel 2007 Bible

21_044039 ch16.qxp 11/21/06 11:07 AM Page 295
The following formula calculates the original loan amount for a 48-month loan that has a monthly payment
amount of $117.43. The annual interest rate is 6 percent.
This formula returns $5,000.21. The monthly payment was rounded to the nearest penny, causing the
$0.21 discrepancy.
A loan calculation example
Figure 16.1 shows a worksheet set up to calculate the periodic payment amount for a loan. The loan
amount is in cell B1, and the annual interest rate is in cell B2. Cell B3 contains the payment period
expressed in months. For example, if cell B3 is 1, the payment is due monthly. If cell B3 is 3, the payment is
due every three months, or quarterly. Cell B4 contains the number of periods of the loan. The example
shown in this figure calculates the payment for a $10,000 loan at 9.5 percent annual interest with monthly
payments for 36 months. The formula in cell B6 is
=PMT(B2*(B3/12),B4,-B1)
FIGURE 16.1 =PV(0.06/12,48,-117.43) Creating Formulas for Financial Applications 16
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 16.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 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
payment period in cell B9:
=IPMT(B2*(B3/12),B9,B4,-B1)

295