Forum Discussion
#NUM error
Using RATE formula and sometimes I get a result and other times I get #NUM. It appears to occur when I have a negative rate of return ie PV is lower than FV in the formula
Keep in mind that PV and FV must have opposite signs if they are non-zero.
=RATE(10,0,2000,-3000) will return a number (4%)
=RATE(10,0,2000,3000) will return #NUM! since PV and FV are both positive.
- good input but I have that issue covered in the formula
brellis45 wrote: ``good input but I have that issue covered in the formula``
Difficult for us to know what you do and do not "cover in the formula" when you neglect to provide to an example formula that demonstrates the problem, even the second time.
-----
brellis45 wrote: ``sometimes I get a result and other times I get #NUM``
This can happen with any set of parameters. Read the RATE help page.
Typically, a #NUM error indicates that the RATE algorithm cannot find a solution within the limits of its implementation, and we need to provide a "guess" parameter. As the help page states:
``If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value. [....] If RATE does not converge, try different values for guess.``
(But unfortunately, we cannot believe everything that is written in MSFT help pages. For example, ``RATE usually converges if guess is between 0 and 1``. Obviously, that is not necessarily the case when we expect a negative rate.)
The #NUM error is not limited to situations where the expected rate is negative. But in my experience, it is indeed more likely in that case.
The following is an example where the expected rate is positive.
=RATE(1440, 1234, -567890, 12345) returns #NUM
=RATE(1440, 1234, -567890, 12345, 0, 2.5%) returns 0.206361918845106%
Unfortunately, there is no "good" way to calculate a good "guess".
If you would like further assistance, please one or more concrete examples for us to work with (formulas and values displayed with up to 15 significant digits, as needed.)
