Geek Answers Handbook

Restore Excel RATE function using Newton's method

I am working on converting a mortgage calculator to PHP, but I don't necessarily need a PHP solution. I am looking for the logic needed to replicate an Excel RATE function. I found a solution that uses bisection, and, if worse, I use this.

I know that someone out there in the interwebs world knows about such a function, so I would like to have an easy answer instead of creating a solution from scratch.

Literature:

thanks

+6
function php excel financial
source share
3 answers

Implementation of the MS Excel RATE () function using the secant method (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()
+8
source share

I tried using the code above, but the results just don't match Excel (or Google Spreadsheet).

I don’t know if I still need to implement this function, but in any case I looked at how this algorithm was built, and although I could not access the excel source code (or google worksheet), I found that it is not simple payment. You can read about this math here:

https://brownmath.com/bsci/loan.htm#Eq8

A function in PHP might look something like this:

 function rate($nprest, $vlrparc, $vp, $guess = 0.25) { $maxit = 100; $precision = 14; $guess = round($guess,$precision); for ($i=0 ; $i<$maxit ; $i++) { $divdnd = $vlrparc - ( $vlrparc * (pow(1 + $guess , -$nprest)) ) - ($vp * $guess); $divisor = $nprest * $vlrparc * pow(1 + $guess , (-$nprest - 1)) - $vp; $newguess = $guess - ( $divdnd / $divisor ); $newguess = round($newguess, $precision); if ($newguess == $guess) { return $newguess; } else { $guess = $newguess; } } return null; }
0
source share

For Laravel use the same function, but you remove the definition

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

and financial_max_iterations = 20; → the same Excel

The code is:

 function RATE($nper, $pmt, $pv, $fv = 0.0, $type = 0, $guess = 0.1) { $financial_max_iterations = 20; $financial_precision = 0.00000008; $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; }

it worked for me

0
source share

More articles:


All Articles