Excel 2010 Bible

Chapter 15: Creating Formulas for Financial Applications

331

Note
The loan calculations in this section all assume a fixed-rate loan with a fixed term. n

Worksheet functions for calculating loan information ............................................

This section describes six commonly used financial functions: PMT, PPMT, IPMT, RATE, NPER,

and PV. For information about the arguments used in these functions, see Table 15.1.

TABLE 15.1

Financial Function Arguments

Function Argument Description

rate The interest rate per period. If the rate is expressed as an annual interest rate, you must

divide it by the number of periods.

nper The total number of payment periods.

per A particular period. The period must be less than or equal to nper.

pmt The payment made each period (a constant value that does not change).

fv The future value after the last payment is made. If you omit fv, it is assumed to be 0.

(The future value of a loan, for example, is 0 .)

type Indicates when payments are due — either 0 (due at the end of the period) or 1 (due

at the beginning of the period). If you omit type, it is assumed to be 0.

PMT ..............................................................................................................

The PMT function returns the loan payment (principal plus interest) per period, assuming constant

payment amounts and a fixed interest rate. The syntax for the PMT function is

PMT(rate,nper,pv,fv,type)

The following formula returns the monthly payment amount for a $5,000 loan with a 6 percent

annual percentage rate. The loan has a term of four years (48 months).

=PMT(6%/12,48,-5000)

This formula returns $117.43, the monthly payment for the loan. The first argument, rate, is the

annual rate divided by the number of months in a year. Also, notice that the third argument (pv,

for present value) is negative and represents money owed.

PPMT ............................................................................................................

The PPMT function returns the principal part of a loan payment for a given period, assuming con-

stant payment amounts and a fixed interest rate. The syntax for the PPMT function is

PPMT(rate,per,nper,pv,fv,type)