Creating a custom JavaScript calculation based on complex Excel formula

Question

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 Answer

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

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 · Answer

Here is my form if needed for JS creation. [Edited by Moderator]

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded