Excel 2010 Bible

Chapter 15: Creating Formulas for Financial Applications

333

NPER ............................................................................................................

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

NPER(rate,pmt,pv,fv,type)

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 percent annual interest rate.

=NPER(6%/12,117.43,-5000)

This formula returns 47.997 (that is, 48 months). The monthly payment was rounded to the near-

est penny, causing the minor discrepancy.

PV ................................................................................................................

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

PV(rate,nper,pmt,fv,type)

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.

=PV(6%/12,48,-117.43)

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 15.1 shows a worksheet set up to calculate the periodic payment amount for a loan.

On the CD
The workbook described in this section is available on the companion CD-ROM. 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 pay-

ment 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 peri-

ods of the loan. The example shown in this figure calculates the payment for a $25,000 loan at

6.25 percent annual interest with monthly payments for 36 months. The formula in cell B6 is

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