Excel RATE() Javascript Function

Finance.excel.rate.js

/*!
 * @fileOverview Finance Excel Rate Formula Javascript Equivalent
 * @version 1.0.0
 *
 * @author Burak Arslan @kucukharf http://www.github.com/kucukharf
 * @license
 * Copyright (c) 2010-2018 Burak Arslan
 * Licensed under Creative Commons (CC) license
 * @usage RATE($periods, $payment, $present, $future, $type, $guess)
 */

RATE = function(periods, payment, present, future, type, guess) {
    guess = (guess === undefined) ? 0.01 : guess;
    future = (future === undefined) ? 0 : future;
    type = (type === undefined) ? 0 : type;
  
    // Set maximum epsilon for end of iteration
    var epsMax = 1e-10;
  
    // Set maximum number of iterations
    var iterMax = 10;
  
    // Implement Newton's method
    var y, y0, y1, x0, x1 = 0,
      f = 0,
      i = 0;
    var rate = guess;
    if (Math.abs(rate) < epsMax) {
      y = present * (1 + periods * rate) + payment * (1 + rate * type) * periods + future;
    } else {
      f = Math.exp(periods * Math.log(1 + rate));
      y = present * f + payment * (1 / rate + type) * (f - 1) + future;
    }
    y0 = present + payment * periods + future;
    y1 = present * f + payment * (1 / rate + type) * (f - 1) + future;
    i = x0 = 0;
    x1 = rate;
    while ((Math.abs(y0 - y1) > epsMax) && (i < iterMax)) {
      rate = (y1 * x0 - y0 * x1) / (y1 - y0);
      x0 = x1;
      x1 = rate;
        if (Math.abs(rate) < epsMax) {
          y = present * (1 + periods * rate) + payment * (1 + rate * type) * periods + future;
        } else {
          f = Math.exp(periods * Math.log(1 + rate));
          y = present * f + payment * (1 / rate + type) * (f - 1) + future;
        }
      y0 = y1;
      y1 = y;
      ++i;
    }
    return rate;
};

By the way... one bug in the above code happens when it fails to converge on a solution within iterMax iterations. In this case, it still returns its current estimate as if that's the answer, even though that answer did NOT meet the desired epsilon. In my experience, this "wrong" answer is hard to detect, because a lot of the time, the above code is returning something that is "close enough" in a given situation -- it did not converge on a value out to 10 decimal places, but maybe it was correct to 2 decimal places, and that's all anybody checked. In this case, increasing iterMax from 10 to 20 will cause it to return the right value more often, but still not all the time!

A fix for this is straightforward. At line 52, instead of just returning rate, insert this block of code that returns null instead of an incorrect answer. (Callers should also check if the result === null and, in that case, handle the error appropriately. And now that you will easily be able to see when it failed to find an accurate answer, increasing epsMax or further increasing iterMax may make sense as potential workarounds.)

  if (i >= iterMax) {
    return null;
  }
  return rate;

Also, it's a good idea IMO to check (again, just before returning rate) for the NaN error I mentioned in a comment above, because that can also cause the return of a very wrong value:

  if (Number.isNaN(y)) { // apparent bug or failure in this implementation of Newton's method!
    rate = null // avoid returning an answer that is likely nowhere NEAR correct... any ideas how to fix this bug?
  }

In my own code, I'm experimenting with falling back to a simple iterative approach when this NaN error occurs. But that's super ugly. I hope somebody is able to suggest a real fix for that bug.

thanks

You really save my day.

hello
periods = 24
payment = -12600
present = 68310

gives -1.4630839294992086
on excel gives 0.1810537

any help