Rate Function in Excel

Hi,

I came, I saw, I joined because I have a problem. I am now off to ferret in the site to see the skills you members have and the fun involved in taking part here. First, though, my Problem.......

The error message #NUM! comes up using the Rate % formula when getting a 6-month rate in the series below. When the penultimate number is 152 or above, the function works as it should, but at 151 or less, the result is an error. "Help" says one will get this error message in the following circumstance: "Using a worksheet function that iterates, such as IRR or RATE, and the function cannot find a result". Help suggests using a different starting value for the worksheet function. 

Index:- 362.3; 279.6; 289.6; 190.2; 151.0; 164.2.

Result:-                     -36.1; -53.7; #NUM!; -25.5.

Using a different starting value is not a practical option, since I have over 4,000 such series run several times a year,  and will never know when one needs to be rebased. Is there another way round the problem? In case you wonder, this is the total return for the real estate sector of the UK Stockmarket, six month annualised average, to June 2009, and 137.1 is the March index actual number.

Thanks, Edmund.

RATE function? Got an example formula showing how you're trying to use the RATE function? OTOH, if you're trying to come up with a rate for a given series, wouldn't you be using IRR? The details in your posting are unclear.

In general, you should be able to use ridiculously low initial guess values for RATE and IRR to get these functions to return values rather than #NUM! errors. For example, IRR(some_range,-0.5). Starting with extremely low guess values forces Excel to iterate over larger possible ranges of interest rates, and that's often enough to allow Excel to converge to a result.