基于PHPExcel的函数RATE()返回NAN() [英] PHPExcel based function RATE() returning NAN()

问题描述

我有以下代码: http://pastebin.com/Sd9WKZFr

当我调用类似rate(60, -6000, 120000)的名称时，它返回给我NAN的结果，但是MS Excel上的相同函数返回了我0,04678....我在尝试-5000，-4000，-3000和-2000时遇到相同的问题.

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.

当我调试代码时，我看到大约8/9迭代，第29行开始返回NAN结果，其他所有结果也都变为NAN.

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.

但是，当我调用类似rate(60, -1000, 120000)之类的东西时，它会返回float -0.02044...，与MS Excel完全相同.

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

我已经尝试将 all 个数学计算转换为 BCMath函数，但是这样-6000的结果是错误的(-1.0427 ...而不是0 ，04678 ...)，但使用-1000的结果正确，与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.

推荐答案

我需要做一些测试，以确保在其他情况下没有不利影响；但是以下内容似乎可以解决此问题，并且肯定会为您的参数RATE(60，-6000，120000)计算正确的比率值，在迭代15中稳定在0.046781916422493.

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() 

这篇关于基于PHPExcel的函数RATE()返回NAN()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！