Mar 15 2022 05:29 AM
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
Mar 15 2022 05:58 AM
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.
Mar 15 2022 07:08 AM - edited Mar 15 2022 07:12 AM
@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.)
Mar 15 2022 07:26 AM
Mar 15 2022 07:33 AM - edited Mar 15 2022 07:34 AM
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.
Mar 15 2022 07:41 AM
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.
Mar 15 2022 07:47 AM
Mar 15 2022 07:52 AM
@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.
Mar 15 2022 08:03 AM
Mar 15 2022 08:05 AM - edited Mar 15 2022 09:16 AM
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.