RATE error in vba

[Josh_123456]

I am trying to do the following

PI_Rate = Rate(Amortization, ExpectedPayment, -LoanAmount) * 12


each of the 4 variables are dim as double for data type


I am trying to put this value in a text box, and when I run the code it debugs with the following error

run-time error '5'
invalid procedure call or argument

and the yellow highlights the above line

when i place the mouse over the following, these are the values

pi_rate = 0
amortization = 360
expectedpayment = 850
loanamount = 100000

could someone please help me

thank you



Josh

[Carim]

Hi,

I am not sure to fully understand ...
Are you after an effective annual rate ...?
What is the PI_rate ...?

[jasoncw]

Put the negative in front of the payment amount, rather than the loan amount.

[Josh_123456]

it worked perfect,

may I ask why it is the loan amount in an excel sheet function and the amount of payment in vba that you place the negative?


thanks


Josh

[jasoncw]

Actually, on a worksheet, you can use the negative in either the payment or present value. I'm not sure why VBA doesn't like the negative on the present value.