asked on

JavaScript Calculation not correct

Good Afternoon,

I am designing a form in LiveCycle Designer ES2 and am having an issue with my JavaScript code for some calculations with the Interest Amount, which I can’t seem to figure out.

The calculation is as follows:

For EUR and USD:
(Principal Amount * (settlement date – maturity date) / 360 * Total Interest Rate) = Interest Amount

Example:
(165,802.00 * (jan 21-apr 21) /360(basis)*0.9519%) = 398.95

For GBP:
(Principal Amount * (settlement date – maturity date) / 365 * Total Interest Rate) = Interest Amount

Example:
(165,802.00 * 91 (jan 21-apr 21) /365(basis)*0.9519%) = 393.50

When I input the values in to my Adobe form for EUR/USD I am getting the below Interest Amount which is incorrect.

Below is the interest amount when GBP is selected, which is also incorrect.

Using the same formula in Excel, the calculations are displaying correctly.

Is someone able to see what I am missing in my calculation in my JavaScript code?

Code for the Interest Amount Calculation is below:

FORM.Page1.SubfrmDEP.InterestAmt::calculate - (JavaScript, client)

    //transform fields to dates
   var regularSettDate = new Date(SettlementDate.rawValue.replace(/-/g, "/"));
   var regularMatDate = new Date(MaturityDate.rawValue.replace(/-/g, "/"));
   
   // Get difference between dates in milliseconds
   var milliseconds = regularMatDate.getTime() - regularSettDate.getTime();
   
   // Define number of milliseconds in one day
   var nMilliSecondsPerDay = 24 * 60 * 60 * 1000;

   // Get difference in days
   var days = milliseconds / nMilliSecondsPerDay; 
      
   // Calculate Interest Amount depending on currency
   if (PrincipalCurr.rawValue == "GBP") 
   {
   this.rawValue = PrincipalAmt.rawValue * TotalInterestRate.rawValue / 365 * days;
   }
   else 
   {
   this.rawValue = PrincipalAmt.rawValue * TotalInterestRate.rawValue / 360 * days;
   }

Open in new window

Full code for this form is below:

 FORM.Page1.SubfrmDEP.MaturityDate::exit - (JavaScript, client)
if (this.errorText)
this.rawValue=null;

 FORM.Page1.SubfrmDEP.SettlementDate::exit - (JavaScript, client)
if (this.errorText)
this.rawValue = null;

  FORM.Page1.SubfrmDEP.InterestAmt::calculate - (JavaScript, client)

    //transform fields to dates
   var regularSettDate = new Date(SettlementDate.rawValue.replace(/-/g, "/"));
   var regularMatDate = new Date(MaturityDate.rawValue.replace(/-/g, "/"));
   
   // Get difference between dates in milliseconds
   var milliseconds = regularMatDate.getTime() - regularSettDate.getTime();
   
   // Define number of milliseconds in one day
   var nMilliSecondsPerDay = 24 * 60 * 60 * 1000;

   // Get difference in days
   var days = milliseconds / nMilliSecondsPerDay; 
      
   // Calculate Interest Amount depending on currency
   if (PrincipalCurr.rawValue == "GBP") 
   {
   this.rawValue = PrincipalAmt.rawValue * TotalInterestRate.rawValue / 365 * days;
   }
   else 
   {
   this.rawValue = PrincipalAmt.rawValue * TotalInterestRate.rawValue / 360 * days;
   }
   

 FORM.Page1.SubfrmDEP.TotalAmt::calculate - (JavaScript, client)

TotalAmt.rawValue = PrincipalAmt.rawValue + InterestAmt.rawValue

 FORM.Page1.SubfrmDEP.TotalInterestRate::calculate - (FormCalc, client)

TotalInterestRate.rawValue = InterestRateAmt.rawValue + SpreadAmt.rawValue

 FORM.Page1.SubfrmDEP.ValueDate::calculate - (FormCalc, client)
if (SettlementDate.rawValue == null) then
$.rawValue = ""
else
Num2Date(Date2Num(SettlementDate.formattedValue, DateFmt(3)) +0, DateFmt(3))
endif


 FORM.Page1.SubfrmDEP.InterestRateAmt::validate - (JavaScript, client)

if (this.rawValue != null) {
 this.rawValue = this.rawValue;
 if (this.rawValue >= 1 || this.rawValue <= 1) {
  this.rawValue = this.rawValue / 100;
 }
}
if (this.rawValue == null || this.rawValue <= 1) 
 true;
else
 false;

 FORM.Page1.SubfrmDEP.SpreadAmt::validate - (JavaScript, client)

if (this.rawValue != null) {
 this.rawValue = this.rawValue;
 if (this.rawValue >= 1 || this.rawValue <= 1) {
  this.rawValue = this.rawValue / 100;
 }
}
if (this.rawValue == null || this.rawValue <= 1) 
 true;
else
 false;

Open in new window

Kindly advise.

Regards,
N

SOLUTION

zephyr_hex (Megan)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Manuel Marienne-Duchêne

perhaps read that

http://floating-point-gui.de/

zephyr_hex (Megan)

@manumd - it would be more helpful for you to explain what you mean with your link. It's not entirely clear from your response what you're referring to. Also, links can rot over time and can stop working. If someone else has a similar problem, your response loses all meaning if the link stops working.

Kevin

ASKER

@zephyr_hex - Thank you for your response and taking time to assist me.

To be honest I don't really know much about JavaScript and have what I have from reading around in forums etc.

Back when I first created this form, I read somewhere that the JavaScript used in Adobe LiveCycle Designer is a little different (being it only recognizes certain parameters) compared to the full language. I am not sure how true it is, but this is why I am including the .getTime() for that line.

Also, as said previously I am currently using Adobe LiveCycle Designer to create my form. I’ve looked around but don’t believe such a console exists in the application to see what value each of the variables have in them when the code is run.

Nevertheless, I have attempted to change the code around as per your suggestions.

Kindly see below tests and results.

The below test I have commented out the unused lines and added the line of code you suggested. However as you can see from the screenshots, the Interest Amount is not calculated at all.

For the second test I reverted my code back to it’s original state and for the days variable I included the Math.floor function.

However the interest amount is off by a few dollars now, instead of just a few cents when the function was not included.

For my third test, I had a look again at the excel formula that the accounting department sent me.

Row 8 (calculating the interest amount) is using the formula =+D1*(D4-D3)*D7/365

This time I attempted to change the formula around as close to what the formula is in the excel example.

However this doesn’t seem to be making much of a difference, as the interest amount displays the same results as my original code.

Kindly advise.

Regards,
N

zephyr_hex (Megan)

You would use the console when viewing your form in a web browser. In most browsers F12 brings up the developer tools and the console is there. It's almost impossible for us to assist with troubleshooting your problem because we don't have your data set.

ASKER CERTIFIED SOLUTION

Robert Schutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin

ASKER

Good Morning,

@zephry_hex

To my knowledge my form is not web based. The way the form is used is that it is saved in a share on the network, which the user opens by double-clicking on the filename.pdf file from within Windows Explorer.

Apologies for not mentioning this in my original post.

@Robert Schutt

Thank you very much Robert for testing the code and providing your solutions.

Using the Math.round function was still not calculating correctly. Probably because it was rounding the amount, which is understandable (https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Global_Objects/Math/round).

What worked, was point one of your solution, with the timezone.

See my test below which is calculating everything correctly, now that the UTC timezone has been specified.

@zephry_hex & @Robert Schutt

Thank you both for your time and assistance.

Kind Regards,
N