Microsoft Office Tutorials and References
In Depth Information
The syntax of the RATE function is similar to those of the PMT family of functions.
RATE( nper pmt type guess ) ,
Because there are some differences between RATE and the other PMT functions, Table 9-4
summarizes the RATE function’s arguments for you.
Table 9-4. The Rate Function’s Arguments
Argument
Description
Nper
The total number of payment periods in an annuity.
Pmt
The payment made each period. This value can’t change over the life of
the annuity. If pmt is omitted, you must include the fv argument.
Pv
The present value of the annuity—the total amount that a series of future
payments is worth now.
Fv
The future value, or a cash balance that you want to attain after the last
payment is made. If fv is omitted, it’s assumed to be 0. (The future value
of a loan, for example, is 0.)
Type
The number 0 or 1, indicating when payments are due. (0 is the default,
which means payments are due at the end of the month.)
Guess
Your guess for what the rate will be. If you omit guess, it’s assumed to be
10 percent. If RATE doesn’t converge, try different values for Guess . RATE
usually converges if Guess is between 0 and 1.
So, if you wanted to figure out the interest rate on a \$150,000 home loan that you were paying
back at \$1,186.19 a month over 15 years, you would use the following formula to determine
the annual percentage rate of the loan:
=RATE(180,-1186.19,150000)*12
It’s important to enter the payment (the second parameter) as a negative number. It might
make it easier to remember this requirement if you think of the payment as money that’s