GNOME Bugzilla – Bug 333631
xirr function returns #NUM! depending on date ranges and values
Last modified: 2006-03-06 17:44:31 UTC
gnumeric version 1.6.2 The issue came up when importing a spreadsheet from openoffice.calc. It contained a XIRR funcion which computed correctly while Gnumeric's xirr would fail with "#NUM!". After filling up all empty date cells with "=today()", xirr computed as expected. So my first assumption was that Gnumeric's xirr fails on empty date cells. But after playing around a bit I noticed that xirr fails with date ranges covering more than n days, as in the example below with the date range exceeding 10281 days. A B C D -5000 29/03/1975 30/03/1975 5500 23/05/2003 23/05/2003 23/05/2003 xirr: #NUM! #NUM! 0.34% days: 37764 10282 10281 Playing around a bit more I noticed that not only the date range matters but also the values. Try the example above with the second value set to 5000 (column D also fails) and 6000 (both columns C and D compute). Is this possibly a problem with internal precision? I noticed another difference between Calc and Gnumeric: Gnumeric treats empty cells as if containing the numeric value of 0, which translates to "31/12/1899 0:00". In openoffice.calc it is interpreted as "30.12.1899 00:00:00". Is there a compatibility issue with numeric date values beeing exactly one day apart?
Dates before 1-Mar-1900 are one day off because Gnumeric has chosen to follow Excel which includes the (non-existing date) 29-Feb-1900. In practice the difference is unimportant. XIRR is calculated by root search. Evidently there are cases where we aren't good enough at that. (But specifying the third argument will cause Gnumeric to find it.) There are surely cases which we find, but OOo does not. We have all the tools at hand to fix this.
Fixed in cvs.