Forum Discussion
RATE Formula #NUM Error
Hello,
I am using Rate formula in Excel. I entered N=44, PMT=0, PV=-1650, FV=15000000, Guess=0. I enabled iterative calculations. The formula generated #NUM error. I experimented with Maximum Iterations and Maximum Change, still #NUM. How do I resolve this?
1 Reply
gsarpkaya wrote: `` I enabled iterative calculations.``
First, do not enable iterative calculations. You might have read instructions to do that, even in MSFT documentation. It is wrong!
You are correct that sometimes, a #NUM error can indicate that RATE needs some help by way of a "guess". But zero is an unlikely choice, in this case.
(Did you really try a "guess" of 0%?! Or did you enter RATE(44, 0, -1650, 15000000, 0)? If the latter, zero is the "type", not the "guess". In the future, post your formulas exactly as you enter them.)
I suspect you are mistyping the FV: 15000000 is 15,000,000 -- from 1650 to 15 million in just 44 periods?!
But it is not infeasible. Since pmt=0, we can calculate the exact growth (interest?) rate as follows:
=(15000000/1650)^(1/44) -1
The result is 23.0179110523521%.
To confirm, note that with the above formula in A2, 1650*(1+A2)^44 displays 15000000.
Of course, we cannot be expected to know 23%. But we might try 15%, then 20%. And in fact:
=RATE(44, 0, -1650, 15000000, 0, 20%)
returns 23.0179110523521%.
