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

@agha-mirum ... I found the version @1FahadShakeel mentioned above, here.

His version does not try to use the Newton-Raphson approach of this one -- it uses a much more straight-forward iterative approach where it does the estimation based on a kind of 'binary search' of possible values. I noted a couple small bugs in his version as well, but since it is more straightforward, they are much easier to understand and fix than the bugs in this one, IMO. I posted my suggested fix on that gist linked above.

The Newton-Raphson approach favored by this gist is nice because many times, it can converge on a value much more quickly (in a lot fewer iterations) than a traditional binary search-based approach. For this algorithm, I find iterMax==10 worked a lot of the time, but sometimes iterMax==20 was needed when the number of periods starts to get higher. Whereas in the more straightforward approach mentioned in that other gist, you really need to allow up to 40 iterations to converge on a solution.

Another difference between this gist an that one is the epsilon (epsMax) value. This gist uses 1e-10 -- basically comparing the results out to 9-10 decimal places. The other gist essentially uses Math.abs(0.00000005 * payment) as its epsilon, meaning, if payment amounts are bigger, it doesn't bother going out to so many probably-less-important decimal places.

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