software

The error message is invalid procedure call or argument.

in my code

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

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

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

Thank

-fjThe 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

gt; wrote in message oups.com...
gt; The error message is invalid procedure call or argument.
gt;
gt; in my code
gt;
gt; rt = rate(264, 652.56, 0, -172050,1) -gt; gives you error
gt;
gt; When I change the code a little bit
gt; rt = rate(264, 640.03, 0, -172050,1) -gt; ok
gt;
gt; Can anyone explain a little bit why I am getting the error? Does a
gt; little difference in payment make the trick?
gt;
gt; Thank
gt;
gt; -fj
gt;
quot; wrote:
gt; The error message is invalid procedure call or argument.
gt; in my code
gt; rt = rate(264, 652.56, 0, -172050,1) -gt; gives you error
gt; When I change the code a little bit
gt; rt = rate(264, 640.03, 0, -172050,1) -gt; ok
gt; Can anyone explain a little bit why I am getting the error?
gt; 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 quot;a little difference
in payment does the trickquot;. 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 quot;guessquot; 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). quot;Close enough for government workquot; ;-).

I cannot say why the VBA rate() function needs a quot;guessquot;,
whereas default quot;guessquot; 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.