+ Reply to Thread
Results 1 to 2 of 2

RATE() function with Visual Basic

  1. #1
    Forum Contributor Lotus123's Avatar
    Join Date
    11-07-2005
    Location
    Texas
    MS-Off Ver
    2007
    Posts
    153

    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!
    Ecce Potestas Casei
    Nathan Head

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1