Problem w/Rate function

[Tres]

I'm calling the Excel Rate function from an Access VBA
loop. It crashes on the 1429th iteration w/ the following
values: Rate(1429,0,-1,2.226759896). I get the #NUM error.

(Actually from Access VBA code I get "Run-time error
'2036'. Method 'Collect' of object Recordset failed.")
But I guess this is how VBA handles the #Num error from an
Excel function.

Anyway, how can I prevent this error? I think I'm getting
the error because of iterations...if I chance the 1429 to
1428, I don't get an error.

From the Help I see that I can change the starting value
(which doesn't do anything) or I can change the iteration
values (which also doesn't seem to work).

Is there anything else I can do? I need to be able to do
this calculation...and I need to be able to handle maybe up
to 3000 periods (the "nper" parameter for the Rate function).

Is there possibly an alternate way to calc the same thing?
Or better code that I can use to implement the function
myself in VBA?

Please help.

Thanks,

Tres

 

[A.W.J. Ales]

Tres,

I looked into the help of the Rate function and found that it returns #NUM
is it doesn't converge to within 0.0000001 within 20 iterations.

Since your Ratefunction actually only calculates the intrest rate for a
single payment (ad 1) accrues to an amount of 2.226759896, you could
calculate that rate more direct by solving the equation : (1 + i ) ^ term
You will find then i = exp(ln(2.226759896/1429) -1 which does return a
result.

(The same is true for 1430, 1431 etc.)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Tres]

Thanks so much. You were right on. It works perfectly!!!