RATE function in VBA not working

[rforster-10]

Hello Everyone,

My issue is that the RATE formula works in my Excel spreadsheet when I use the following:

However, when I try to replicate this in VBA code, I get Run-time error '5'

Here is my code:

Sub LoanCalculator()

Dim periods As Double
Dim payment As Double
Dim principal As Double
Dim futureValue As Variant
Dim interestRate As Double

periods = 360
payment = 3419.64612201533
principal = -425000
futureValue = 75377.9008606371

interestRate = Rate(periods, payment, principal, futureValue)

Debug.Print(interestRate)

End Sub

I need the principal amount to go in as a negative number for purposes of my calculation. Can someone help me solve this?

Thanks!

[#2]

Make that:
interestRate = Application.WorksheetFunction.Rate(periods, payment, principal, futureValue)

[#3]

@rforster-10 

I agree with @Jan Karel Pieterse's workaround as the better solution.  WorksheetFunction.Rate (Excel RATE) is easier to use, and I suspect it is more reliable, in general.

But a minimal fix is to add a "guess" parameter, to wit:

interestRate = Rate(periods, payment, principal, futureValue, 0, 0.0080)

The zero is the "type" of the annuity, which you defaulted to zero.

0.0080 is 0.80%.  "Gee", you ask, "how did you know to use that guess?"

The payment must be large enough to cover the first period interest plus some principal.  Since the payment is 3419.65 and principal is 425000, the periodic interest rate must be less than 3419.65/425000 = 0.804623529411765%.

"But why", you ask, "does VBA Rate need a guess, but Excel RATE does not?"

Obviously, the internal algorithms are different, and presumably Excel RATE's algorithm is better.

IIRC, VBA Rate does indeed use a different method of approximation.  I believe that Excel RATE used the same method until Excel 2003, 2007 or 2010; I don't remember which.

But the bottom line is:  the VBA run-time error is equivalent to when Excel RATE returns #NUM (or #DIV/0).