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.
Also, about the rates (which are extremely excessive of course) it's a loan, a real one, in Argentina where we have 110% ANUAL INFLATION so loans go from over 200% to 1000% in yearly basis, believe it or not (you can check online easily, it's nuts but it's real, lol)
Lucas03411 wrote: ``we have 110% ANUAL INFLATION so loans go from over 200% to 1000% in yearly basis``
My condolences. :wink:
-----
Lucas03411 wrote Re: RATE(12,2800,-10000,0,0,25%): ``For some reason that I do not understand this worked perfectly``
How can I help you understand? What exactly do you not understand: 25% itself; or the fact that RATE requires a guess sometimes?
Does the following help?
-----
Lucas03411 wrote Re: =RATE(18,2400,-10000): ``it does [work] as well incorporating ...;0;0;25)``
First, I hope "25" is a typo. It should be 25%, or equivalently 0.25 -- that is, 25/100.
Second, I hope you realize that 25% will not always work, especially considering your high interest rates.
As I mentioned previously, usually I would create a range of values that represents the "NPV curve", like H21:I42 below.
Formulas:
I22: =PV(H22,$F$21,-$F$22,0)-$F$20
Then we look for where the NPV sign changes (I26:I27). That indicates where the curve should cross zero. We are looking for the rate that causes the NPV to be zero (or nearly so).
So, for this example, the intended should be between 20% (H26) and 25% (H27). We could choose either for our "guess".