PHPExcel based function RATE() returning NAN()

Question

I have this code: http://pastebin.com/Sd9WKZFr

When i call something like rate(60, -6000, 120000) it returns me a NAN result, but the same function on MS Excel returns me 0,04678.... I have the same problem trying -5000, -4000, -3000 and -2000.

When i debug the code, i see that about the 8/9 iteration, the line number 29 begins to return a NAN result, making all of other results to turn NAN too.

BUT, when i call something like rate(60, -1000, 120000) it returns me a float -0.02044..., exactly the same result of MS Excel.

I have already tryed to convert all of math calculations into BCMath functions, but this way the results of -6000 is wrong (-1.0427... instead of 0,04678...) but using -1000 the result is correct, matching excel's result.

Is there a way to make it work correctly?

Thanks in advance for any useful sight about that.

this are just guessings, this looks primarily as a calculation problem. - is it possible $rate can get negative so log() is undefined in line 29? - are your brackets set correctly? — worenga, Apr 07 '12 at 01:16
Hi @mightyuhu, there are no sintaxe errors cause using -1000 it works fine. At the same time, it is a function from stable phpexcel. Thank u anyway. — sdlins, Apr 07 '12 at 01:38
@sidtj @mightyuhu is right, `rate(60, -6000, 120000)` produces an intermediate result of Rate < 1 (you have already identified this at the 8/9th iteration). You need to work on your algorithm — chris neilsen, Apr 07 '12 at 02:16
Sorry, It seems I misunderstood @mightyuhu. But, i really cant figure out what should I do. I have nor even a minimal idea. Could you help? What exactly should i change in my algorithm? And why that works fine in Excel? (=rate(60, -6000, 120000) — sdlins, Apr 07 '12 at 02:37

Mark Baker · Accepted Answer · 2012-04-07T19:55:37.957

2

I'll need to do some tests to ensure that there's no adverse effects in other situations; but the following looks as though it might fix this problem, and certainly calculates the correct rate value for your arguments RATE(60, -6000, 120000) stabilises at 0.046781916422493 in iteration 15.

define('FINANCIAL_MAX_ITERATIONS', 128); 
define('FINANCIAL_PRECISION', 1.0e-08); 


function RATE($nper, $pmt, $pv, $fv = 0.0, $type = 0, $guess = 0.1) { 

    $rate = $guess; 
    if (abs($rate) < FINANCIAL_PRECISION) { 
        $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv; 
    } else { 
        $f = exp($nper * log(1 + $rate)); 
        $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv; 
    } 
    $y0 = $pv + $pmt * $nper + $fv; 
    $y1 = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv; 

    // find root by secant method 
    $i  = $x0 = 0.0; 
    $x1 = $rate; 
    while ((abs($y0 - $y1) > FINANCIAL_PRECISION) && ($i < FINANCIAL_MAX_ITERATIONS)) { 
        $rate = ($y1 * $x0 - $y0 * $x1) / ($y1 - $y0); 
        $x0 = $x1; 
        $x1 = $rate;
        if (($nper * abs($pmt)) > ($pv - $fv))
            $x1 = abs($x1);

        if (abs($rate) < FINANCIAL_PRECISION) { 
            $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv; 
        } else { 
            $f = exp($nper * log(1 + $rate)); 
            $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv; 
        } 

        $y0 = $y1; 
        $y1 = $y; 
        ++$i; 
    } 
    return $rate; 
}   //  function RATE()

edited Apr 07 '12 at 19:55

answered Apr 07 '12 at 16:05

Mark Baker

209,507
32
346
385

Hi Baker, i was about to send you this link in the phpexcel forum when i saw who had sent it: you yourself. THank you so much. I will try in some different circunstances before accept your answer. Thank you. – sdlins Apr 07 '12 at 17:23
Already done some testing: it doesn't work with your rate(60, -1000, 120000) example, investigating further – Mark Baker Apr 07 '12 at 17:38
What a pitty. Using 60, -6000, 120000 i get 0.046781916422493, the same result of the LibreOffice Calc (Excel cant do this, returning #NUM!). But lower values (-1000, -2000, etc) this function, now using abs(), really pass to return wrong results. Thank you for your valuable efforts. – sdlins Apr 07 '12 at 18:11
@sidtj - See edited version for a change that will work with both your examples – Mark Baker Apr 07 '12 at 19:56
Thank you so much, Barker! I tryed with many values and it works till the max -8000. Values -9000, -10000 and on yet does not work. Maybe you wanna fix that for the next phpexcel release. But for my needs, it is working fine now. Thanks again. – sdlins Apr 07 '12 at 20:12
In the long term, I'll probably switch to using Newton Raphson rather than secant; but in the meanwhile this simple change will appear in the next PHPExcel release... there's still cases that it doesn't resolve, but it does work with more cases than the unchanged version. – Mark Baker Apr 07 '12 at 20:50
Ok. PHPExcel is an excellent piece of code! Congrats and may all of you keep the good work! – sdlins Apr 07 '12 at 21:59

score 1 · Answer 2 · answered Apr 08 '12 at 00:32

I would not suggest using Secant method to find internal rate of return as it consumes more time than other preferred iterative methods such as Newton Raphson method. From the code it seems setting a maximum of 128 iterations is a waste of time

Using Newton Raphson method to find RATE with either of the two TVM equations takes only 3 iterations

TVM Eq. 1: PV(1+i)^N + PMT(1+i*type)[(1+i)^N -1]/i + FV = 0

f(i) = 0 + -6000 * (1 + i * 0) [(1+i)^60 - 1)]/i + 120000 * (1+i)^60

f'(i) = (-6000 * ( 60 * i * (1 + i)^(59+0) - (1 + i)^60) + 1) / (i * i)) + 60 * 120000 * (1+0.05)^59

i0 = 0.05
f(i1) = 120000
f'(i1) = 42430046.1459
i1 = 0.05 - 120000/42430046.1459 = 0.0471718154728
Error Bound = 0.0471718154728 - 0.05 = 0.002828 > 0.000001

i1 = 0.0471718154728
f(i2) = 12884.8972
f'(i2) = 33595275.7358
i2 = 0.0471718154728 - 12884.8972/33595275.7358 = 0.0467882824629
Error Bound = 0.0467882824629 - 0.0471718154728 = 0.000384 > 0.000001

i2 = 0.0467882824629
f(i3) = 206.9714
f'(i3) = 32520602.801
i3 = 0.0467882824629 - 206.9714/32520602.801 = 0.0467819181458
Error Bound = 0.0467819181458 - 0.0467882824629 = 6.0E-6 > 0.000001

i3 = 0.0467819181458
f(i4) = 0.056
f'(i4) = 32503002.4159
i4 = 0.0467819181458 - 0.056/32503002.4159 = 0.0467819164225
Error Bound = 0.0467819164225 - 0.0467819181458 = 0 < 0.000001
IRR = 4.68%


TVM Eq. 2: PV + PMT(1+i*type)[1-{(1+i)^-N}]/i + FV(1+i)^-N = 0

f(i) = 120000 + -6000 * (1 + i * 0) [1 - (1+i)^-60)]/i + 0 * (1+i)^-60

