rory-h rory-h - 4 months ago 15
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)


excel formula

This is the result should be :
the result

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

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

Comments