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

this code has some bugs in it, if you want the correct excel formula hit me up.

Beatiful ❤

This algorithm seems to do exceptionally well in many circumstances. However, there are definitely some situations where it seems to produce a wrong answer! And I'm not sure why. I did some reading on the Newton-Raphson method, but my math is rusty enough that I still couldn't fully understand how this is supposed to be working -- so I was unable to fix the bugs. But here are some of my test cases -- some of which pass, some of which fail:

    var tolerance = 1e-8
    // PASSING tests:
    expect(RATE( 60, -152.50,  8000)).to.be.approximately( 4.513488e-3, tolerance)
    expect(RATE(4*12, -200,    8000)).to.be.approximately( 7.701472e-3, tolerance)
    expect(RATE(360, -2214.19, 358374.96, 0, 0, 0.1)).to.be.approximately( 5.235535e-3, tolerance)
    expect(RATE( 12, -1260,   15800)).to.be.approximately(-6.703889e-3, tolerance)
    expect(RATE( 24, -1260,   15800)).to.be.approximately( 6.009211e-2, tolerance)
    // FAILING tests:
    expect(RATE(360, -2214.19, 358374.96, 0, 0, 0.01)).to.be.approximately( 5.235535e-3, tolerance)
    expect(RATE( 36, -1260,   15800)).to.be.approximately( 7.355105e-2, tolerance)
    expect(RATE(221, -606.49, 44503)).to.be.approximately( 1.281009e-2, tolerance)
    expect(RATE(222, -605.98, 44503)).to.be.approximately( 1.280956e-2, tolerance)
    expect(RATE(250, -594.15, 44503)).to.be.approximately( 1.279468e-2, tolerance)

Regarding failure of RATE(360, -2214.19, 358374.96, 0, 0, 0.01):
Thanks to user @RanaForam for this test case. Interestingly, it seems to fail because the "guess" of 0.01 (which is the default guess in the code) is so low. If you guess higher, at 0.1 (Excel's default, BTW), the algorithm finds the correct value.

Regarding failure of RATE( 36, -1260, 15800):
Thanks to user @sfdev2018 for this test case. It produces a value of 3.916865e-2 (3.9% monthly / 47% annual) instead of the correct value of 7.36% monthly/88.26% annual. No idea why that case produces the wrong answer (as compared to Excel) when similar case RATE( 24, -1260, 15800) succeeds.

Regarding the last 3 failures:
These were found by a user of the software I'm building. They produce the wrong value because, at line 45 in the code at a certain point during estimation, the value of "rate" becomes LESS THAN -1, causing Math.log(1 + rate) to return NaN, which cascades, causing y and then y1 to also be NaN, which unintentionally exits the while loop and causes an incorrect value to be returned. This is where I wish I could follow the logic of what is supposed to be happening, but I haven't managed to yet.

So... a huge thanks to @kucukharf for providing this code... it's an interesting starting point! But FYI for anybody hoping to rely on the code in any kind of a production environment... definitely needs some tweaking still. :-)

1FahadShakeel

Could you please share it, I have a problem when the period is increasing.

Thanks for all comments,

@SpainTrain feel free to use, hope it helps. @venkatesh1401 you sure incoming variable types are correct? @ChiragGupta-CL should increase epsMax val.

What epsMax values are recommended for such cases? for example period is increasing over 100.

@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