PRB:Rate Function Gives Error If It Can't Calculate Accurately

• Standard, Professional, and Enterprise Editions of Microsoft Visual Basic, 16-bit and 32-bit, for Windows, version 4.0
• Standard and Professional Editions of Microsoft Visual Basic for Windows, version 3.0

SYMPTOMS

If the Rate function is unable to converge to within 0.00001 percent of the correct value after 20 iterations, a trappable error will occur.

In Visual Basic Version 3.0, the error is "Illegal Function Call," and is error number 5.

In Visual Basic Version 4.0, the error is "Invalid procedure call," and is also error number 5.

WORKAROUND

If the error occurs, retry the Rate function with a different initial guess value. Sometimes a different guess will lead to numerical convergence within the 20 iterations allowed.

You might want to set up an error handler that progressively changes the guess and retries the Rate function. If the Rate function continues to fail for all the guess values tried, then it may be necessary to warn the user that an accurate calculation of the rate based on the values for Total Payment, Number of Payments, and Present Value is not possible.

The following code implements an error handler to arrive at a Rate value.

Add the following code to the Form_Click event to include the error handler, and a new static variable Newguess, which is adjusted each time an error handler is called:

   Private Sub Form_Click
   Dim Fmt As Variant, FVal As Variant, Guess As Variant, PVal As Variant,_
   TotPmts As Variant, Payment As Variant, PayType As Variant, _
   APR As Variant

   Static NewGuess
   NewGuess = 0.02

   Const ENDPERIOD = 0, BEGINPERIOD = 1    ' When payments are made.
   Const MB_YESNO = 4  ' Define Yes/No buttons.
   Const ID_NO = 7 ' Define No as a response.

   On Error GoTo Errhandler

   Fmt = "##0.00"  ' Define percentage format.
   FVal = 0    ' Usually 0 for a loan.
   Guess = 0.1 ' Guess of 10 percent.
   PVal = 81709.07    '
   Payment = 720.45
   TotPmts = 700
   PayType = BEGINPERIOD
   APR = (Rate(TotPmts, -Payment, PVal, FVal, PayType, Guess) * 12) * 100
   MsgBox "Your interest rate is " & Format(CInt(APR), Fmt) & "percent."

   Exit Sub

   NewGuess = NewGuess + 0.01

   If (NewGuess > 1#) Then
     MsgBox "Calculation of rate not possible-please change Total payments"
     Exit Sub
   End If

   Guess = NewGuess
   Resume

   End Sub

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior in Visual Basic 4.0

1. Start a new project. Form1 is created by default.

2. In the Click event for the form, place the following code:

   Dim Fmt As Variant, FVal As Variant, Guess As Variant, PVal As Variant,_ TotPmts As Variant, Payment As Variant, PayType As Variant, _ APR As Variant

      Const ENDPERIOD = 0, BEGINPERIOD = 1    ' When payments are made.
      Const MB_YESNO = 4  ' Define Yes/No buttons.
      Const ID_NO = 7 ' Define No as a response.
   
      Fmt = "##0.00"  ' Define percentage format.
      FVal = 0    ' Usually 0 for a loan.
      Guess = 0.1 ' Guess of 10 percent.
      PVal = 81709.07    '
      Payment = 720.45
      TotPmts = 700
      PayType = BEGINPERIOD
      APR = (Rate(TotPmts, -Payment, PVal, FVal, PayType, Guess) * 12) * 100
      MsgBox "Your interest rate is " & Format(CInt(APR), Fmt) & "percent."
   
   

3. Run the program by pressing the F5 key. A run-time error occurs when the code calls the Rate function.