SOLVED

How to report a bug in Excel when Contact Support in Help does not work

Copper Contributor

nper is returning incorrect results

21 Replies

@John_Oakey 

 

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.

@John_Oakey 

@Sergei Baklan @Riny_van_Eekelen @mtarler @Peter Bartholomew 

 

Do any of you guys have experience with the NPER that would help here? And if Excel is indeed yielding incorrect results, would you be able to connect with Microsoft? 

best response confirmed by mathetes (Silver Contributor)
Solution

@John_Oakey 

 

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.

@mathetes 
I have no practical experience with NPER. I may contact other MVPs who most probably it's desirable to be more concrete with samples. Plus we shall contact not only Microsoft, but Google and Zoho as well, perhaps other companies.

Example below have no sense from practical calculations point of view ( @Joe User , thank you ), that only to illustrate major spreadsheet apps provide exactly the same result for that function. You may repeat with more practical cases.

Excel:

image.png

Google Sheet:

image.png

ZOHO Sheet:

image.png

@John_Oakey 

i agree with @Joe User; 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.

 

JoeUser_0-1644785322648.png

 

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:

 

JoeUser_1-1644785441959.png

 

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:

 

JoeUser_2-1644787096880.png

 

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.

How excellent! It may take me a while to digest all you have written - thank you.

You know what Joe, you were right to want more info...

I was wrong in my last conjecture. Excel's confusion is not ordinary vs annuity due, it is Pv vs Fv know.

 

That said:

Keeping it simple: PV or FV 1000, i = .10/12 = .008333333, (I = 10%, n =12), pmt = 100

The formula for periods n when Present Value Pv is know:

=-Ln(1-(Pv/pmt)*i)/Ln(1+i)                  Result: 10.48481075

The Excel formula: =NPER(i,pmt,Pv,,0)   Result: -9.64509092

The formula for periods n when Future Value Fv is know:

=LN(1+i*Fv/pmt)/LN(1+i)                     Result: 9.64509092

The Excel formula:  =NPER(i,pmt,,Fv,1)           Result: -10.48481075

I have used named cells to make sure all formulas are using the same values.  It does not matter if you use Ln or Log.

The formulas above have been around a long time

You can change the signs all you want to adjust for Excel's input requirement but the absolute value of the numbers, while not staying exactly the same for Excel’s Fv calc remain very very close and I think are reversed.

Showing formulas

John_Oakey_0-1644794490285.png

 

Showing Results

John_Oakey_1-1644794490287.png

 

Thanks for the interesting discourse.   I realize this is probably something I am doing wrong but I have a good bit of experience with formulas and have not found any other issues with many I am programming for a Python module.  The difference between 10.48 and 10.39 is, I think, a different but still still puzzeling question.  Thank again. 

John

@Joe User 

(sanitized....)

 

@John_Oakey  wrote:

The formula for periods n when Present Value Pv is know:

=-Ln(1-(Pv/pmt)*i)/Ln(1+i) Result: 10.48481075

The Excel formula: =NPER(i,pmt,Pv,,0) Result: -9.64509092

 

-----

Again, because you did not use signed cash flows.  Your pmt and Pv are both positive.  They should not be, because they represent cash flow in different directions.  See https://en.wikipedia.org/wiki/Cash_flow_sign_convention .

 

Signed cash flows are an artifact of computer-based time-value functions.  AFAIK, it was invented for the HP-12C in the early 1980s.

 

Mathematics does not use signed cash flows.

 

For example, suppose pv=1000, g=1%, n=12 and fv=pv*(1+g)^n.  Given pv, fv and n, how do we calculate g?

 

In math, (fv/pv)^(1/n) - 1.  In Excel, RATE(n, 0, pv, -fv) or RATE(n, 0, -pv, fv).

 

[....]

 

There is nothing wrong with Excel's use of PV and FV.  There is nothing puzzling about the difference between 10.48 and 10.39, if you are referring to the different NPER results for type=0 and type=1.

 

[....]

 

@Joe User 

Please don't let the irritation show.  The discussion is likely to be of interest to others as a presentation of the facts.  As a mathematician/engineer, I find finance conventions pretty impenetrable on occasion!   I have been known to toss a coin to decide whether the inflows or outflows should be negated!  Whichever gives me a positive result maybe?

Thanks Peter but at 74 I am long past being concerned over someone getting a bit antsy over a math problem. I changed all the cash flows in the last example just to make everybody happy and the problem persists. Excel has the n results for Pv and Fv reversed.
I am only using Excel as a 'check' against results from known formulas and of the 30 or so I've done so far this is the only discrepancy. I have an MBA in Finance so my references may be a bit dated, but I don't think the math has changed. Anybody who grew up with an HP80 knows how to ignore signs and use common sense interpreting the results.

@John_Oakey 

Could I suggest that you post a copy of the spreadsheet you're using, so the rest of us can see inside, seeing not only the cell references (if you use them) and the exact formula?  To post a spreadsheet, you need to use the "full text editor" and then use the window below the text box to drag and drop your file. It looks like this:

mathetes_0-1644848320547.png

 

OK Peter, I won't bore you with the whole workbook where I use Excel to test working formulas before coding them as a function in Python but here is a partial page that includes everything that you would need to understand. Note to make the negative sign crowd happy I have entered payment and Pv/Fv values manually as negative numbers. I hope you can show me the error of my ways but I'm fairly confident of the actual formulas which are mostly derived from James C. VanHorne's 'Financial Management and Policy".  For me this is pretty much a hobby to create content for www.wikipython.com. Still, I like to be accurate and I realize there are slim odds Excel has an error.

@John_Oakey 

 

Just for the record, John, I'm not  Peter; that would be @Peter Bartholomew 

Nor am I Joe; that would be @Joe User 

 

Your spreadsheet looks impressive. I don't use the functions that are involved, so I'm deferring on the whole issue to those who are more experienced.

 

On a more personal note, I commend you for your hobby with wikipython. That's more or less the same basis on which many of us pitch in on this site, answering questions about Excel. John (mathetes) Alsdorf

@mathetes 

I only could comment that

=NPER(i,-100,1000,,0)
=NPER(i,-100,,1000,0)

give exactly the same result as formulas.

@John_Oakey 

It is possible to get agreement provided appropriate sign conventions are used.

image.png

 

1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
Solution

@John_Oakey 

 

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.

View solution in original post