=RATE NUM ERROR

Question

=RATE(52,0,-0.14,9500)

joeuser2004 · Answer

mtarler&nbsp; wrote:&nbsp; ``I still don't understand how +/- don't matter``&nbsp;It is not an accounting vs engineering thing.&nbsp; My father the CPA did not understand it.&nbsp; He turned to me the mathematician/scientist/engineer to explain it.&nbsp;(Aside....&nbsp; The OP did not say what the numbers 0.14 and 9500 mean. But it is easier to speak of them as "cash flows", since that is what the terms "pv" and "fv" refer to.).&nbsp;I think my first exposure to signed cash flows was with the HP12-C calculator, 40 years ago.&nbsp; It used an internal formula similar to the one that is documented in the Excel PV help page.&nbsp;But OMG, it was removed recently by the MSFT idiots who have been "simplifying" help pages to the point of being unhelpful.&nbsp; Fortunately, I captured (and corrected) an image from the Excel 2010 help page, to wit:With pmt=0, algebraically the solution for FV becomes:&nbsp;fv = -pv*(1+rate)^nper&nbsp;And with pv=-0.14, nper=52 and rate=23.8555007215111% (rounded), we calculate fv=9500 (approximately), not -9500.&nbsp;But I think the concept of signed cash flows is straight-forward, to wit:&nbsp; inflows and outflows should have opposite signs.&nbsp;The choice of signs does not matter (it depends on point of view), as long as they are used consistently for each Excel function.&nbsp;So as a personal preference, I usually choose signs so that the Excel function result should be positive.&nbsp;(Caveat....&nbsp; Not all Excel financial functions permit that flexibility.&nbsp; Historically, the functions that do not were developed by third parties and contributed to the ATP before they were mainstreamed in Excel 2007.&nbsp; For example, CUMPRINC allows only positive "pv".)&nbsp;

kmbrown · Answer

Why does this keep coming up as an error?

mtarler · Answer

well you want a rate such that a present value of -0.14 will result in a value of 9500 after 52 cycles/periods. I don't see how that is possible. Please review the definitions of the arguments to make sure you are using it right:
The RATE function syntax has the following arguments:
Nper Required. The total number of payment periods in an annuity.
Pmt Required. The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.
Pv Required. The present value — the total amount that a series of future payments is worth now.
Fv Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). If fv is omitted, you must include the pmt argument.

joeuser2004 · Answer

mtarler&nbsp; wrote:&nbsp; ``I don't see how that is possible``&nbsp;For your edification....&nbsp;First, when pmt=0, the exact periodic rate can be calculated simply with the formula:&nbsp;=abs(fv/pv)^(1/nper) - 1&nbsp;I use ABS to avoid dealing with the signed cash flows, in general.&nbsp; In this case, we can write:&nbsp;=(9500 / 0.14)^(1/52) - 1&nbsp;That displays 23.8555007215111%.&nbsp;As a double-check, note that =FV(23.8555007215111%, 52, 0, -0.14) displays 9500.00000000016.&nbsp;The infinitesimal difference is due, in part, because I rounded to the 15-digit rate.&nbsp; When I use the exact calculated rate, FV displays 9500.00000000001.&nbsp; That infinitesimal difference is probably due to internal rounding and binary arithmetic.&nbsp;Finally, we can use RATE to calculate an approximate rate.&nbsp; The original formula results in a #NUM error only because Excel needs a "guess" to help its internal algorithm.&nbsp;There is no good way to determine a "good" guess, especially since Excel algorithms are peculiar wrt "guesses".&nbsp; But with a priori knowledge, we can write:&nbsp;=RATE(52, 0, -0.14, 9500, 0, 24%)&nbsp;That displays 23.855500721511%.&nbsp; And using that exact result, FV displays 9500.00000000001.&nbsp;But I caution that, in general, RATE uses an interative algorithm to derive its result, even though an exact result could be calculated when pmt=0.&nbsp; So sometimes, the RATE result has significant "error" (mathematically speaking).

mtarler · Answer

Joe User, thank you for jumping in as accounting is not my forte. I still don't understand how +/- don't matter. To me that solution results in -9500 not +9500=-0.14*1.23855500721511^52=-9500Guess that is the engineer in me

Forum Discussion

=RATE NUM ERROR

5 Replies

Resources