# RATE() function with Visual Basic

1. ## RATE() function with Visual Basic

Problem: You cannot write an algebraic formula to do what the RATE() function does when dealing with an annuity.

For example:
Total Interest Paid = 50.32
Monthly Payment = 98.93
Principal or PV = 840.00
Total Payments or FV = 890.37 (840.00 + 50.32 or 98.93 * 9)
Periods/Months = 9

=RATE(N,PYMT,-PV)*12
=RATE(9,98.93,-840)*12
=14.1697%

I know the above is true; however, due to the needs of our IT development team, I need to provide a way to calculate the above WITHOUT the use of the RATE function.

I know this too:
"For any of the finance formulae, the formulae may also be rearranged to determine one of the other unknowns. In the case of the standard annuity formula, however, there is no closed-form algebraic solution for the interest rate (although financial calculators and spreadsheet programs can readily determine solutions through rapid trial and error algorithms)."

This means Excel using some type of iteration to solve the RATE() function. I suspect, this could be done with Visual Basic (way out of my league to program something like this).

Thus, I'm enlisting the experts here. Can someone write a visual basic program that does the same thing as RATE(); however, the code will be such that an IT person could take it and convert it to another programming language (I think they are using SQL...is that a programming language?).

Thanks a bundle!  Register To Reply

2. Given PV, PMT, and N, your IT guys need to solve this for r:

1 / r - 1 / (r * (1+r)^N) = PV / PMT

They can do what RATE does, and assume r = 10% for starters. Just a warming-up exercise over their morning coffee.  Register To Reply