19 Replies Latest reply on Dec 3, 2013 12:12 PM by gkaiseril

    PMT Calculation in Acrobat Form

    teresamcgill

      I have a calculation I need adjusted from Excel...

       

      Here is the xls calc:

      =PMT(H16/12,300,-H13)

       

      H16 = intrest_rate

      H13 = total_loan

       

      Please help!

        • 1. Re: PMT Calculation in Acrobat Form
          gkaiseril MVP & Adobe Community Professional

          Acrobat JavaScript does not have all the functions that Excel has built-in, but if you know the formula you should be able to write a function that performs the same calculation.

           

          For the payment calculation one needs the rate per period for the loan, total number of payments, the present value of the loan, and optionally the future value of the loan and if the payments are paid currently or paid in arrears.

           

          Loan Payment

          • 2. Re: PMT Calculation in Acrobat Form
            tapehiss

            Somewhat helpful, but after hours of Googling about I still can't seem to recreate the above formula in my Adobe form either.

             

            I require the exact same thing; =PMT(H16/12,300,-H13)

             

             

            The Adobe simplified field notation equivalent of this would be my saviour.

            Any further help would be greatly appreciated!!

            • 3. Re: PMT Calculation in Acrobat Form
              gkaiseril MVP & Adobe Community Professional

              You need to use JavaScript to write the formula. The simplified field notation does not support the use of the JavaScript Math object which is needed for the exponentiation of 1 + r to the -n power.

               

              function PMT(r, Nper, Pv) {

              // compute the periodic payment amount using the follwing information;

              // r - interest rate per period;

              // Nper - total number of payments for the loan;

              // Pv - present value of the loan;

              var Payment = "";

              var nDivisor = 1 - Math.pow((1 + r), - Nper);

              if (nDivisor != 0) {

              Payment = (r * Pv) / (1 - Math.pow((1 + r), - Nper));

              } // end non-zero divisor;

              return Payment;

              } // end PMT function;

               

              I assume you do know how to write the necessary custom JavaScript to call the function and place the code for the function in a PDF.

               

              Entering Document Scripts

              • 4. Re: PMT Calculation in Acrobat Form
                tapehiss Level 1

                @GKaiseril,

                 

                Your help is very much appreciated, but unfortunately we can't assume anything when it comes to my skillset! Sum and simple calculations aren't a problem, but this particular one is a couple notches out of my league.

                 

                All I have at the moment are the four 'text fields' that makeup the formula PMT(r, Nper, Pv)

                 

                PMT = "payment_amount"

                r = "rate_of_borrowing"

                Nper = "number_of_payments"

                Pv = "net_finance"

                 

                What I should put in the custom calculation properties of the "payment_amount" field is the big mystery...

                • 5. Re: PMT Calculation in Acrobat Form
                  gkaiseril MVP & Adobe Community Professional

                  The custom calculation script for the payment field could look like:

                   

                  var AnnualRate = this.getField("rate_of_borrowing");

                  if(AnnualRate == null) app.alert("error intrest rate field");

                  var NumberPayments = this.getField("number_of_payments");

                  if(NumberPayments == null) app.alert("error getting number of payments field");

                  var PresentValue = this.getField("net_finance");

                  if(PresentValue == null) app.alert("error getting present value field");

                  event.value = PMT( AnnualRate.value / 12, NumberPayments.value, PresentValue.value);

                  • 6. Re: PMT Calculation in Acrobat Form
                    tapehiss Level 1

                    Well, it looks great.. and makes complete sense -- but no value is output to the payment_amount field. So issue not quite resolved...

                    • 7. Re: PMT Calculation in Acrobat Form
                      gkaiseril MVP & Adobe Community Professional

                      Here is a working example:

                       

                      Loan Payment Calculator

                      • 8. Re: PMT Calculation in Acrobat Form
                        tapehiss Level 1

                        As always, GKaiseril, you're help with my issue is much appreciated...

                         

                        But I've downloaded your working example and it doesn't appear to work for me. I'm at a loss..

                        • 9. Re: PMT Calculation in Acrobat Form
                          gkaiseril MVP & Adobe Community Professional

                          It must have been a bad load.

                           

                          Loan Payment Example

                          • 10. Re: PMT Calculation in Acrobat Form
                            tapehiss Level 1

                            Simply fantastic.

                             

                            Thank you kindly for your efforts - it's very much appreciated.

                            • 11. Re: PMT Calculation in Acrobat Form
                              tapehiss Level 1

                              Not to press my luck here... but let's just say I wanted to add a 'cost_of_borrowing' field... how exactly might I go about that?

                               

                              Adding a simple payment_amount*number_of_payments doesn't seem to do the trick...

                              • 12. Re: PMT Calculation in Acrobat Form
                                gkaiseril MVP & Adobe Community Professional

                                What do you mean by "cost of borrowing' field?

                                 

                                Is this a loan origination fee, a percentage of the face value of the loan, a commission, or something else?

                                 

                                The calculation provides one with the even payment amount of principal repayment and interest due for each period. The interest paid for a period in the interest due for the outstanding loan amount on for the specific period then principal repaid for that period is the payment amount less the interest due for that period.

                                 

                                So if one multiplies the periodic payment times the number of payments one will have the total principal repaid and the total interest paid. Subtracting the beginning principal from the total of all the payments should provide the total interest paid for the loan.

                                • 13. Re: PMT Calculation in Acrobat Form
                                  tapehiss Level 1

                                  I hear ya.  I'm just saying that when I add a new field and use the simple formula payment_amount*number_of_payments, that new field doesn't always display the correct amount. It will at first - but if I then go change a variable (number of payments or interest rate, let's say) it doesn't give me the new revised number... but rather continues to show the old/previous amount.

                                  • 14. Re: PMT Calculation in Acrobat Form
                                    gkaiseril MVP & Adobe Community Professional

                                    Check your field calculation order.

                                    • 15. Re: PMT Calculation in Acrobat Form
                                      teresamcgill Level 1

                                      Thank you so much! This helped immensely!

                                      • 16. Re: PMT Calculation in Acrobat Form
                                        tapehiss Level 1

                                        Any clue as to why that final calculation, (payment_amount*number_of_payments) is always a few cents off?? Is there some sort of rounding issue at play?

                                         

                                        Not to sound like a broken record, but all this help is greatly appreciated!

                                        • 17. Re: PMT Calculation in Acrobat Form
                                          gkaiseril MVP & Adobe Community Professional

                                          There has been no rounding applied to the computation. Now if you are looking at the displayed value then you are seeing a rounded value but the value used by JavaScript and for the form field is not rounded.

                                           

                                          You need to round the result of the function when setting the value of the field.

                                           

                                          // round value to 2 decimal places.

                                          event.value = Number(util.printf("%,1 .2f",PMT( AnnualRate.value / 12, NumberPayments.value, PresentValue.value)));

                                           

                                          It appears that Excel also does not round the result of their function.

                                           

                                          The result are the same as Excel to 10 decimal places.

                                          • 18. Re: PMT Calculation in Acrobat Form
                                            teresamcgill Level 1

                                            I'm baaaaack....

                                             

                                            I have another question for you. I'm modifying the calculation and splitting the loan into 'loan 1' and 'loan 2'.

                                             

                                            I have the loan payment calculator working, no problem, BUT how would I alter the code to reflect that the field below should be 75% of the total loan? (As 'loan 2' - a different form field - will represent the remaining 25%)

                                             

                                             

                                             

                                            //document level script;

                                            function PMT(r, Nper, Pv) {

                                            // compute the periodic payment amount using the follwing information;

                                            // r - interest rate per period;

                                            // Nper - total number of payments for the loan;

                                            // Pv - present value of the loan;

                                            var Payment = "";

                                            var nDivisor = 1 - Math.pow((1 + r), - Nper);

                                            if (nDivisor != 0) {

                                            Payment = (r * Pv) / (1 - Math.pow((1 + r), - Nper));

                                            } // end non-zero divisor;

                                            if(isNaN(Payment)) Payment = "";

                                            return Payment;

                                            } // end PMT function;

                                             

                                            function GetField(cName) {

                                            // return the field object for parameter cName;

                                            var oField = this.getField(cName);

                                            if (oField ==  null) {

                                            app.alert("Error accessing field \"" + cName + "\"", 0, 0);

                                            }

                                            return oField;

                                            } // end GetField function;

                                            // end document level script;

                                             

                                            var AnnualRate = GetField("rate_b");

                                            var NumberPayments = GetField("number_of_payments");

                                            var PresentValue = GetField("loan1_amt_2");

                                            if(PresentValue == null) app.alert("error getting present value field");

                                            event.value = PMT( AnnualRate.value / 12, NumberPayments.value, PresentValue.value);

                                            • 19. Re: PMT Calculation in Acrobat Form
                                              gkaiseril MVP & Adobe Community Professional

                                              It was written as a function, you just need to change the parameters used by the call of the function.