Re: #VALUE! error in nper?

[John Denker <jsd av8n com>]

On Mon, May 8, 2017 at 11:24 AM, Scott Ballantyne <sdb ssr com> wrote:
> > This seems a bug:

I don't think so.

> > nper(7.75%,50000,646000) -> #VALUE!
> > nper(7.75%,50000,645000) -> 111
> >
> > Seems anything greater than 646000 for PV causes this error, which is
> > very unfortunate for me :(


I reckon the bug report is mistaken in at least two ways.

1) For starters, to get a meaningful result, it is almost certainly necessary
 for the "pmt" field to be negative.

2) The #VALUE! result is correct in cases where the pmt is insufficient to
 keep up with the accrued interest.  Here are the results I get, which do
 not seem erroneous in any obvious way, although the negative results require
 thoughtful interpretation:

A       B               C       D       E               F       G
-9.295  =nper(C1,D1,E1) 7.75%   50000   646000          7.740%  =D1/E1
-9.284                  7.75%   50000   645000          7.752%  
                                                        
#VALUE!                 7.75%   -50000  646000          -7.740% 
111.115                 7.75%   -50000  645000          -7.752%