RATE Function returns Err:523 with >9 in NPER Postion

micaiah · Blue Pill Joined: Aug 04, 2009 Posts: 1

RATE Function returns Err:523 with >9 in NPER Postion
RATE(9;188.86;31500) = -154.01%
RATE(10;188.86;31500) = Err523

Should this be so? I am trying to evaluate real estate notes. My periods will be in 100's. Am I doing something wrong?

Thanks

yoxi · Posted: Wed Aug 05, 2009 12:33 am Post subject:

I don't know whether this helps (since I have no idea what it actually means!) but the Help for that error reads:

#523 - The calculation procedure does not converge

Function missed a targeted value, or iterative references do not reach the minimum change within the maximum number of steps set.

- padmavyuha

ovvldc · Posted: Wed Aug 05, 2009 10:56 am Post subject:

This could be an outcome if you specified a range of numbers that could lead to multiple results. Like doing and IRR calculation when losses are made in between periods of profit - there are multiple solutions.

Are you sure the math you are trying is feasible on your dataset?

Best wishes,
Oscar

Jim · Posted: Mon Aug 10, 2009 3:05 pm Post subject:

Okay, just set the payment to minus 188.86, and it'll show you a rate of -33.18%. Your actual rate is the positive of this number. For a presentation spreadsheet, use ABS(RATE(10;-188.86;31500)).

When using financial functions, be sure to observe the signs. Cash out carries a minus sign, so PMT needs to be minus. Why the dang function returns a negative number, I do not know. But look at the help file: Its example shows a similar calculation, and a similar negative result. FYI, I checked this on my HP12C; it shows a rate of 33.1779%--exactly what this function returns when you expand to four decimals.