2

I am trying to rebuild the Excel formula "Rate" in APEX. This is a formula for calculating the interest percentage of a loan, based on the number of periods, payment amount and total amount.

It's obviously not possible in a formula field because it involves an iterative process for guessing the percentage and then trying to get closer to it in each step.

This is the definition of the Rate function:

RATE(nper, pmt, pv, [fv], [type], [guess])

  • Nper Required. The total number of payment periods in an annuity.
  • Pmt Required. The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.
  • Pv Required. The present value — the total amount that a series of future payments is worth now.
  • Fv Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
  • Type Optional. The number 0 or 1 and indicates when payments are due.
  • guess Optional. Your guess of what the rate will be
3

Here's the code I came up with, based on some examples in javascript I found online. I tested it for a number of different scenarios and was able to get the same results as in Excel.

public class MathUtils {

    public static Decimal Rate(Integer periods, Decimal payment, Decimal present, Decimal future, Decimal type, Decimal guess) {
        guess = (guess == null) ? 0.01 : guess;
        future = (future == null) ? 0 : future;
        type = (type == null) ? 0 : type;

        // Set maximum epsilon for end of iteration
        Double epsMax = 0.00004539992;

        // Set maximum number of iterations
        Integer iterMax = 20;

        // Implement Newton's method
        Double y = 0;
        Double y0 = 0;
        Double y1 = 0;
        Double x0 = 0;
        Double x1 = 0;
        Double f = 0;
        Double i = 0;
        Double 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;
    }    

}
3
  • Its not working when number of month is 360 or more I am getting erro: Cannot represent Double 'NaN' as a Decimal At line y = present * f + payment * (1 / rate + type) * (f - 1) + future; Can anyone help me out? Value: =RATE(360, 1950.71,-288000, null, null, null) Thanks in advance
    – Foram
    Jul 31 '20 at 6:15
  • Indeed Newton's method has some flaws which, in combination with the limitations of APEX (i.e. max accuracy of Double), can lead to errors like this one. See also here: en.wikipedia.org/wiki/Newton%27s_method Aug 3 '20 at 13:38
  • You could potentially solve this by providing a guess that is of better quality then the default 0.01 (which is equal to 1%). E.g. 0.005 will work. However, I'm not sure if this will work for your scenario. I'm afraid I don't have a full solution available. That will require some complex workarounds, like described in the wikipedia article. Aug 3 '20 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.