Bug 112177 - NPER function not working as anticipated following example in excel (which works in google sheets)
Summary: NPER function not working as anticipated following example in excel (which wo...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.0.3 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Winfried Donkers
QA Contact:
URL:
Whiteboard: target:6.0.0 target:5.4.2
Keywords: bibisected, bisected, regression
Depends on:
Blocks:
 
Reported: 2017-09-02 13:28 UTC by bvargo
Modified: 2017-09-07 19:19 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Excel example from text (31.47 KB, image/png)
2017-09-02 17:33 UTC, bvargo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description bvargo 2017-09-02 13:28:34 UTC
Description:
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 The same function does work properly in google sheets and Excel. In Calc, it returns Err502.

Steps to Reproduce:
1.=NPER(0.12,0,-25000,50000)
2.
3.

Actual Results:  
ERR502

Expected Results:
6.1162553741997



Reproducible: Always

User Profile Reset: No

Additional Info:
Also see information at the ask.libreoffice.org site:  
https://ask.libreoffice.org/en/question/128333/nper-function-not-working-as-anticipated-following-example-in-excel-which-works-in-google-sheets/?answer=128384#post-id-128384

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



User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36
Comment 1 Jean-Baptiste Faure 2017-09-02 17:04:09 UTC
Thank you for the bug report.

Reproducible with LO 5.4.1 and current master. The problem is that this function, in these LO versions, does not accept zero as second argument. Using a very small value instead of 0 gives a result close to the expected value.

Works as expected in LO 5.3.6, so regression.

Best regards. JBF
Comment 2 Mike Kaganski 2017-09-02 17:30:48 UTC
Regression from commit https://cgit.freedesktop.org/libreoffice/core/commit/?id=190eaa760336ec7c8f2d8d89785b22e770b3e8d6

> author	Winfried Donkers <winfrieddonkers@libreoffice.org>	2017-03-05 12:13:36 (GMT)
> committer	Eike Rathke <erack@redhat.com>	2017-03-06 16:45:25 (GMT)
> commit	190eaa760336ec7c8f2d8d89785b22e770b3e8d6
> tree	3ee7d9c2a43d9e0e4cef4f6cc90a6b87c3b3fe1e
> parent	67bff281f9befbea8b4ff028fabe056b8c4e184b
> Check for divide by 0 in Calc function NPER.
> Plus improve efficiency and use correct prefixes for variable names.
Comment 3 bvargo 2017-09-02 17:33:02 UTC
Created attachment 135972 [details]
Excel example from text

Text is _Corporate Finance_ 11/e, Ross, Westerfield, Jaffe, Jordan, p. 99:
Comment 4 Mike Kaganski 2017-09-02 17:42:19 UTC
The lines from the patch

> +    else if ( fPmt == 0.0 )
> +        PushIllegalArgument();  // No payment, future value can never be reached

don't take into account that future value can be reached not only by payments, but also by accumulated interest
Comment 5 Winfried Donkers 2017-09-04 09:18:50 UTC
I'll fix this.
Comment 6 Commit Notification 2017-09-07 10:31:38 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=003c2cfaa258e204402a366a105366da74e220a2

tdf#112177 remove incorrect constraint for Calc function NPER.

It will be available in 6.0.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 7 Commit Notification 2017-09-07 13:17:33 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=085768b2a0dd1fc6371e8b2ba4e2418ba96cdb21&h=libreoffice-5-4

tdf#112177 remove incorrect constraint for Calc function NPER.

It will be available in 5.4.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 Jean-Baptiste Faure 2017-09-07 19:19:15 UTC
Verified fixed in master and LO 5.4.2.0.0+ both built at home under Ubuntu x86-64.

Best regards. JBF