rory-h - 1 year ago 106
jQuery Question

# excel formula to javascript function

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.

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);``````

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download