Posted on March 25, 2010.
Calculation of interest rates with Microsoft Excel The function calculates the rate of interest rate implicit in a set of loan or investment terms
given the number of periods (months, quarters, years or other), the payment period, the present value, future value, and possibly the type of switch-rent, and possibly an estimate of interest rates .
If you set the switch type of annuity to 1, Excel assumes payments occur at the beginning
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 next period
Convention ordinary annuity.
The function uses the following syntax:
RATE (spm, pmt, pv, fv, type, guess)
For example, suppose you want to calculate the implicit interest rate on a rental car for a car of $ 20,000 which requires five-year, $ 250 per month payments (occurring as an annuity due ) and also a lump sum $ 15,000. To do this, assuming you want to start with a proposal 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 slightly less than 1%.
If you annualize this monthly rate by multiplying it by 12, you get an annual equivalent
interest rate of 11.41%.
As another example, suppose you want to calculate the implicit interest rate on a mortgage that requires $ 300,000 thirty-year $ 2,000-a month payments (occurring at an ordinary pension), but (thankfully) no lump sum payment. To do this, assuming you want to start with a proposal 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 annual equivalent
interest rate of 7.0203%.
One last point: Excel solves the RATE function iteratively starting from the argument assumes that you provide. (If you do not supply this optional argument, Excel uses 10%.) If Excel can not resolve the claim rates in 20 attempts, it returns # NUM! error. You can try to guess a different argument that can help because you tell Excel to begin its search for a different point (hopefully earlier) start.
