Converting Excel formula into JavaScript format

Question

I'm trying to convert an Excel formula into jQuery.

Excel Formula:a1 * (a2/ 12) / (1 - (1 + (a2/ 12)) ^ -(a3* 12));

However, I'm stuck where the operator "^" came out. I researched Math.pow but I'm not sure how I'm gonna set values inside that method

So far, I have this

 var temp = $('#slider1').slider('value'); //value is 16000
 var temp2 = $('#slider2').slider('value'); //value is 7
 var temp3 = 3.85;
finalval = Math.pow(temp * (temp3 / 12) / (1 - (1 + (temp3 / 12))), -(temp2 * 12));
 $('#output').text(finalval);

I'm not really familiar with Math functions, can

Instead of trying to have everything in one calculations, it makes it easier to keep track of everything if you have several intermediate calculations. — Teepeemm
– Teepeemm, Commented Jul 1, 2015 at 0:52

tbm0115 · Accepted Answer · 2015-07-01 14:27:28Z

4

Take a look at W3Schools, but I'm pretty sure your issue is the location of your Math.pow()

Maybe try: final = Math.pow(amount * (temp / 12) / (1 - (1 + (temp / 12))), -(temp2 * 12))

Also, you were missing a parenthesis

Update After further review of the calculation (and reviewing comments), I'm pretty sure there's an error in the way we interpreted the calculation. See this page

The formula should be as follows:



    var amount = 16000; //Currency amount of a loan
    var interest = 0.0325; //Percent of interest per period in decimal form
    var years = 7; //Number of periods, this will be elongated into months in the equation.
                   //Time should be in similar units in equation.
    temp1 = amount * (interest / 12) / (1 - Math.pow(1 + (interest / 12), -(years * 12)));

Then you can apply the value to your text (according to the JSFiddle provided) using:



    $('#monthly').text(Math.round(temp1))

edited Jul 1, 2015 at 14:27

answered Jun 30, 2015 at 23:04

tbm0115

41911 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Christine Damon Over a year ago

I tried your suggestion and check it with the latest fiddle, it wasn't returning the correct value. Let's have an assumption, where amount=16000, interest = 3.25, years = 7. I'm getting value of 43333, it should be 2132. Do you think the format of the translated formula is the cause?

tbm0115 Over a year ago

Is your interest rate in decimal form? Try 0.0325 instead?

tbm0115 Over a year ago

I just plugged in the values you provided (16,000; 0.0325 {3.25%}; and 7 years) and got a value of $213.22 per month. This seems accurate to me as it results in a total of $17,910.48 over the length of the term. Usually these type of equations require percentages to be in decimal form and not the "whole number" form. I'll adjust my answer to include the variables here and comment the purpose of each variable.

Christine Damon Over a year ago

Converting 3.25 into 0.0325 fixed it. :)

Dekel · Accepted Answer · 2015-06-30 23:03:48Z

2

Unlike Excel, Math.pow is a function who gets two arguments - the base and the exponent: http://www.w3schools.com/jsref/jsref_pow.asp

Your problematic line should look like this:

 final = Math.pow(amount * (temp / 12) / (1 - (1 + (temp / 12))), -(temp2 * 12));

answered Jun 30, 2015 at 23:03

Dekel

62.9k12 gold badges109 silver badges130 bronze badges

7 Comments

Christine Damon Over a year ago

Now I'm having trouble with datatypes. Which variable is perfect to use together with Math.pow for displaying whole numbers?

Dekel Over a year ago

Not sure I understand your problem. Can you give some more information?

Christine Damon Over a year ago

Hi Dekel, thanks for quick response. variable final returns a value 4.484155085839392e-188, I think I'm not getting the right output and format. I want to display it as a whole number

Dekel Over a year ago

Can you also let us know the values of amount/temp/temp2? What are the expected values?

Dekel Over a year ago

Without the values for temp and temp2 it's impossible to help. You can add a comment inside your code for the values you check with.

|

Collectives™ on Stack Overflow

Converting Excel formula into JavaScript format

2 Answers 2

4 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related