Short description:

The rate function calculates the rate neccessary to reach the future value.

nper - number of periods (number of periods) pmt - payment开发者_Python百科s (every month or year) pv - present value (the initial amount) fv - future value (the amount to reach)

Question:

I have used the funtion Rate (php code below) and i have some troubles with it. I use it with the following values:

Wrong

 Excel - RATE(228;-196,02;-49005;961546,464646;1;0,05) gives: 0,0119
 Php   - RATE(228,-196.02,-49005,961546.464646,1,0.05) gives: 0.0022

Good

 Excel - RATE(228;-196,02;-49005;161546,464646;1;0,05) gives:0,003
 Php   - RATE(228,-196.02,-49005,161546.464646,1,0.05) gives:0.003

It looks like that when the difference between $nper, $pv, $pmt and $fv becomes bigger the code goes wrong.

Does anyone know what the problem is? Thanks in advance.

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


function rate($nper, $pmt, $pv, $fv, $type, $guess) 
{

    $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;
} 

---

The code is using the secant method to find the rate of return, I am not sure why the code is not working properly but a better way to implement the Excel RATE function is done with Newton Raphson Method

I will give you the equation that Excel uses for the TVM functions and you can implement the Newton Raphson method by making function f(x) out of this equation

From there you will need to get a derivative of f(x) namely f'(x) and follow the instructions on this page about IRR. The page describes finding IRR with Newton Raphson method but you can replace function f(x) and f'(x) in the article with the following function

f(x) = PV (1+RATE)^NPER + PMT(1+RATE*TYPE)[{(1+RATE)^NPER-1}/RATE] + FV

Now it is up to you to find f'(x) and use the steps outline for using the Newton Raphson Method

This Excel RATE calculator is implemented in PHP using Newton Raphson method so you can use your test data to see if you get the required results. Do note that this calculator follows the US and UK notation for numbers and period is used to represent decimal point unlike the comma used in Europe