Microsoft Office Tutorials and References
In Depth Information
This formula returns 6.00%. Notice that the result of the function is multiplied by 12 to get the annual percent-
The NPER function returns the number of payment periods for a loan, given the loan's amount, interest rate, and
periodic payment amount. The syntax for the NPER function is
The following formula calculates the number of payment periods for a $5,000 loan that has a monthly payment
amount of $117.43. The loan has a 6% annual interest rate.
This formula returns 47.997 (that is, 48 months). The monthly payment was rounded to the nearest penny, caus-
ing the minor discrepancy.
The PV function returns the present value (that is, the original loan amount) for a loan, given the interest rate,
the number of periods, and the periodic payment amount. The syntax for the PV function is
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%.
This formula returns $5,000.21 . The monthly payment was rounded to the nearest penny, causing the $0.21 dis-
A loan calculation example
Figure 11-1 shows a worksheet set up to calculate the periodic payment amount for a loan.
The workbook described in this section is available at this book's website. The file is
named loan payment.xlsx.
The loan amount is in cell B1, and the annual interest rate is in cell B2. Cell B3 contains the payment period ex-
pressed 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 $25,000 loan at 6.25% annual interest with monthly payments for 36
months. The formula in cell B6 is