Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignore error msgs in formula references | Excel Discussion (Misc queries) | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) | |||
ERROR | Excel Discussion (Misc queries) |