#NUM error

[brellis45]

Using RATE formula and sometimes I get a result and other times I get #NUM. It appears to occur when I have a negative rate of return ie PV is lower than FV in the formula

[#2]

@brellis45 

Keep in mind that PV and FV must have opposite signs if they are non-zero.

=RATE(10,0,2000,-3000) will return a number (4%)

=RATE(10,0,2000,3000) will return #NUM! since PV and FV are both positive.

[#3]

good input but I have that issue covered in the formula

[#4]

@brellis45  wrote:  ``good input but I have that issue covered in the formula``

Difficult for us to know what you do and do not "cover in the formula" when you neglect to provide to an example formula that demonstrates the problem, even the second time.

-----

@brellis45  wrote:  ``sometimes I get a result and other times I get #NUM``

This can happen with any set of parameters.  Read the RATE help page.

Typically, a #NUM error indicates that the RATE algorithm cannot find a solution within the limits of its implementation, and we need to provide a "guess" parameter.  As the help page states:

``If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value. [....] If RATE does not converge, try different values for guess.``

(But unfortunately, we cannot believe everything that is written in MSFT help pages.  For example, ``RATE usually converges if guess is between 0 and 1``.  Obviously, that is not necessarily the case when we expect a negative rate.)

The #NUM error is not limited to situations where the expected rate is negative.  But in my experience, it is indeed more likely in that case.

The following is an example where the expected rate is positive.

=RATE(1440, 1234, -567890, 12345) returns #NUM

=RATE(1440, 1234, -567890, 12345, 0, 2.5%) returns 0.206361918845106%

Unfortunately, there is no "good" way to calculate a good "guess".

If you would like further assistance, please one or more concrete examples for us to work with (formulas and values displayed with up to 15 significant digits, as needed.)

[#5]

Here is my formula. Sometimes I receive a numeric result as I should but other times I receive #NUM

=RATE(($A$2-A5)/365,,E5*-1,I5)

A2 is today's date and A5 is a date prior to today. The value in E5 and I5 is always positive

[#6]

@brellis45 

That doesn't help at all, since we don't know what A5, E5 and I5 contain.

Please attach a sample workbook, or make it available through OneDrive, Google Drive, Dropbox or similar.

[#7]

@Hans Vogelaar 

Apologies but how do I attach my workbook?

[#8]

@brellis45 

Thanks for the formula.  But I wrote:  ``provide to an example formula that demonstrates the problem``.

That means:  provide the values of A2, A5, E5 and I5 that cause RATE to return #NUM.

And when you do, note that I also wrote:  provide ``values displayed with up to 15 significant digits, as needed``.

That means:  show us the values of E5 and I5 (at least) with the max precision that Excel will display. 

One way to do that is to highlight each reference in the Formula Bar and press f9.  Remember to press ctrl+z or Esc to restore the original formula.

I suggest that you do that with A2 and A5, as well, showing us their decimal value, not in date form. 

Even though we expect them to be integers, sometimes they are not.  And the date form can be misinterpreted (e.g. MDY vs. DMY).

Remember:  the more (accurate) details that you provide, the sooner you can expect to get a helpful response.

[#9]

A2 44634.6653020833
A5 44,586.000000000000000
E5 $3,423.460000000000000
I5 $2,660.500000000000000

[#10]

@brellis45  wrote:  ``=RATE(($A$2-A5)/365,,E5*-1,I5)``

Please forgive the incessant responses, and I think it would be helpful to you if we solved your problem with RATE -- which, again, requires more information from you.

But I just noticed that in your use of RATE, the "pmt" is zero.  In that case, you can calculate the periodic rate without using RATE (and more accurately) with the following formula:

=ABS(I5 / E5)^(365 / ($A$2 - A5)) - 1

-----

Aside.... In the future, you can write simply -E5 instead of E5*-1.

[#11]

That formula works in all cases but still seems like the RATE formula should have worked

[#12]

@brellis45 

With the data provided:

Formulas:

B5: =ABS(B4 / B3)^(365 / (B1 - B2)) - 1

B6: =RATE((B1-B2)/365, 0, -B3, B4)

B7: =RATE((B1-B2)/365, 0, -B3, B4, 0, -85%)

B5 returns -84.9092087421774%.

B7 returns -84.9092087422167%

But note that A2 is not an integer, as I suspected might happen.  It represents a time of day as well as date.

If you truly want just the difference in days, as I suspect, the expression should be (INT($A$2)-A5)/365.

Better still:  use =TODAY() instead of =NOW() in A2.

[#13]

good stuff. thanks