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

English
, , , , ,
#1

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

#2

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

#3

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)

#4

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.

#5

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.
#6
  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.
#7

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.

#8

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.

#9

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.

#10

All is fine, thanks

#11

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.

#12

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.

#13

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)

#14

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.

#15

Regression bug report: https://bugs.documentfoundation.org/show_bug.cgi?id=112177
Text is Corporate Finance 11/e, Ross, Westerfield, Jaffe, Jordan, p. 99:

image description

#16

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.