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
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
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.
Created attachment 135972 [details] Excel example from text Text is _Corporate Finance_ 11/e, Ross, Westerfield, Jaffe, Jordan, p. 99:
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
I'll fix this.
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.
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.
Verified fixed in master and LO 5.4.2.0.0+ both built at home under Ubuntu x86-64. Best regards. JBF