Issue 27597 - XIRR Still Broken
Summary: XIRR Still Broken
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.1.1
Hardware: PC Windows 2000
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: needhelp
Depends on:
Blocks: 18704
  Show dependency tree
Reported: 2004-04-08 10:52 UTC by chris319
Modified: 2013-08-07 15:12 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Demonstrates Problem with XIRR Function in 1.1.1 (7.77 KB, application/vnd.sun.xml.calc)
2004-04-08 10:56 UTC, chris319
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description chris319 2004-04-08 10:52:48 UTC
XIRR function not calculating properly. Attachment to follow.
Comment 1 chris319 2004-04-08 10:56:26 UTC
Created attachment 14409 [details]
Demonstrates Problem with XIRR Function in 1.1.1
Comment 2 chris319 2004-04-08 11:06:37 UTC
XIRR returns an error if there is a date present in the bottom row of the date 
range (see cell D68 in attached file). XIRR returns the wrong value when there 
is NOT a date present (see cell A68 in attached file). The value in cell B70 of 
the attachment should be +2.3% as checked against a competing spreadsheet 

In addition, XIRR should be calculated on the basis of 356.25 days in a year 
because there are 1461 days in a four-year period due to leap year, not 1460.
Comment 3 frank 2004-04-08 15:07:59 UTC
Hi Eike,

as discussed this one is for you.

Comment 4 ooo 2004-04-08 15:52:20 UTC
This may be related to issue 27002, and also falls under the category of
functions to be investigated => adding blocking dependency to issue 18704.
Adding needhelp keyword for time reasons.
Comment 5 niklas.nebel 2004-04-08 16:33:02 UTC
No, this is unrelated.
- XIRR is calculated based on 365 days a year, by convention. This is compatible
with other spreadsheets and won't be changed.
- The empty cell is evaluated as value 0 (date 1899-12-30). -0.25% actually is a
valid solution then (in 1980-05-13 values, the sum of the other values is then
218397.48, as is the value of 267741.73 on 1899-12-30). Sure, these unusual
cases could be rejected, but it's not a "wrong value".
- The error code in E70 is caused by the default "guess" value of 10%, which
breaks the iteration. If you specify anything near the result (like 1%), the
formula works. This is the only real problem here. Eike, there is also internal
#86392# for that.
Comment 6 chris319 2004-04-13 01:03:30 UTC
A major commercial spreadsheet application was able to return a usable value for
XIRR from this same data with the default guess of 10%. Perhaps OOo needs more
iterations or a looser error value to get a data set such as this to converge?
From the user's point of view it can be difficult to estimate a suitable "guess"
value when all they see is the Err:502 message and conclude that the function
isn't working properly.
Comment 7 daniel.rentz 2008-04-02 14:54:36 UTC
taking over