Forum Discussion
How to report a bug in Excel when Contact Support in Help does not work
nper is returning incorrect results
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.
21 Replies
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.
Could you give some more details. In particular,
- what are the variables you are feeding into the NPER function?
- what are the results you're getting?
- And what should those results be?
You do realize, of course, that hundreds of thousands of people around the world are using this function, and have been for decades, making million dollar (and other currency) uses of that and related functions. It would be highly unusual that it's just now returning incorrect results. Not impossible. But highly unusual.
I've found errors in calculations done by major and highly respected financial institutions, places where you'd expect the routine calculations to be done flawlessly; so I know it's possible. But I had to give them specifics in order to convince them. That is why it would be helpful if you could spell out a bit more what you're seeing in your use of NPER.
- Keep it simple. Try a present value of $1000 with payments of $100 at 10% APR monthly (.0083 iper). nper yeilds 9.64. Actual answer should be 10.4848 periods. it appears to me they may be substracting instead of adding interest. Formula is correct for Fv calculations.
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.
