Excel ROUND function in Javascript

Question

I've got a mathematical formula in Excel, which is the following:

ROUND((B2+C2)*(B55/100)/12;2)

Initial values:

B2 = 1000
C2 = 0
B55 = 0,03

Results (t means time in months).

Here is my Javascript approach:

(function _calculateRates() {
  var singlePayment = parseInt(1000, 10),
    amount = singlePayment,
    monthlyPayment = parseInt(0, 10),
    investTime = parseFloat(12),
    rate_a = parseFloat(0.03),
    rate_b = parseFloat(0.03),
    investment = monthlyPayment,
    interest = 0;

  for (var month = 0; month < investTime; month += 1) {
    investment = (month === 0) ? 0 : monthlyPayment;

    interest = Number(((amount + investment) * (rate_a / 100) / 12).toFixed(2));
    amount = Number((amount + interest + investment).toFixed(2));
  }
  console.log('Result: ', amount);
})();

As one can see, the result is not correct.

Where can I find the Microsoft Excel algorithm for ROUND() ?

What is the result that gets logged, and what result do you want instead? — Cat
– Cat, Commented Jan 29, 2019 at 8:14
In excel, is the argument separator the comma (,) or the semi-colon (;) ? what is excel showing as the result? It should come out as 2dp... — Solar Mike
– Solar Mike, Commented Jan 29, 2019 at 8:37
rounding to 2 decimals ... floor((100.0*x)+0.5)*0.01; for different number of places you just change the 100 and 0.01 for 10^n and 10^-n — Spektre
– Spektre, Commented Jan 29, 2019 at 8:41
@SolarMike the problem is 0.3 can not be represented inbinary as exact binary number ... its irrational in there so the value is slightly less then 0.3 leading to slight less than 0.025 result which get round down so its rounding error based... to remedy you can try this How to deal with overflow and underflow? — Spektre
– Spektre, Commented Jan 29, 2019 at 12:24

Axel Richter · Accepted Answer · 2019-01-30 06:37:55Z

In Excel =0.3/12 evaluates to 0.025. So rounded to 2 decimals it is 0.03.

In JavaScript var result = 0.3/12; results in 0.024999999999999998. That .toFixed(2) is 0.02.

Internally Excel also gets 0.024999999999999998 like all systems using IEEE Standard for Floating-Point Arithmetic (IEEE 754). But it has the additional rule, to only take 15 digits maximum. That is 0.02499999999999 + 0.000000000000009, which is 0.025.

So we cannot using .toFixed in JavaScript. If we are using another method for rounding in JavaScript the this leads to the same result as in Excel.

See example using simple values:

var result = 0.3/12;
console.log(result);
console.log(result.toFixed(2));
console.log(Math.floor((Math.pow(10, 2)*result)+0.5)*Math.pow(10, -2));

See example using your algorithm:

(function _calculateRates() {
  var singlePayment = parseInt(1000, 10),
    amount = singlePayment,
    monthlyPayment = parseInt(0, 10),
    investTime = parseFloat(12),
    rate_a = parseFloat(0.03),
    rate_b = parseFloat(0.03),
    investment = monthlyPayment,
    interest = 0;

  for (var month = 0; month < investTime; month += 1) {
    investment = (month === 0) ? 0 : monthlyPayment;

    interest = Number(((amount + investment) * (rate_a / 100) / 12));
    interest = Math.floor((Math.pow(10, 2)*interest)+0.5)*Math.pow(10, -2);
    amount = Number((amount + interest + investment));
    amount = Math.floor((Math.pow(10, 2)*amount)+0.5)*Math.pow(10, -2);
  }
  console.log('Result: ', amount);
})();

Because it is related to this question, especially why in JavaScript var result = 0.3/12; results in 0.024999999999999998, the link to What Every Programmer Should Know About Floating-Point Arithmetic could be helpful.

Collectives™ on Stack Overflow

Excel ROUND function in Javascript

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related