# NPER function not working as anticipated following example in excel (which works in google sheets). [closed]

In order to calculate the number of periods, t, for an amount x to reach an amount y, given an interest rate of z, the text book that I am using shows the following equation in excel: =NPER(z,0,-x,y) and returns a value t, e.g: =NPER(0.12,0,-25000,50000)=6.116 years As I do not have Excel, I cannot test this, however, the same function does work properly in google sheets. In Calc, it returns Err502.

What is going wrong and is there some way I can follow my text's examples properly in Calc?

Version: 5.4.0.3 (x64) Build ID: 7556cbc6811c9d992f4064ab9287069087d7f62c CPU threads: 4; OS: Windows 6.19; UI render: default; Locale: en-US (en_US); Calc: group

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-10-14 15:58:55.424034

Version: 5.4.0.3 (x64) Build ID: 7556cbc6811c9d992f4064ab9287069087d7f62c CPU threads: 4; OS: Windows 6.19; UI render: default; Locale: en-US (en_US); Calc: group

( 2017-09-02 15:14:01 +0100 )edit

Sort by » oldest newest most voted

With LO 5.4.0.3 and spanish-nicaragua locale, I get 6.118 as answer. The function is: =NPER(0.12,1,-25000,50000). Look at the second parameter. If I replace it with 0 (zero), I get "Err:502". This parameter is the regular payment, that should be zero. With n=0.000000001 instead of 1, I get as answer 6.116255374; the same result with logarithms.

Manually approximating the exponent value of the following equation:
50000 = 25000*(1.12^t), I can get 6.11625537420 as exact solution. Verifiying with SpeQ 3.4, the exponent should be 6.1162553741997 exactly.

With logarithms, t=(log(50000)-log(25000))/log(1.12), I get 6.11625537420

Reviewing the available functions, I believe that the correct function for the problem, is this: =P.DURACION(0.12,25000,50000)

more

Thanks Denis, I can't vote this solution up because I just joined yesterday but it is correct. =P.DURACION(0.12,25000,50000) in English is =PDURATION(0.12,25000,50000) and returns the correct answer in Calc and Excel. It turns out that my school gives me access to Office 365, which I downloaded and installed and in Excel 2016, =NPER(0.12,0,-25000,50000)=6.1162553741997000. Further, the log solution above works as well in both Excel and Calc.

( 2017-09-02 15:05:22 +0100 )edit

You should:

1. Mention your details: LO version and OS at least.
2. File a bug at our Bugzilla on a regression in 5.4 wrt this function (that stopped accepting 0 in its second parameter) - OK in 5.3.4.
more

2. I will, but I do not know what you mean by "on a regression in 5.4 wrt this function" means.
( 2017-09-02 15:09:36 +0100 )edit

It means that it worked OK in 5.3, but fails in 5.4 (which is why @librebel saw normal operation and suggested that you simply used wrong syntax), which means that the bug you've found is a regression. A regression is given higher priority than other bugs in fixing.

( 2017-09-02 17:39:09 +0100 )edit

By the way: you mentioned that you followed some text book as a reference, and the example you posted was taken from there. It would help if you mentioned the book in the bug report that you will file.

( 2017-09-02 19:12:33 +0100 )edit

Thanks, I figured out the idea behind regression after I thought about it and saw Jean-Baptiste's bug response...and I just pieced together wrt=with respect to. I posted the literature reference above as an answer b/c I wasn't sure exactly the appropriate way to do so, and on the Bugzilla...hopefully in the correct manner.

( 2017-09-02 19:39:15 +0100 )edit

All is fine, thanks

( 2017-09-02 19:50:33 +0100 )edit

The function seems to be broken. The above example works for me in LibO V4.4.7, but not in LibO V5.4.1.2. In the second case I get Err.502. Recent versions seem to expect (at least in some cases) as mandatory the fifth parameter 'Number PayType' which is specified to be optional. The error returned doesn't indicate the cause.
=NPER(0.12,0,-25000,50000,0) should work in a point-is-decimal-separator locale.

more

So in Calc 5.4.0.3 en-US, =NPER(0.12,0,-25000,50000,0) and =NPER(0.12,0,-25000,50000,1) both result in Err502.

( 2017-09-02 15:07:48 +0100 )edit

Hello @bvargo,

In my Calc this function returns 6,11625537419971.

Please make sure to follow the locale notation, it could be written as:

=NPER(0,12;0;-25000;50000)

more

This isn't the correct solution as Calc itself actually puts in commas and not semi-colons when making parameter suggestions. Further, when trying to change the periods over to commas and the commas to semicolons, Calc changes the semicolons back to commas, and interprets "0,12" as the first two parameters. The result is 2083.33333.

( 2017-09-02 15:13:45 +0100 )edit

Regression bug report: https://bugs.documentfoundation.org/s... Text is Corporate Finance 11/e, Ross, Westerfield, Jaffe, Jordan, p. 99:

more