0

Im trying to use an excel formula into my javascript code but cannot get the correct result, here is the formula in excel:

(C4-(C8/C6))*(1-((1+(C5/12))^-C6))/(C5/12)+C8+C7

and how i try it in javascript:

(737-(50000/300))*(1-(Math.pow((1+(0.02/12)),-300)))/(0.02/12)+50000+10000

or

(737-(50000/300))*(1-((1+(0.02/12))**-300))/(0.02/12)+50000+10000

Both attempt give me same result but this result is not the same as in excel, any idea?

Javascript give me 194558.80507389922 but excel give me 191516,666

enter image description here

Christophe
  • 145
  • 1
  • 1
  • 12

2 Answers2

1

When I try it myself in Excel, the formula produces 194559,8051 (same as JS).

When I change C5 from 0,02 to 0,022 then the formula produces 191516,666 (same as your Excel).

Conclusion -> your C5 is actually 0,022, or something very close to it. Please make sure your cells are set to show as many decimals as needed to avoid display-only rounding.

Peter B
  • 22,460
  • 5
  • 32
  • 69
  • Thanks a lot, you are right, switched the cells format to 3 decimals and it was 0.022.. thanks you for the help! – Christophe Sep 21 '20 at 09:37
0

Same result to the 4 decimals my Excel rounded with default and same result to 7 decimals

NOTE: Is floating point math broken?

const C4 = 737;
const C5 = 0.02;
const C6 = 300;
const C7 = 10000;
const C8 = 50000;

// (C4-(C8/C6))*(1-((1+(C5/12))^-C6))/(C5/12)+C8+C7         = 194558,8051 in my Office 365 Excel
                                                           // 194558,805073901 - with 9 decimals
console.log(
(C4-(C8/C6))*(1-(Math.pow((1+(C5/12)),-C6)))/(C5/12)+C8+C7 // 194558.80507389922
)

console.log(
(C4-(C8/C6))*(1-((1+(C5/12))**-C6))/(C5/12)+C8+C7          // 194558.80507389922
)
mplungjan
  • 169,008
  • 28
  • 173
  • 236
  • Thanks for the answer, im indeed getting 194558.80507389922 but what bother me is that excel give me 191516,666 ... adding the screen in initial message – Christophe Sep 21 '20 at 09:03
  • I got 194558,805073901 in my Excel Office 365 with the values I displayed and your formula – mplungjan Sep 21 '20 at 09:08