Forum Discussion
#¡NUM! Error in RATE Formula
Lucas03411 wrote: ``I get #¡NUM! when I apply:`` =RATE(12,2800,-10000) effectively
Because RATE needs a "guess" to help its internal algorithm.
It is difficult to discover a good guess. Usually, I create a table of values that represent the "NPV curve". LMK if you want details.
In this case, =RATE(12,2800,-10000,0,0,25%) works.
Note that RATE returns a periodic rate. So if 12 is a number of years, the result (26.30%) is an annual rate. But if 12 is a number of months, the result is a monthly rate; and typically, the equivalent rate is 12 times that (because there are 12 months in a year; not because the number of periods is 12, by coincidence).
-----
Lucas03411wrote: ``I already tried changing Iterative Calculations in Options and didn't work.``
Because that is a misdirection, even in MSFT documentation.
Do not enable Iterative Calculation mode.
However, in some cases, changing Max Iterations and/or Max Change helps. (Again, IC does not need to be and should not be enabled.)
But off-hand, that does not help in this case.
Lucas03411 wrote: ``I get #¡NUM! when I apply:`` =RATE(12,2800,-10000) effectively
Because RATE needs a "guess" to help its internal algorithm.
It is difficult to discover a good guess. Usually, I create a table of values that represent the "NPV curve". LMK if you want details.
In this case, =RATE(12,2800,-10000,0,0,25%) works.
Note that RATE returns a periodic rate. So if 12 is a number of years, the result (26.30%) is an annual rate. But if 12 is a number of months, the result is a monthly rate; and typically, the equivalent rate is 12 times that (because there are 12 months in a year; not because the number of periods is 12, by coincidence).
-----
Lucas03411wrote: ``I already tried changing Iterative Calculations in Options and didn't work.``
Because that is a misdirection, even in MSFT documentation.
Do not enable Iterative Calculation mode.
However, in some cases, changing Max Iterations and/or Max Change helps. (Again, IC does not need to be and should not be enabled.)
But off-hand, that does not help in this case.
Also I'd like to point out regarding to the periods (in my rate 12 refers to months) that I didn't worked previously with =RATE(18,2400,-10000) but it does as well incorporating ...;0;0;25)