The excel RATE() function in Javascript

rate_function_excel.js

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

@1FahadShakeel ... thanks for providing this! It takes a much simpler, more straightforward approach to the RATE function than the Newton-Raphson approach I saw elsewhere... on which I noticed your comment and thus found my way here. :-)

However, I also found a couple of cases where your code fails to produce the correct value:

• it fails when pv<0 and pmt>0 (Excel's RATE works in both cases)
• it will not find negative rates (Excel's RATE will return negative rates if the sum of all payments do not at least meet the value)

I fixed these problems by inserting the following at line 15:

  var npv = pv < 0; // present value is NEGATIVE
  var nrt = Math.abs(nper * pmt) < Math.abs(pv) // searching for a NEGATIVE rate
  if (nrt) {
    guess = -guess;
    highLimit = -highLimit;
  }

... and then changing line 37 (the else if) to...

      } else if ((npv !== nrt) ? balance + fv < 0 : balance + fv > 0) {

Sorry for the somewhat-ugly hack, but it now passes all my test cases. :-P Thanks again for providing this code!

P.S. Edited to add comments in my code snippet :-)

the result is always null for me, i already set the times to find a solution for a higher number. Can you help?