Calculating Interest Rates with Microsoft Excel

The Rate function calculates the interest rate implicit in a residence of loan or investment terms
given the number of periods (months, quarters, years or whatever), the payment per period, the demonstrate value, the future value, and, optionally, the type-of-annuity switch, and also optionally, an interest-rate guess.

If you position the type-of-annuity switch to 1, Excel assumes payments occur at the beginning
of the period, following the annuity due convention. If you region the annuity switch to 0 or
you omit the argument, Excel assumes payments occur at the kill of the period following
the ordinary annuity convention.

The function uses the following syntax:

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

As one example, whine 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 originate with a guess of 10%, you
can spend the following formula:

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

The function returns the value .95%, which is a monthly interest rate of honest less than 1%.
If you annualize this monthly rate by multiplying it by 12, you score an equivalent annual
interest rate of 11.41%.

As another example, enlighten you want to calculate the implicit interest rate on a $300,000 staunch 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 commence with a guess of 10%, you can utilize 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 gain 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 befriend because you’re telling Excel to open its search from a different (hopefully closer) starting point.