0

Version: 7.2.3.2 / LibreOffice Community
Build ID: 20(Build:2)
CPU threads: 8; OS: Linux 5.3;
UI render: default; VCL: kf5 (cairo+xcb)
Locale: en-GB (en_GB.UTF-8); UI: en-GB
Calc: threaded

http://www.zen224037.zen.co.uk/LibreOfficeBug.ods

The two calculations are exactly the same apart from the one day difference between the dates in C1 and C3, which shouldn't affect the result anyway because the cash flows for that date are zero.

Can anyone explain this?

Richard Parkins
  • 347
  • 2
  • 13

1 Answers1

0

Explain? No. But the wiki.documentfoundation.org article on XIRR (quoting the ODF standard) says,

The first cash-flow amount is a negative number that represents the investment.

If you change the amount in cell C4 from 0 to -0.01 both XIRR functions return -12.14%. I'd make C2 non-zero as well.

urznow
  • 1,576
  • 1
  • 4
  • 13
  • Well the XIRR function in LibreOffice does not follow this specification, see file:///usr/share/libreoffice/help/en-GB/text/scalc/01/04060118.html?DbPAR=CALC#bm_id3147485 in the LIbreOffice documentation, which should be authoritative for their implementation. In particular, the first cash-flow amount does not need to be negative. Changing all the signs of all the cash flows in the example gives the same positive 18.28% result. I think it may be taking the sign of the first non-zero value as the sign of the investment. – Richard Parkins Mar 19 '22 at 19:37
  • The LibreOffice documentation also says "This function ignores any text or empty cell within a data range." Replacing the zero values in cells C2 and C4 by empty cells still shows the anomalous behaviour. – Richard Parkins Mar 19 '22 at 19:41
  • @RichardParkins: I prefer `wiki.documentfoundation.org` over LibreOffice docs: better quality and easier to navigate. `XIRR` in both Excel and LibreOffice expects an initial negative value, it seems. Perhaps they know more about edge cases at [https://ask.libreoffice.org/tag/xirr](https://ask.libreoffice.org/tag/xirr). – urznow Mar 20 '22 at 10:10
  • XIRR in LibreOfiice (in my copy at least) definitely does *not* expect an initial negative value. I use initial positive values in all of my calculations and they usually give correct results. – Richard Parkins Mar 21 '22 at 11:26