Ask Your Question

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

asked 2017-09-01 16:48:33 +0100

bvargo gravatar image

updated 2020-10-28 16:32:07 +0100

Alex Kemp gravatar image

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: (x64) Build ID: 7556cbc6811c9d992f4064ab9287069087d7f62c CPU threads: 4; OS: Windows 6.19; UI render: default; Locale: en-US (en_US); Calc: group

edit retag flag offensive 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: (x64) Build ID: 7556cbc6811c9d992f4064ab9287069087d7f62c CPU threads: 4; OS: Windows 6.19; UI render: default; Locale: en-US (en_US); Calc: group

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

5 Answers

Sort by » oldest newest most voted

answered 2017-09-02 08:48:35 +0100

Denis J Navas gravatar image

updated 2017-09-02 09:10:08 +0100

With LO 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)

edit flag offensive delete link 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.

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

answered 2017-09-01 17:58:33 +0100

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.
edit flag offensive delete link more


  1. Added to the OP.
  2. I will, but I do not know what you mean by "on a regression in 5.4 wrt this function" means.
bvargo gravatar imagebvargo ( 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.

Mike Kaganski gravatar imageMike Kaganski ( 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.

Mike Kaganski gravatar imageMike Kaganski ( 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.

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

All is fine, thanks

Mike Kaganski gravatar imageMike Kaganski ( 2017-09-02 19:50:33 +0100 )edit

answered 2017-09-01 21:56:12 +0100

Lupp gravatar image

updated 2017-09-01 22:05:29 +0100

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.

edit flag offensive delete link more


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

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

answered 2017-09-01 17:12:36 +0100

librebel gravatar image

Hello @bvargo,

In my Calc this function returns 6,11625537419971.

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


edit flag offensive delete link 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.

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

answered 2017-09-02 19:35:35 +0100

bvargo gravatar image

updated 2017-09-02 19:36:32 +0100

Regression bug report: Text is Corporate Finance 11/e, Ross, Westerfield, Jaffe, Jordan, p. 99:

image description

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2017-09-01 16:48:33 +0100

Seen: 1,578 times

Last updated: Sep 02 '17