RATE formula working in Excel spreadsheet but not VBA

[rforster10]

Hello Everyone,

When I use the RATE formula in my Excel spreadsheet it works:
=RATE(360,3419.64612201533,-425000,75377.9008606371)

The formula gives me 0.760897490282994% which is correct for my purposes.

However, when I try to code the Rate function using VBA in my macro, I get a runtime '5' error. See below for 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 to use a principal amount that is negative in VBA for my purposes. Can someone please help me?

Thanks!

 

[DanteAmor]

Hi and welcome to MrExcel!

Change this:
interestRate = Rate(periods, payment, principal, futureValue)

For this:
interestRate = WorksheetFunction.Rate(periods, payment, principal, futureValue)

 

[rforster10]

Hi and welcome to MrExcel!

Change this:
interestRate = Rate(periods, payment, principal, futureValue)

For this:
interestRate = WorksheetFunction.Rate(periods, payment, principal, futureValue)

Thanks, Dante. This solved my issue. I really appreciate you taking the time to show me this.

 

[DanteAmor]

Im glad to help you, thanks for the feedback.