XIRR financial function not calculating as in Excel

[blacknell]

This is:

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

What is the expected behavior?

XIRR should be able to return a value -0.6422991 for this simple use case

What is the current behavior?

XIRR returns #NUM!

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

use PhpOffice\PhpSpreadsheet\Calculation\Financial\CashFlow\Variable\NonPeriodic;

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

ini_set('memory_limit', '8192M');
ini_set('display_errors', 1);
ini_set('log_errors', true);

$cashFlows = [55600, -51094.83];
$cashFlowDates = ['24/11/2021', '24/12/2021'];

# Excel returns -0.6422991
echo NonPeriodic::rate($cashFlows, $cashFlowDates).PHP_EOL;

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet 1.20.0 (tested with 1.15 also using pre namespace changes)
PHP 7.4 and 8.0

[oleibman]

I am able to verify that there is a problem. However, your result -0.6422991 does not quite agree with what I see -0.642308. I have confirmed the latter on several platforms/libraries. Can you please confirm the numbers you specified in your sample code?

XIRR is calculated by making an initial guess and then using an algorithm which is fast but is not guaranteed to converge. Your example falls into one of those non-converging areas with the initial default guess of 0.1. However, noticing that the correct answer is negative, I took a shot and supplied an initial guess of -0.1, and that did converge to the correct result. It is certainly possible to change the code to try the negative of the original guess if the original guess fails. It solves this problem and doesn't break anything. I'm sure that's not the smartest possible approach, but I will at least have a PR out within a day or two that adopts that approach while I study the problem further. Note that the existing unit tests already have at least one example of a converging negative result even with the default guess.

[blacknell]

Firstly, thank you @oleibman for taking a look at this.

Indeed something has been miskeyed because when I re-keyed the above into a new Excel worksheet I get ​-0.64230761304498 - I didn't keep the workbook from before so I can't explain my error above.

In the library, when I enter -0.1 as an initial guess I too get -0.64230760881305. Weirdly, when this known result is used as an initial guess it also fails to converge.

I take your point about the nature of the proposed solution. I don't know how Excel converges but it is doing so with default 0.1 guess. I've rarely (if ever) seen it fail.

Would be good to have this scenario added to the test suite. Let me know if I can help