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

Amazing code

It works.. alhamdulillaah.. save my day.. thanks thanks a lot.

Thanks alot. This save me lots of time!

Hi,
The above code fails in case the 'period' parameter values is more than 160 months. Can you please help with some fix for that.

@kucukharf could you comment on code license for this gist?

@kucukharf.
hope your are good. i am trying the convert the flat rate interest into reducing rate. But unable to do and difficult to.

1. How payments value was calculated. is it principal/number of terms.
2. I am trying this using java. could pls explain little more

Please see my below code
public double calculateRate(double nper, double pmt, double pv, double fv, double type, double guess) {
int FINANCIAL_MAX_ITERATIONS = 20;//Bet accuracy with 128
double FINANCIAL_PRECISION = 0.0000001;//1.0e-8

      double y, y0, y1, x0, x1 = 0, f = 0, i = 0;
      double rate = guess;
      if (Math.abs(rate) < FINANCIAL_PRECISION) {
         y = pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
      } else {
         f = Math.exp(nper * Math.log(1 + rate));
         y = pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
      }
      y0 = pv + pmt * nper + fv;
      y1 = pv * f + pmt * (1 / rate + type) * (f - 1) + fv;

      // 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 = pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
         } else {
            f = Math.exp(nper * Math.log(1 + rate));
            y = pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
         }

         y0 = y1;
         y1 = y;
         ++i;
      }
      return rate;
   }

example : User borrows money of $100,000 with 3 years and flat rate is 10%.

1. Nper = 3 * 12 = 36 months
2. How the calculate the monthly payments? which value i have to pass
3. PV = 100000
4. FV = 0
5. Type = 0
6. guess = 0

Please help to solve this and thanks in advance

When 0 as guess value, it returns NAN output.
When 0.1 as guess value, it returns rate as 15.196% Per year, which is not equal to excel rate function output (17.97)
please correct if i did any mistake

@kucukharf
It worked, I converted repayment periods in the wrong way. Thanks to everyone for your help and support.

awesome

nice

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.

Good work 😎

@kucukhharf can you please explain how to use this ?.
i am not able to understand what you mean bye present,future and type?!

@kucukharf

This is a wonderful code. Thank you for sharing with us. I used your code to calculate RATE. I get the results right when I tried upto 24 months period. I'm getting different results when I tried with 36 months (or increments of 12 months from there.. I.e 36...48...60...72 etc). Please correct me if I'm doing anything wrong?

iterMax = 128;
epsMax = 1e-10

double periods = 36; // nper - The total number of payment periods
double payment = 1260; // pmt - The payment made each period
double present = -15800; // pv - The present value, or total value of all loan payments now
double fv = 0 //[optional] The future value, or desired cash balance after last payment.
double type = 0 //[optional] When payments are due. 0 = end of period. 1 = beginning of period.
double guess = 0.01 //[optional] Your guess on the rate. Default is 10%.

double db = RATE(periods, payment, present, future, type, guess);

Here are my results:

For periods = 12
Excel result is -0.00670388916144098
Code result is -0.00670388916144098
Test Passed

For periods = 24
Excel result is 0.060092108011840846
Code result is 0.060092108011840846
Test Passed

For periods = 36
Excel result is 0.882612632222412
Code result is -1.4302980543374155
Failed for me.

It starts to fail from 36 months onwards for me. I know I'm making some silly mistake. I couldnt figure it out. Would you be able to help? I appreciate your help.

Magnifico

It's not worked for me with the below value.
Loan Amount = 358374.96, period = 360 , monthly payment =2214.19

Output = 1.83%
Expected Result = 6.28%

@kucukharf

Great job!!

saved my day, thanks!

wow this is amazing, you save my day. thank you for sharing

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