使用牛顿法重新创建Excel RATE函数 [英] Recreate Excel RATE function using Newton's Method
问题描述
我正在使用PHP转换抵押计算器,但不一定需要PHP解决方案。我正在寻找复制Excel RATE
函数所需的逻辑。我找到了一个使用二等分的解决方案,如果更糟糕的是,我使用这个。
I'm working on converting a mortgage calculator in PHP, but I don't necessarily need a PHP solution. I'm looking for the logic needed to replicate the Excel RATE
function. I've found a solution which uses bisection, and if worse comes to worse, I use that.
我知道这个世界上有一个人知道这样一个功能,所以我希望有一个简单的答案,而不是从头开始创建一个解决方案。
I know someone out there in the interwebs world has knowledge of such a function, so I'd love to have an easy answer instead of creating a solution from scratch.
参考文献:
- http:// office。 microsoft.com/en-us/excel-help/rate-HP005209232.aspx
- http://en.wikipedia.org/wiki/Newton%27s_method
- http://office.microsoft.com/en-us/excel-help/rate-HP005209232.aspx
- http://en.wikipedia.org/wiki/Newton%27s_method
谢谢
推荐答案
使用割线法(牛顿法的有限差分近似法)实现MS Excel RATE()函数来自PHPExcel:
Implementation of the MS Excel RATE() function using the secant method (a finite difference approximation of Newton's method) taken from PHPExcel:
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 (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()
这篇关于使用牛顿法重新创建Excel RATE函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!