f'(i) = (--6000 * (1+i)^-60 * ((1+i)^60 - 60 * i - 1) /(i*i)) + (0 * -60 * (1+i)^(-60-1))

i0 = 0.05
f(i1) = 6424.2628
f'(i1) = 1886058.972
i1 = 0.05 - 6424.2628/1886058.972 = 0.0465938165535
Error Bound = 0.0465938165535 - 0.05 = 0.003406 > 0.000001

i1 = 0.0465938165535
f(i2) = -394.592
f'(i2) = 2081246.2069
i2 = 0.0465938165535 - -394.592/2081246.2069 = 0.046783410646
Error Bound = 0.046783410646 - 0.0465938165535 = 0.00019 > 0.000001

i2 = 0.046783410646
f(i3) = 3.1258
f'(i3) = 2069722.0554
i3 = 0.046783410646 - 3.1258/2069722.0554 = 0.0467819004105
Error Bound = 0.0467819004105 - 0.046783410646 = 2.0E-6 > 0.000001

i3 = 0.0467819004105
f(i4) = -0.0335
f'(i4) = 2069813.5309
i4 = 0.0467819004105 - -0.0335/2069813.5309 = 0.0467819165937
Error Bound = 0.0467819165937 - 0.0467819004105 = 0 < 0.000001
IRR = 4.68%

PHPExcel based function RATE() returning NAN()

2 Answers2

Linked