CODEWITHSUNDEEP
phpexcelfinancialxirr
gonel
gonel

Reputation: 1

Php financial XIRR not giving strange results

I'm using the XIRR function from the php financial library (http://www.phpclasses.org/package/892-PHP-Financial-functions-with-the-Excel-function-names-.html) but I get strange results with these values (dates are d/m/y):

(01/01/2014, -400) , (01/10/2014, 18)

MS Excel correctly returns 0.98, while the XIRR function returns -1.5714653207915E+40. The code is as follow:

$f->XIRR(array(-400,18), array( 
    mktime(0,0,0,1,1,2014), 
    mktime(0,0,0,10,1,2014),
    ), 0.1);

Can anyone explain me what am I doing wrong? Thanks in advance for any help.

Upvotes: 0

Views: 1945

Answers (3)

Jasmeen
Jasmeen

Reputation: 906

I have written one code from PHP Excel Functions. I have calculated XIRR and XNPV. Here is code sample with some dummy data.

Main advantage is that, There is no dependency on any library in this code.

<?php

$rate = 0.12;
$values = array(-5000,-3000,-8000,25000,-4000);
$dates = array('01-02-2015','05-05-2016','02-03-2018','03-03-2019','05-03-2019');


/** FINANCIAL_MAX_ITERATIONS */
define('FINANCIAL_MAX_ITERATIONS', 128);

/** FINANCIAL_PRECISION */
define('FINANCIAL_PRECISION', 1.0e-08);

$result = XIRR($values,$dates,0.1);
print_r($result);


 function XIRR($values, $dates, $guess = 0.1) { 
        $x1 = 0.0;
        $x2 = $guess;
        $f1 = XNPV($x1, $values, $dates);  
        $f2 = XNPV($x2, $values, $dates); 
        for ($i = 0; $i < 128; ++$i) {
            if (($f1 * $f2) < 0.0) break;
            if (abs($f1) < abs($f2)) {
                $f1 = XNPV($x1 += 1.6 * ($x1 - $x2), $values, $dates);
            } else {
                $f2 = XNPV($x2 += 1.6 * ($x2 - $x1), $values, $dates);
            }
        } 

        $f = XNPV($x1, $values, $dates);
        if ($f < 0.0) {
            $rtb = $x1;
            $dx = $x2 - $x1;
        } else {
            $rtb = $x2;
            $dx = $x1 - $x2;
        }

        for ($i = 0;  $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
            $dx *= 0.5;
            $x_mid = $rtb + $dx;
            $f_mid = XNPV($x_mid, $values, $dates);
            if ($f_mid <= 0.0) $rtb = $x_mid;

            if ((abs($f_mid) < FINANCIAL_PRECISION) || (abs($dx) < FINANCIAL_PRECISION)) return $x_mid;
        } 

    }



      function XNPV($rate, $values, $dates) {

        $valCount = count($values);  
        $xnpv = 0.0;
        for ($i = 0; $i < $valCount; ++$i) 
        { 

            $datediff = strtotime($dates[$i]) - strtotime($dates[0]);
            $datediff =  round($datediff / (60 * 60 * 24));  
            $xnpv += $values[$i] / pow(1 + $rate,$datediff / 365); 
        } 
        return $xnpv; 
    }

 ?>

Upvotes: 1

Scars
Scars

Reputation: 11

Use the American mm/dd/yyyy :

mktime(0,0,0,1,1,2014), 
mktime(0,0,0,1,10,2014)

Upvotes: 0

user3470881
user3470881

Reputation:

The correct XIRR value is -98.417% as shown below

-400 + 18(1+i)^-(273/365) = 0
18(1+i)^-(273/365) = 400
(1+i)^-(273/365) = 400/18
(1+i)^(273/365) = 18/400
(1+i) = (18/400)^(365/273)
1+i = (0.045)^(1.336996337)
i = (0.045)^(1.336996337) - 1
i = -0.984174769
i = -98.417%

Upvotes: 0

Related Questions