#### Latest posts by techwriter (see all)

- INFOGRAPHICS – Which Business Entity is Right for You? - September 28, 2016
- 3 Ways to Add Copyright Free Images to Your Blogs, Books and Documents - September 19, 2016
- How to Delete All Hyperlinks in a MS #Word Document through VBA Macro - September 1, 2016

By Stephen Nelson

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 (zero) 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.

About the author: Seattle CPA Stephen L. Nelson wrote the bestselling book, MBA’s Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits the S Corporations Explained and LLCs Explained websites.

*Want to read more about MS Excel tips and tutorials? Visit Hot Excel.*