The Rate function calculates the interest rate implicit in a set of loan or investment terms

given the number of periods (months, quarters, years or whatever), the payment per period, the present value, the future value, and, optionally, the type-of-annuity switch, and also optionally, an interest-rate guess.

If you set the type-of-annuity switch to 1, Excel assumes payments occur at the beginning

of the period, following the annuity due convention. If you set the annuity switch to 0 or

you omit the argument, Excel assumes payments occur at the end of the period following

the ordinary annuity convention.

The function uses the following syntax:

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

As one example, suppose you want to calculate the implicit interest rate on a car lease for a $20,000 car that requires five years of $250-a-month payments (occurring as an annuity due) and also a

$15,000 balloon payment. To do this, assuming you want to start with a guess of 10%, you

can use the following formula:

=RATE(5*12,-250,20000,-15000,1)

The function returns the value .95%, which is a monthly interest rate of just less than 1%.

If you annualize this monthly rate by multiplying it by 12, you get an equivalent annual

interest rate of 11.41%.

As another example, suppose you want to calculate the implicit interest rate on a $300,000 real estate mortgage that requires thirty years of $2000-a-month payments (occurring as an ordinary annuity) but (thankfully) no balloon payment. To do this, assuming you want to start with a guess of 10%, you can use the following formula:

=RATE(30*12,-2000,300000)

The function returns the value .59%, which is a monthly interest rate of slightly more than half a percent.

If you annualize this monthly rate by multiplying it by 12, you get an equivalent annual

interest rate of 7.0203%.

A final point: Excel solves the RATE function iteratively starting with the guess argument you provide.

(If you don't provide this optional argument, Excel uses 10%.) If Excel can't solve the RATE argument within 20 attempts, it returns the #NUM! error. You can try a different guess argument, which may help because you're telling Excel to begin its search from a different (hopefully closer) starting point.