Help with Rate() function in Expression Builder

[Burnett71]

I've tried to submit this question several times, but it never seems to post.
I am trying to use the rate() function that is built-in to the expression
builder. I address all the factors as prompted, but keep receiving #Error.
Is there another way to calculate an interest rate based on term, present
value and payment amount?

 

[S.Clark]

From the Help File:

Function CalcRate() As Double

Dim Fmt, FVal, Guess, PVal, Payment, TotPmts, PayType, APR

Const ENDPERIOD = 0, BEGINPERIOD = 1 ' When payments are made.

Fmt = "##0.00" ' Define percentage format.
FVal = 0 ' Usually 0 for a loan.
Guess = 0.1 ' Guess of 10 percent.
PVal = InputBox("How much did you borrow?")
Payment = InputBox("What's your monthly payment?")
TotPmts = InputBox("How many monthly payments do you have to make?")
PayType = MsgBox("Do you make payments at the end of the month?", _
vbYesNo)
If PayType = vbNo Then PayType = BEGINPERIOD Else PayType = ENDPERIOD
APR = (Rate(TotPmts, -Payment, PVal, FVal, PayType, Guess) * 12) * 100
MsgBox "Your interest rate is " & Format(CInt(APR), Fmt) & " percent."

End Function

 

[Burnett71]

Hi S. Clark,

Thank you so much for your help. I apologize for my delayed response.

For some reason, I was posting questions and checked for two days but they
did not appear to be posting so I gave up. In looking for insight on another
issue today (days later), I see that my questions did eventually post and
that you offered to assist. Just want you to know I wasn't ignoring you
effort or trying to waste anyone's time.

Thank you

 

[Klatuu]

I have been having troulbe as well. These discussion groups sometimes get
weird. If it aint working right you can assume it aint working right