                       RELATED ARTICLES                 # Calculating Interest Rates With Microsoft Excel

Making interest rate calculations? Microsoft Excel can help. Excel's RATE, or interest rate, function lets you precisely calculate the implicit interest rate for a loan given the payment amount, loan amount, and number of payments.

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.

Source: Free Articles from ArticlesFactory.com   California Bail Bonds Bounty Hunters  Immigration Attorney  Moving Relocation Services  Alpine Car Audio  Carpet Cleaning Los Angeles