financial rate function in javascript not working properly

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

JavaScript
​x
 
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);
}

JavaScript
 
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>

JavaScript
 
<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>
​

Answer

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

JavaScript
 
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

JavaScript
 
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
​

Advertisement

Answer