CODEWITHSUNDEEP
javascriptjqueryexcelmath
ProEvilz
ProEvilz

Reputation: 5455

Converting excel formulae to JS?

I have this formula and it uses powers e.g. '10 to the power 30'

=B2*(B3/12) / (1-(1+(B3/12)) ^ -B6)

The problem is that I can't put ^ in JS/jQuery, I have to use Math.pow() as the equivalent.

I've tried quite a few different positions but I can't seem to get it correct, the brackets really confuse me. Which parts of the formula would I wrap the .pow within?

My attempt: =B2*(B3/12) / Math.pow( (1-(1+(B3/12)) , -B6) )

UPDATE: Please see this Fiddle

Fiddle code:

var B2 = 500000; //£500,000
var B3 = 2.00; //2.00%
var B6 = 300; //Total number of payments

var original_formula =  'B2*(B3/12)/(1-(1+(B3/12))^-B6)';

var x = B2 * (B3 / 12) / Math.pow(1 - (1 + (B3 / 12)), -B6)

console.log(x);

//Expected/Excel result = 2,119.27
//Actual result  = 2.9884337938456618e-229 <- what's going on here?

Upvotes: 0

Views: 313

Answers (2)

Lutz Lehmann
Lutz Lehmann

Reputation: 25992

=B2*(B3/12) / (1-(1+(B3/12)) ^ -B6)

is the same as

=B2*B3/12 / ( 1 - (1+B3/12) ^ -B6 )

With this reduced number of parantheses it is perhaps easier to see that the javascript implementation should be

= B2*B3/12 / ( 1 - Math.pow(1+B3/12, -B6) )

You also need to translate B3=2.00% into B3=2.00/100 or B3=0.02. Then the result is indeed 2119.2716932202998

var B2 = 500000; //£500,000
var B3 = 2.00; //2.00%
var B6 = 300; //Total number of payments

var original_formula =  'B2*(B3/12)/(1-(1+(B3/12))^-B6)';

B3 = B3/100 // 2.0% is 0.02 as number
var x = B2*B3/12 / (1 - Math.pow(1 + B3/12, -B6) )

var log = document.getElementById("log")
log.innerHTML += "x="+x;
<div id="log"></div>

Upvotes: 1

katniss.everbean
katniss.everbean

Reputation: 550

Have not tried this, but one problem is probably that you're wrapping the two arguments in Math.pow() with a set parentheses.

In your original formula, you wrap the base and exponent in parentheses to group them together:

(1 - (1 + (B3 / 12)) ^ -B6)

Math.pow() takes two arguments, the base, and the exponent: Math.pow(base, exponent)

You can't use parentheses across two arguments in Javascript, and in fact you don't need to since the pow() function knows that the two arguments should be used together.

So instead of:

Math.pow((1 - (1 + (B3 / 12)), -B6))

I think that should be more like:

Math.pow(1 - (1 + (B3 / 12)), -B6)

Upvotes: 2

Related Questions