rory-h - 1 year ago 60

jQuery Question

This is the excel formula that I have

`fx = ROUNDUP(C17/((1-(1/(1+(C19/12))^(C18*12)))/(C19/12)),0)`

This is the result should be :

This is the javascript function that I wrote:

`function refreshRenoLoanCalculator() {`

var amt = parseInt($('.calc-renoloan .principal-amount').val().replace(/,/g, ''), 10)

console.log(amt); //10 000

var rate = $('.calc-renoloan .loan-rate').val();

console.log(rate); //0.0444

var tenure = $('.calc-renoloan .loan-tenure').val();

console.log(tenure); // 5

var a = rate / 100. / 12.;

console.log(a);

var b = 1. + a;

b = Math.pow(b, (tenure * 12)) - 1.;

console.log(b);

var FC = a / b + a;

FC = FC.toFixed(10);

console.log(FC);

var RP = amt * FC;

console.log(RP);

toolsSetCalculatedValue('.calc-renoloan .calc-result-installment', Math.ceil(RP).toLocaleString().split('.')[0])

$('.calc-renoloan .results-container').slideDown();

}

At the moment the result that I'm getting from the excel formula and the javascript function is not the same. Any help would be greatly appreciated.

Answer Source

In JS the equivalent calculation would be this:

```
var C17 = 10000;
var C18 = 5;
var C19 = 4.44;
var interest = C19 / 1200;
var foo = Math.ceil(C17 * interest / (1 - (Math.pow(1/(1 + interest), C18 * 12))));
console.log(foo);
```

The main difference is the multiplication of the interest rate (`* 1200`

as opposed to `* 12`

) as Excel stores percentage values as floating points between `0`

and `1`

.

If you'd prefer to calculate in the same manner as Excel, then you need to convert the input rate to this:

```
var C17 = 10000;
var C18 = 5;
var C19 = 0.0444; // Note the difference here
var interest = C19 / 12; // and here
var foo = Math.ceil(C17 * interest / (1 - (Math.pow(1 / (1 + interest), C18 * 12))));
console.log(foo);
```