Calculating Interest Rates with Microsoft Excel

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:

Calculating Interest Rates with Microsoft Excel

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 ,000 car that requires five years of 0-a-month payments (occurring as an annuity due) and also a
,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 0,000 real estate mortgage that requires thirty years of 00-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.

Calculating Interest Rates with Microsoft Excel
Check For The New Release in Health, Fitness & Dieting Category of Books NOW!
Check What Are The Top Cooking Books in Last 90 Days Best Cheap Deal!
Check For Cookbooks Best Sellers 2012 Discount OFFER!
Check for Top 100 Most Popular Books People Are Buying Daily Price Update!
Check For 100 New Release & BestSeller Books For Your Collection

Stephen L. Nelson is a small business tax CPA specializing in S corporations and the author of many bestselling books including the MBA's Guide to Microsoft Excel from which this article is adapted. Nelson also edits the Forming an S corp online, the Incorporating a small business and the forming an LLC web sites.