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, Jan 29 '19 at 08: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, Jan 29 '19 at 08: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, Jan 29 '19 at 08:41
In `Excel` `=0.3/12` evaluates to `0.025`. So rounded to 2 decimals is `0.03`. In `JavaScript` `var result = 0.3/12;` results in `0.024999999999999998`. That `.toFixed(2)` is `0.02`. That's why the difference. — Axel Richter, Jan 29 '19 at 08:44
@AxelRichter 12 / 3 is 4 and 3 / 12 is 0.25, so why does javascript have such an issue? — Solar Mike, Jan 29 '19 at 09:01
@Solar Mike: Not only `JavaScript` but all systems using [IEEE Standard for Floating-Point Arithmetic (IEEE 754)](https://en.wikipedia.org/wiki/IEEE_754) will have that "issue". `Excel` additional has accuracy of only 15 digits maximum. This looks like an advantage here but sometimes it is not. — Axel Richter, Jan 29 '19 at 09:08
@AxelRichter so that result is not "precision" it is "error"... — Solar Mike, Jan 29 '19 at 09:20
@Solar Mike: That's correct. All electronic systems we are using currently are **not** able doing floating point arithmetic in 100% accuracy. But this is as it is. We have to live with it. — Axel Richter, Jan 29 '19 at 09:24
@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?](https://stackoverflow.com/a/33006665/2521214) — Spektre, Jan 29 '19 at 12:24
@Spektre: I posted my answer in the hope someone would counter having a better `JavaScript` rounding approach than `Math.floor((Math.pow(10, n) * x ) + 0.5) * Math.pow(10, -n)`. This is really unwieldly, isn't it? — Axel Richter, Jan 29 '19 at 13:05

Axel Richter · Accepted Answer · 2019-01-30T06:37:55.787

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.

Excel ROUND function in Javascript

1 Answers1