financial rate function in javascript not working properly

Question

the function given at simple financial rate function in javascript is not giving me same answers as excel rate function some time. It works perfectly for the problem given at http://allfinancialmatters.com/2009/11/03/how-to-use-the-rate-function-in-excel/ but for my test cases. its results are different from excel rate. this is strange behaviour. i am unable to sort this out. my test cases (with excel output) are

RATE(360,-665.3, 99000)    = 0.0059
RATE(360,-958.63, 192000)    =0.0036
RATE(180,-1302.96,192000)    = 0.0023
RATE(360, -889.19, 192000) =0.00312
RATE(360, -1145.8, 240000) = 0.0033

my code.js is

function rate(paymentsPerYear, paymentAmount, presentValue, futureValue, dueEndOrBeginning, interest)
{
    //If interest, futureValue, dueEndorBeginning was not set, set now
    //if (interest == null) // not working here :D 
    if (isNaN(interest))
        interest = 0.1;
        //interest = 0.1;

    if (isNaN(futureValue))
        futureValue = 0;

    if (isNaN(dueEndOrBeginning))
        dueEndOrBeginning = 0;

    var FINANCIAL_MAX_ITERATIONS = 128;//Bet accuracy with 128
    var FINANCIAL_PRECISION = 0.0000001;//1.0e-8

    var y, y0, y1, x0, x1 = 0, f = 0, i = 0;
    var rate = interest; // initiallizing rate to our guess interest 
    if (Math.abs(rate) < FINANCIAL_PRECISION)
    {
        y = presentValue * (1 + paymentsPerYear * rate) + paymentAmount * (1 + rate * dueEndOrBeginning) * paymentsPerYear + futureValue;
    }
    else
    {
        f = Math.exp(paymentsPerYear * Math.log(1 + rate));
        y = presentValue * f + paymentAmount * (1 / rate + dueEndOrBeginning) * (f - 1) + futureValue;
    }
    y0 = presentValue + paymentAmount * paymentsPerYear + futureValue;
    y1 = presentValue * f + paymentAmount * (1 / rate + dueEndOrBeginning) * (f - 1) + futureValue;

    // find root by Newton secant method
    i = x0 = 0.0;
    x1 = rate;
    while ((Math.abs(y0 - y1) > FINANCIAL_PRECISION)
        && (i < FINANCIAL_MAX_ITERATIONS))
    {
        rate = (y1 * x0 - y0 * x1) / (y1 - y0);
        x0 = x1;
        x1 = rate;

        if (Math.abs(rate) < FINANCIAL_PRECISION)
        {
            y = presentValue * (1 + paymentsPerYear * rate) + paymentAmount * (1 + rate * dueEndOrBeginning) * paymentsPerYear + futureValue;
        }
        else
        {
            f = Math.exp(paymentsPerYear * Math.log(1 + rate));
            y = presentValue * f + paymentAmount * (1 / rate + dueEndOrBeginning) * (f - 1) + futureValue;
        }

        y0 = y1;
        y1 = y;
        ++i;
    }
    return rate;
    //return String(parseFloat(rate).toFixed(3)); // rounding it to 3 decimal places
    //return parseFloat(rate).toFixed(3);
}

and my HTML file is

<head><title>JavaScript Loan Calculator</title>
<script src="code.js"></script>
</head>
<body bgcolor="white">

<form name="loandata">
  <table>

    <tr>
      <td>1)</td>
      <td>paymentsPerYear:</td>
      <td><input type="text" name="paymentsPerYear" size="12" 
                 onchange="calculate();"></td>
    </tr>
    <tr>
      <td>2)</td>
      <td>paymentAmount:</td>
      <td><input type="text" name="paymentAmount" size="12" 
                 onchange="calculate();"></td>
    </tr>
    <tr>
      <td>3)</td>
      <td>presentValue:</td>
      <td><input type="text" name="presentValue" size="12" 
                 onchange="calculate();"></td>
    </tr>


    <tr>
      <td>4)</td>
      <td>futureValue:</td>
      <td><input type="text" name="futureValue" size="12"></td>
    </tr>
    <tr>
      <td>5)</td>
      <td>dueEndOrBeginning:</td>
      <td><input type="text" name="dueEndOrBeginning" size="12"></td>
    </tr>
    <tr>
      <td>6)</td>
      <td>interest:</td>
      <td><input type="text" name="interest" size="12"></td>
    </tr>
    <tr><td colspan="3">
      <input type="button" value="Compute" onClick="calculate();">
    </td></tr>
        <tr>

      <td>APR:</td>
      <td><input type="text" name="APR" id="APR" size="12"></td>
    </tr>
  </table>
