Free Articles, Free Web Content, Reprint Articles
Saturday, September 21, 2019
 
Free Articles, Free Web Content, Reprint ArticlesRegisterAll CategoriesTop AuthorsSubmit Article (Article Submission)ContactSubscribe Free Articles, Free Web Content, Reprint 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 argumentArticle Search, 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

ABOUT THE AUTHOR


Seattle accountant and bestselling computer book author Stephen L. Nelson wrote the MBA's Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits the S Corporations Explained website (homepage), the LLCs Explained website (homepage), and the Fast Easy Incorporation Kits website (homepage).



Health
Business
Finance
Travel
Technology
Home Repair
Computers
Marketing
Autos
Family
Entertainment
Law
Education
Communication
Other
Sports
ECommerce
Home Business
Self Help
Internet
Partners


Page loaded in 0.040 seconds