Creating a custom JavaScript calculation based on complex Excel formula

[Whimsical_partner3838]

I have a complex Excel formula that needs to be converted to JavaScript to create a custom calculation on the "Pepare a Form" tool. I have no experience with JS.

I am trying to calculate how much I need to save each month based on my different savings goals. Note, that the field amounts will be variable, and do not want to have to write a code for each box of the monthly contribution required.

Example amounts are:

Goal amount (J17)	$10,000
Current balance (J18)	$1,000
Needed in x years (J19)	1
Needed in x months (J20)	6
Expected annual interest/return (J21)	4%

The excel formula to find out how I need to save each month is: 

=IF(J21=0,(J17-J18)/((J19*12)+J20),((J17-(J18*(1+J21/12)^(J20 +(J19*12))))*(J21/12))/((1+J21/12)^(J20+(J19*12))-1))

It the formula needs to have the output (based on the example amounts provided) of 482.65.

Then should be rounded to the next nearest whole number. The excel formula for that is: =ROUND(J24,0) with the output of 483. Note that J24=482.65.

Thank you for your help!!

[Nesa Nurani · Accepted solution]

To enter 4 and get 4% use this as 'Validate' script:
if(event.value)event.value = Number(event.value)/100;

[Whimsical_partner3838]

Here is my form if needed for JS creation.

[Edited by Moderator]

[PDF Automation Station]

What is the compounding frequency - annually or monthly?  Is the ROR field formatted as a percent?

[Whimsical_partner3838]

The rate of return is an annual percentage. It is formatted as a percentage, but the user of this form would need to enter it in as .04 instead of entering 4 for 4%. I would like it to be just entering it as 4 and that is the percentage, but I dont know how to do that either.

[Nesa Nurani · Accepted solution]

To enter 4 and get 4% use this as 'Validate' script:
if(event.value)event.value = Number(event.value)/100;

[Nesa Nurani · Accepted solution]

See if this works for you:

var goalAmount = this.getField("Goal Amount 1").valueAsString;
var currentBalance = this.getField("Current Balance 1").valueAsString;
var years = this.getField("Year 1").valueAsString;
var months = this.getField("Month 1").valueAsString;
var annualReturnRate = this.getField("Annual Return Rate 1").valueAsString;

if (goalAmount === "" || currentBalance === "" || years === "" || months === "" || annualReturnRate === "") {
 event.value = "";} 
else {
 var totalMonths = (Number(years) * 12) + Number(months);

 if (annualReturnRate == 0) {
  event.value = Math.round((Number(goalAmount) - Number(currentBalance)) / totalMonths);} 
 else {
  var monthlyReturnRate = Number(annualReturnRate) / 12;
  var balanceGrowth = Number(currentBalance) * Math.pow((1 + monthlyReturnRate), totalMonths);
  event.value = Math.round(((Number(goalAmount) - balanceGrowth) * monthlyReturnRate) / (Math.pow((1 + monthlyReturnRate), totalMonths) - 1));}}

[Whimsical_partner3838]

That works thank you!