Microsoft KB Archive/138536

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

Article ID: 138536

Article Last Modified on 1/11/2001

-

APPLIES TO


 * Microsoft Visual Basic 4.0 Standard Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 16-bit Enterprise Edition
 * Microsoft Visual Basic 4.0 32-Bit Enterprise Edition
 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q138536



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

In Visual Basic Version 3.0, the error message is as follows:

Illegal Function Call

In Visual Basic Version 4.0, the error message is as follows:

Invalid procedure call

In Visual Basic 5.0 and 6.0, the error message is as follows:

Invalid procedure call or argument



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

Errhandler:

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.



Steps to Reproduce Behavior in Visual Basic 4.0
 Start a new project. Form1 is created by default.  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."  Run the program by pressing the F5 key. A run-time error occurs when the code calls the Rate function.

Keywords: kbprb KB138536

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.