</form>


<script language="JavaScript">
function calculate() {
    var paymentsPerYear = document.loandata.paymentsPerYear.value;
    var paymentAmount = document.loandata.paymentAmount.value;
    var presentValue = document.loandata.presentValue.value;
    var futureValue = document.loandata.futureValue.value;
    var dueEndOrBeginning = document.loandata.dueEndOrBeginning.value ;
    var interest = document.loandata.interest.value ;
    var ans = rate(parseFloat(paymentsPerYear), parseFloat(paymentAmount), parseFloat(presentValue), parseFloat(futureValue), parseFloat(dueEndOrBeginning), parseFloat(interest));
    document.loandata.APR.value=ans;
    //alert(futureValue);


}

</script>
</body>
</html>

_"not giving me same answers as excel rate function some time"_ - So different answers as in off by a couple of decimal places, or radically different? — nnnnnn, Nov 09 '13 at 22:00
radially different and some time exact... for my test cases. answers are wrong with js file.. but with the data given at http://allfinancialmatters.com/2009/11/03/how-to-use-the-rate-function-in-excel/ , my answer with it is consistent — Abdul Rauf, Nov 09 '13 at 22:06
need to isolate values that create differing results...then create some simple test arrays with those, use breakpoints in your code to walk through each step and see what might be throwing them off — charlietfl, Nov 09 '13 at 22:22
At first glance, the line `var FINANCIAL_MAX_ITERATIONS = 128;//Bet accuracy with 128` is wrong: "If the successive results of RATE do not converge to within 0.0000001 after 20 iterations" http://office.microsoft.com/en-gb/excel-help/rate-function-HA102752889.aspx — SheetJS, Nov 18 '13 at 00:41
@AbdulRaufMujahid Did you get a consistent result of this question? — Programmeur, Jun 01 '16 at 18:49

score 5 · Accepted Answer · answered Aug 18 '16 at 23:23

In case anyone is still looking for a javascript implementation of the Excel's Rate function, this is what I came up with:

var rate = function(nper, pmt, pv, fv, type, guess) {
    // Sets default values for missing parameters
    fv = typeof fv !== 'undefined' ? fv : 0;
    type = typeof type !== 'undefined' ? type : 0;
    guess = typeof guess !== 'undefined' ? guess : 0.1;

    // Sets the limits for possible guesses to any
    // number between 0% and 100%
    var lowLimit = 0;
    var highLimit = 1;

   // Defines a tolerance of up to +/- 0.00005% of pmt, to accept
   // the solution as valid.
   var tolerance = Math.abs(0.00000005 * pmt);

   // Tries at most 40 times to find a solution within the tolerance.
   for (var i = 0; i < 40; i++) {
       // Resets the balance to the original pv.
       var balance = pv;

       // Calculates the balance at the end of the loan, based
       // on loan conditions.
       for (var j = 0; j < nper; j++ ) {
           if (type == 0) {
               // Interests applied before payment
               balance = balance * (1 + guess) + pmt;
           } else {
               // Payments applied before insterests
               balance = (balance + pmt) * (1 + guess);
           }
       }

       // Returns the guess if balance is within tolerance.  If not, adjusts
       // the limits and starts with a new guess.
       if (Math.abs(balance + fv) < tolerance) {
           return guess;
       } else if (balance + fv > 0) {
           // Sets a new highLimit knowing that
           // the current guess was too big.
           highLimit = guess;
       } else  {
           // Sets a new lowLimit knowing that
           // the current guess was too small.
           lowLimit = guess;
       }

       // Calculates the new guess.
       guess = (highLimit + lowLimit) / 2;
   }

   // Returns null if no acceptable result was found after 40 tries.
   return null;
};

Testing the function with Abdul's test cases give the following results:

rate(360,-665.3, 99000);
0.005916521358085446

rate(360,-958.63, 192000);
0.0036458502960158515

rate(180,-1302.96,192000);
0.0022917255526408564

rate(360, -889.19, 192000);
0.0031250616819306744

rate(360, -1145.8, 240000);
0.003333353153720964

financial rate function in javascript not working properly

1 Answers1