XIRR returning warnings and incorrect result

[blacknell]

This is:

- [X] a bug report
- [ ] a feature request
- [X] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

I would expect the result to be 0.13796 (as calculated using XIRR in Excel)
However, this is because my dates are given in non ascending order and the document is clear that this is wrong. I believe a VALUE error should be returned.

What is the current behavior?

Lots of warnings given

Warning: A non-numeric value encountered in /vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Financial.php on line 2288

The result given is 0.16448

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// add code that show the issue here...
$values = [
	1893.67,
	139947.43,
	52573.25,
	48849.74,
	26369.16,
	-273029.18
];

$dates = [
	43643,
	43636,
	43637,
	43640,
	43643,
	43673,
];

$result = \PhpOffice\PhpSpreadsheet\Calculation\Financial::XIRR($values,$dates);

echo $result;

Which versions of PhpSpreadsheet and PHP are affected?

PHP 7.2
Latest PHPSpreadsheet 1.8.2

[blacknell]

I should add that when dates are presented in the required ascending order then the correct result is returned

[blacknell]

Can anyone/someone comment on this issue? I'm up for submitting a pull request for a fix which checks date order and returns VALUE but I'd like to know first that the community agrees it's desirable

[blacknell]

@MarkBaker @PowerKiKi any comments?

[PowerKiKi]

According to https://support.office.com/en-us/article/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d:

Dates may occur in any order

So feel free to submit a PR, but we should stick to the official docs as much as possible and allow any order of dates

[blacknell]

Will do. I see my comments above are incorrect. I'm restating here.

• Dates can be any order, but the result returned is wrong if they are not ascending.

@PowerKiKi - Can you label as Bug please and I'm going to fix it

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[oleibman]

Current version of PhpSpreadsheet returns expected result with no warnings.