help with VBA.Financial.rate error
 

The error message is invalid procedure call or argument.

in my code

rt = rate(264, 652.56, 0, -172050,1) - gives you error

When I change the code a little bit
rt = rate(264, 640.03, 0, -172050,1) - ok

Can anyone explain a little bit why I am getting the error? Does a
little difference in payment make the trick?

Thank

-fj

help with VBA.Financial.rate error
 

The first rate returns a negative value, the second returns a positive.

But this worked fine for me:

rt = Application.WorksheetFunction.Rate(264, 652.56, 0, -172050, 1)

so I have no idea why you are getting an error.

Bernie
MS Excel MVP

wrote in message
oups.com...
The error message is invalid procedure call or argument.

in my code

rt = rate(264, 652.56, 0, -172050,1) - gives you error

When I change the code a little bit
rt = rate(264, 640.03, 0, -172050,1) - ok

Can anyone explain a little bit why I am getting the error? Does a
little difference in payment make the trick?

Thank

-fj

help with VBA.Financial.rate error
 

" wrote:
The error message is invalid procedure call or argument.
in my code
rt = rate(264, 652.56, 0, -172050,1) - gives you error
When I change the code a little bit
rt = rate(264, 640.03, 0, -172050,1) - ok
Can anyone explain a little bit why I am getting the error?
Does a little difference in payment make the trick?

First things first. You need to understand why the first set
of rate() parameters is nonsense. If you make 264 payments
at 652.56, the principal alone is 172,275.84. That is greater
than the FV, which is supposed to be the principal plus
interest. In effect, you are earning negative interest.

In contrast, with the second set of rate() parameters, you
make 264 payments at 640.03, for a total principal of
168,967.92. Since that is less than the FV, you will earn
positive interest to grow the principal to 172,050.

I hope that helps you understand why "a little difference
in payment does the trick". These numbers must make
sense in the real world.

Considering the negative interest in the first rate() formula,
it would not surprise me if rate() returned an error -- were
it not for the fact that the worksheet rate() does not return
an error. In other words, it would not surprise me if the
implementors of the VBA rate() function decided
that the inconsistent parameters should be treated as an
error instead of returning nonsense.

However, it appears to be an accident of implementation.
If you put a "guess" of -.00001, the VBA() rate function does
not fail. Instead, it returns a negative interest rate that is
almost the same as the worksheet rate() result:
-9.9023333760407E-06 (VBA) v. -9.90233337425932E-06
(worksheet). "Close enough for government work" ;-).

I cannot say why the VBA rate() function needs a "guess",
whereas default "guess" suffices for the worksheet rate()
function. It would take an internal analysis of each
implementation to explain. I am more amazed that the
worksheet rate() works without extra effort than I am
that the VBA rate() fails.