Bug 100646 - Financial functions, PayType parameter used incorrectly
Summary: Financial functions, PayType parameter used incorrectly
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Winfried Donkers
URL:
Whiteboard: target:5.3.0
Keywords:
: 100542 100559 100669 (view as bug list)
Depends on:
Blocks:
 
Reported: 2016-06-27 21:13 UTC by raal
Modified: 2016-10-25 18:38 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
document used for testing (19.43 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-07-04 11:12 UTC, Winfried Donkers 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description raal 2016-06-27 21:13:43 UTC 
ODF specification
6.12.20 FV
Summary: Compute the future value (FV) of an investment.
Syntax: FV( Number Rate ; Number Nper ; Number Payment [ ; [ Number Pv = 0 ]
[ ; Number PayType = 0 ] ] )
... PayType: the type of payment, defaults to 0. It is 0 if payments are due
at the end of the period; 1 if they are due at the beginning of the period.

=FV(0,0525/1; 10*1; -100; -6500; 2)  return result 12 182,00  , should 
return #error. Last parameter is 2, only 0 or 1 is allowed.
=FV(0,0525/1; 10*1; -100; -6500; -2)  return result 12 115,19
Comment 1 Winfried Donkers 2016-07-04 11:12:05 UTC 
Created attachment 126058 [details]
document used for testing

Only Part Type 0 or 1 should be accepted.
Comment 3 Eike Rathke 2016-07-04 19:53:13 UTC 
PayType should be Logical, not Number. Excel and Gnumeric do the same.

https://lists.oasis-open.org/archives/office-comment/201607/msg00004.html
Comment 4 Eike Rathke 2016-07-05 09:40:52 UTC 
Reopening because PayType for this and related functions is evaluated weirdly as

 if (PayType > 0.0)
     ... beginning of period
 else
     ... end of period

which is complete nonsense.
Comment 5 Winfried Donkers 2016-07-05 10:43:57 UTC 
It appears that many financial functions:
CUMPIPMT
FV
IPMT
PMT
CUMPRINC
PV
use a horrible interpretation of boolean values (like [<=0] for false).

Changing the subject of the bug report to reflect all functions affected.
Comment 6 Winfried Donkers 2016-07-05 15:08:40 UTC 
*** Bug 100542 has been marked as a duplicate of this bug. ***
Comment 7 Winfried Donkers 2016-07-05 15:09:05 UTC 
*** Bug 100559 has been marked as a duplicate of this bug. ***
Comment 8 Winfried Donkers 2016-07-06 11:12:35 UTC 
*** Bug 100669 has been marked as a duplicate of this bug. ***
Comment 9 Winfried Donkers 2016-07-06 11:29:17 UTC 
(In reply to Winfried Donkers from comment #5)
> It appears that many financial functions:
> CUMPIPMT
> FV
> IPMT
> PMT
> CUMPRINC
> PV
> use a horrible interpretation of boolean values (like [<=0] for false).
> 

NPER is one of the functions affected, too.
Comment 10 Commit Notification 2016-07-07 12:01:34 UTC 
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8340f6dcfb2f391feeddb1bc24931cd6f145669a

tdf#100646 Make correct use argument PartType in financial functions.

It will be available in 5.3.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.