How Can I Use the Excel Rate Function Correctly in PHP?

Question

The last time I posted this question I got spanked by you guys because it was not clear what I was asking…I blew it, sorry. So let me try again.

I am trying to mimic the Excel Rate function in PHP. I did obtain some code that does function correctly for other Excel functions but is erratic with the Rate function.

There are two steps involved:

1. Calculate the payment

PMT($rate/12, $nper*12, $pv, $fv, $type)
$rate = .08 // interest rate
$nper = 30 // loan term in years
$pv = (100000 + 4000) // loan amt + loan fees
$fv = 0
$type = 0
PMT = -$763.12 // my PHP result and Excel result

2. Calculate the rate

RATE($nper, $pmt, $pv, $fv, $type, $guess)
$nper = 360
$pmt = -763.12 // must remain negative in formula
$pv = 100000 // loan amt only (excludes loan fees)
$fv = 0
$type = 0
$guess = 0.09 // excel default is 0.10 but it doesn’t work so I lowered
RATE = 0.00701507 // Excel result (correct)
RATE = 0.0048541 // my PHP result (incorrect)

Okay, here’s the catch. When the interest rate is changed from 0.08 to 0.07 the result is a lower PMT of $691.91 which then makes both RATE’s almost identical.

RATE = 0.0061609831 // Excel
RATE = 0.0061609269 // my PHP

My question is this: Have any of you used the Excel RATE function in PHP? Were you successful then please share your formula with me. Or did you encounter this problem and find a way to fix it then please tell me what you did. Thank you.

Possible duplicate of [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) — miken32, Nov 30 '18 at 00:07

score 2 · Answer 1 · answered Jan 17 '18 at 16:59

Check out this solution:

<?php
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;
    $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;
}
$nper = 60;
$pmt = 93.22;
$pv = -5000;
$fv = 0;
$type = 0;
$guess = 0.09;
var_dump(RATE($nper, $pmt, $pv, $fv, $guess));
?>

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

Do you know that in PHP (and some other languages) floor((0.1+0.7)*10) equals 7 and not 8?

I'm pretty sure it is all because of this "nuance": http://php.net/manual/en/language.types.float.php

Instead of using float numbers in your calculations, consider using BCMath functions, which guarantee high precision as all numbers are represented as strings: http://php.net/manual/en/book.bc.php

Related: PHP - Floating Number Precision

edited May 23 '17 at 12:27

Community

1
1

answered Jul 15 '11 at 00:41

LazyOne

158,824
45
388
391

Thank you, LazyOne. I will explore those links. I'm a newbie, though, so it might take awhile for me. – James Kobzeff Jul 15 '11 at 13:01

How Can I Use the Excel Rate Function Correctly in PHP?

2 Answers2