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