Forum Discussion
How to report a bug in Excel when Contact Support in Help does not work
Please show us your formula. Copy-and-paste from the Formula Bar. The following works fine for me:
=NPER(10%/12, 100, -1000)
It returns 10.4848107531311 .
-----
Aha! I suspect that you have a typo: an extra comma; to wit:
=NPER(10%/12, 100,, -1000)
returns 9.64509091983739 (although that rounds to 9.65, not 9.64).
That form incorrectly specifies a "pv" of zero and "fv" of 1000 (signed properly), instead of a "pv" of 1000 and "fv" of zero.
-----
PS.... Or did you mean that your use of NPER returns negative 9.64 (sic)?
In that case, your mistake might be failing to properly sign cash flows, to wit:
=NPER(10%/12, 100, 1000) (wrong!)
That does return -9.64509091983739. But a negative number of periods makes little sense (*).
In Excel, cash flows must be properly signed; that is, opposite signs for inflows and outflows.
In your previous response, you did not identify amounts as inflows and outflows.
If "pv" (1000) is an initial balance (inflow), "pmt" (100) is additional payments (inflows), and "fv" (0) is a final balance (outflow), we cannot have a final balance of zero if the interest rate is positive (10%/12).
So, I interpereted your description to mean, for example: a loan of 1000 (inflow) and repayments of 100 (outflow), with a final balance of zero. The choice of signs (and cash flow "directions") is arbitrary, as long as they are opposites. I chose -1000 and +100. If the final balance were non-zero (balloon payment), it would have the same sign as other payments (positive).
-----
(*) Re: ``a negative number of periods makes little sense``
Aarrgghh! The NPER help page has an example that does return a negative number. In fact, it is similar to John's example, to wit: NPER(12%/12, -100, -1000).
That does not make it "right".
Unfortunately, Excel help pages have become increasingly unreliable and less helpful as they have been rewritten over the years.
i agree with JoeUser2004; the flows were not given the correct signs and, as a result, the zero balance occurs 9 yrs in the past. My calculation for monthly rate
= (1+APR)(1/12) - 1
would also give a slightly different result but
= APR / 12
appears to be correct usage in the US.
[.... withdrawn ....]
- After more experimentation I THINK we will find the formula's return the wrong (opposite) values for annuity due vs ordinary annuity.
John_Oakey wrote: ``we will find the formula's return the wrong (opposite) values for annuity due vs ordinary annuity.``
Well, we cannot and should not discuss this further unless and until you accede to our request that you provide the actual formula, and display all referenced cell values with a precision of 15 significant digits, so that we might have a chance to duplicate your results, if possible.
Also provide the details that demonstrate that the NPER results are incorrect.
-----
IMHO, the following demonstrates that NPER does, indeed, return the "correct" (i.e. intended) result.
Errata: A7 should read "nfv", not "npv".
The formula in B7 is:
=B3*(1+B1)^B6 + B2*(1+B1*B5)*((1+B1)^B6 - 1)/B1 + B4
Ideally, the result is zero. -4.55E-13 is "close enough".
The NPV formula is based on the mathematical formula in the PV help page, to wit:
Errata: That is an NFV formula, not an NPV formula, for signed cash flows (pv, pmt, fv)..
Another way to demonstrate "correctness" (again, as intended) is to calculate PMT based on the return from NPER. Note that =PMT(10%/12, B6, -1000, 0, 1) returns 100, the same as B2. That demonstrates that the NPER result is copacetic with other financial Excel functions.
(Actually, PMT returns 99.9999999999997. That is off by -2.84E-13, which is "close enough".)
It is difficult to prove (or disprove) the correctness of a non-integer NPER in practical terms, because humans count in whole units. We cannot count 10.3943 equal periods.
But for an integer NPER, we can demonstrate correctness with an amortization schedule. For example:
Again, B26 should be zero; and -7.16E-14 is "close enough".
FYI, the formula in B16 demonstrates the correct calculation for type=1 (payment in advance). CUMPRINC and CUMIPMT (et al) use an incorrect calculation.
If that is why you believe that NPER is incorrect, actually the defect is in CUMPRINC, CUMIPMT, PPMT and IPMT, not NPER.
Now, it is your turn to provide as much detail to demonstrate why you believe NPER is wrong.
