Microsoft Office Tutorials and References
In Depth Information
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.
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 — This is the total number of payment periods in an annuity.
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
• 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 — 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
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-