RATE Formula #NUM Error

Occasional Visitor


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%.