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-

age rate.

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% 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 nearest penny, caus-

ing 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%.

=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 dis-

crepancy.

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