Pre-populate Loan Payment Schedule

Introduction

The JavaScript function populate is designed to automate calculating and updating a loan payment schedule within a Joget form grid.

How does it work?

1. Retrieve Loan Details: The function starts by fetching the current values of loan amount, interest rate, and monthly payment amount from the form inputs using FormUtil.getValue.

2. Define Variables and Grid Configuration: It identifies the grid where the payment schedule will be populated (payment_schedule) and retrieves the function associated with adding new entries to this grid.

3. Calculation Loop: The function uses a while loop to calculate the details of each payment until the entire loan amount is paid off. It calculates the interest for each month, the principal amount paid, and the balance after each payment. The loop continues until the loan balance is zero.

   • Interest Calculation: Interest for the month is calculated as a percentage of the current balance.
   • Principal Calculation: The principal paid is the difference between the total monthly payment and the interest.
   • Balance Update: The remaining loan balance is updated by subtracting the principal from the current balance.
     
     

4. Date Handling: It calculates the payment date for each sequence by incrementing the month in the current date.

5. Edge Cases: The function handles scenarios where the monthly payment is less than the interest (which would prevent the loan from being paid off) and adjusts the final payment to bring the balance to zero if necessary.

6. Update Form Grid: For each payment cycle, it constructs a JSON object containing all payment details and uses the grid's add function to insert this data into the payment schedule grid.

7. UI Handling: Alerts and UI blocking mechanisms are used to manage user interaction and feedback during calculation.

Here is the complete code that implements the calculation and updating of the payment schedule in your Joget form:

<script type="text/javascript">
    function populate(){
         
        var loanAmount = parseFloat(FormUtil.getValue("loan_amount"));
        var loanInterest = parseFloat(FormUtil.getValue("loan_interest"));
        var loanMonthly = parseFloat(FormUtil.getValue("loan_monthly"));
         
        var field = FormUtil.getField("payment_schedule");
        var functionName = window[field.attr("id") + "_add"];
        if(typeof functionName === 'function') {
         
            var seq = 1;
            while(loanAmount>0){
                var balanceBefore = loanAmount;
                var interest = loanAmount * loanInterest/100 / 12;
                var principal = loanMonthly - interest;
                var balanceAfter = loanAmount - principal;
                loanAmount = balanceAfter;
                 
                var currentDate = new Date();
                currentDate.setMonth(currentDate.getMonth() + seq);
                var paymentDate = currentDate.toISOString().slice(0, 10);
         
                if(interest > loanMonthly){
                    alert("Monthly repayment must be higher than interest of " + interest);
                    break;
                }
                 
                if(balanceAfter<0){
                    loanMonthly += balanceAfter;
                    balanceAfter = 0;
                    principal = loanMonthly - interest;
                }
                 
                var args = new Object();
                args['result'] = '{"payment_seq":"'+ seq +'","balance_before":"' + balanceBefore + '","balance_after":"","payment_date":"' + paymentDate + '","payment_amount":"' + loanMonthly + '","payment_interest":"' + interest + '","payment_principal":"' + principal + '","balance_after":"' + balanceAfter + '"}';
                functionName(args);
                 
                seq++;
            }
        }
        $.unblockUI();
    }
</script>
    <button onClick="$.blockUI(); setTimeout('populate()',500); return false; "><strong style="font-size:130%">Populate</strong></button>

This script should be included in your Joget application's form configuration to enable dynamic creation and updating of payment schedules based on user-entered loan details.

Related documentation

•  Form Grid

Download sample app