Microsoft Office Tutorials and References

In Depth Information

**Syntax:**

Caution

The algorithm behind the PMT function is new and more accurate in Ex-

cel 2013. Although this will not affect basic loan payment calcula-

tions like the one shown here, be aware that Excel 2007 and Excel 2013

might produce different results for some uses of PMT.

Syntax:

RATE(nper,pmt,pv,fv,type,guess)

The RATE function returns the interest rate per period of an annuity. RATE is

calculated by iteration and can have zero or more solutions. If the success-

ive results of RATE do not converge to within 0.0000001 after 20 iterations,

RATE returns a #NUM! error. This function takes the following arguments:

•
nper

nper
—
This is the total number of payment periods in an annuity.

•
pmt

pmt
—
This is the payment made each period and cannot change over

the life of the annuity. Typically, pmtincludes principal and interest

but no other fees or taxes. If pmtis omitted, you must include the fv

argument.

•
ppv
—
This is the present value
—
the total amount that a series of

future payments is worth now.

•
ffv
—
This is the future value, or a cash balance you want to attain

after the last payment is made. If fvis omitted, it is assumed to be 0,

which means the future value of a loan is zero.

•
type

type
—
This is the number 0 or 1 to indicate when payments are due. The

default value of 0 assumes that payments are due at the end of the

period. A value of 1 means the payments are due at the beginning of

each period.

•
guess

guess
—
This is your guess for what the rate will be. If you omit

guess, the rate is assumed to be 10%. If RATE does not converge, you

can try different values for guess. RATE usually converges if guess

is between 0 and 1.

Make sure you are consistent about the units you use for specifying guess

and nper. If you make monthly payments on a 4-year loan at 12% annual in-