GetExistingLoanSchedulesQuery

Overview

GetExistingLoanSchedulesQuery retrieves the actual repayment schedules for an existing disbursed loan account. This query returns comprehensive schedule information including expected amounts, paid amounts, due amounts, and payment state for each installment.

Key Difference

This query is for existing loans (already created and disbursed). For preview schedules before loan creation, use GenerateLoanSchedulesCommand.

When to Use

✅ Use GetExistingLoanSchedulesQuery for:

• Displaying loan payment schedules in customer portals
• Generating loan statements with payment history
• Tracking loan repayment progress
• Calculating outstanding balances per installment
• Building payment reminders for upcoming installments
• Creating loan dashboards with schedule status
• Exporting loan schedule reports

❌ Don't use for:

• Preview schedules before loan creation (use GenerateLoanSchedulesCommand)
• Creating new loan schedules (handled automatically during loan creation)
• Modifying loan schedules (use restructure/reschedule commands)

Syntax

Basic Usage (doBackend)

// Lookup by loan account number
var schedules = doCmd('GetExistingLoanSchedules', {
  accountNumber: 'LN00001234'
});

// Access summary data
var totalSchedules = schedules.summary.totalSchedules;
var paidSchedules = schedules.summary.paidSchedules;
var totalDue = schedules.summary.totalDue;

// Iterate through schedules
schedules.schedules.forEach(function(schedule) {
  console.log('Installment #' + schedule.installmentNumber);
  console.log('Due Date: ' + schedule.dueDate);
  console.log('Total Due: ' + schedule.totalDue);
});

Alternative Lookup Methods

// Lookup by encoded key
var schedules = doCmd('GetExistingLoanSchedules', {
  encodedKey: 'abc123def456'
});

// Lookup by loan ID  
var schedules = doCmd('GetExistingLoanSchedules', {
  id: 12345
});

Parameters

Parameter	Type	Required	Description
accountNumber	string	Yes*	Loan account number (e.g., "LN00001234")
encodedKey	string	Yes*	Loan encoded key (alternative lookup)
id	number	Yes*	Loan database ID (alternative lookup)
pageNumber	number	No	Page number for pagination (default: 1)
pageSize	number	No	Number of schedules per page (default: 50)
isExport	boolean	No	If true, returns all schedules without pagination (default: false)

* At least one identifier is required. The accountNumber is the most commonly used.

Response Structure

Response Object

The query returns a paginated response with the following structure:

{
  summary: {
    // Loan identification
    accountNumber: "LN00001234",
    loanEncodedKey: "abc123def456",
    loanAmount: 100000.00,
    currencyCode: "NGN",
    loanState: "Active",
    productName: "Personal Loan",
    
    // Schedule counts (all schedules, not just current page)
    totalSchedules: 12,
    paidSchedules: 3,
    overdueSchedules: 1,
    upcomingSchedules: 8,
    
    // Aggregate amounts (all schedules)
    totalExpected: 110000.00,
    totalPaid: 27500.00,
    totalDue: 82500.00
  },
  schedules: [
    // Array of schedule objects for current page (see below)
  ],
  
  // Pagination metadata
  pageNumber: 1,
  pageSize: 50,
  totalPages: 1,
  totalRecords: 12
}

Schedule Object

Each installment in the schedules array contains:

{
  // Identification
  id: 1001,
  encodedKey: "sch_abc123",
  dueDate: "2026-02-15T00:00:00",
  installmentNumber: 1,
  
  // Expected amounts (original schedule)
  principalExpected: 8333.33,
  interestExpected: 833.33,
  feesExpected: 0.00,
  penaltyExpected: 0.00,
  totalExpected: 9166.66,
  
  // Paid amounts
  principalPaid: 8333.33,
  interestPaid: 833.33,
  feesPaid: 0.00,
  penaltyPaid: 0.00,
  totalPaid: 9166.66,
  
  // Due amounts (expected - paid)
  principalDue: 0.00,
  interestDue: 0.00,
  feesDue: 0.00,
  penaltyDue: 0.00,
  totalDue: 0.00,
  
  // State information
  scheduleState: "Paid",
  scheduleStateDescription: "Paid in Full",
  balanceAfterPayment: 91666.67,
  
  // Timestamps
  createdDate: "2026-01-15T10:30:00",
  lastModifiedDate: "2026-02-15T14:20:00"
}

Schedule States

State	Description
Pending	Upcoming Payment - not yet due
Paid	Paid in Full - completely paid
Late	Overdue - past due date
NotDisbursed	Not Disbursed - loan not yet disbursed

Examples

Example 1: Display Payment Calendar

var schedules = doCmd('GetExistingLoanSchedules', {
  accountNumber: context.accountNumber
});

var calendar = [];

schedules.schedules.forEach(function(schedule) {
  calendar.push({
    installment: schedule.installmentNumber,
    dueDate: schedule.dueDate,
    amount: schedule.totalExpected,
    paid: schedule.totalPaid,
    status: schedule.scheduleStateDescription
  });
});

return {
  loanAccount: schedules.summary.accountNumber,
  calendar: calendar
};

Example 2: Calculate Next Payment

var schedules = doCmd('GetExistingLoanSchedules', {
  accountNumber: context.accountNumber
});

// Find next unpaid schedule
var nextPayment = null;

for (var i = 0; i < schedules.schedules.length; i++) {
  var schedule = schedules.schedules[i];
  if (schedule.totalDue > 0) {
    nextPayment = {
      installmentNumber: schedule.installmentNumber,
      dueDate: schedule.dueDate,
      amountDue: schedule.totalDue,
      isOverdue: schedule.scheduleState === 'Late'
    };
    break;
  }
}

return nextPayment;

Example 3: Generate Payment Summary Report

var schedules = doCmd('GetExistingLoanSchedules', {
  accountNumber: context.accountNumber
});

var summary = schedules.summary;

var report = {
  loanDetails: {
    accountNumber: summary.accountNumber,
    product: summary.productName,
    originalAmount: summary.loanAmount,
    currency: summary.currencyCode,
    currentBalance: summary.principalBalance
  },
  paymentProgress: {
    totalInstallments: summary.totalSchedules,
    paidInstallments: summary.paidSchedules,
    remainingInstallments: summary.upcomingSchedules,
    overdueInstallments: summary.overdueSchedules,
    completionPercentage: round((summary.paidSchedules / summary.totalSchedules) * 100, 2)
  },
  amounts: {
    totalExpected: summary.totalExpected,
    totalPaid: summary.totalPaid,
    totalOutstanding: summary.totalDue,
    paymentProgress: round((summary.totalPaid / summary.totalExpected) * 100, 2)
  }
};

return report;

Example 4: Check for Overdue Payments

var schedules = doCmd('GetExistingLoanSchedules', {
  accountNumber: context.accountNumber
});

var overduePayments = [];

schedules.schedules.forEach(function(schedule) {
  if (schedule.scheduleState === 'Late') {
    overduePayments.push({
      installment: schedule.installmentNumber,
      dueDate: schedule.dueDate,
      amountDue: schedule.totalDue,
      daysPastDue: calculateDaysPastDue(schedule.dueDate)
    });
  }
});

if (overduePayments.length > 0) {
  return {
    hasOverduePayments: true,
    overdueCount: overduePayments.length,
    overduePayments: overduePayments
  };
} else {
  return {
    hasOverduePayments: false,
    message: "All payments are up to date"
  };
}

function calculateDaysPastDue(dueDate) {
  var due = new Date(dueDate);
  var now = new Date();
  var diffTime = Math.abs(now - due);
  var diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
  return diffDays;
}

Example 5: Export to CSV Format

var schedules = doCmd('GetExistingLoanSchedules', {
  accountNumber: context.accountNumber
});

var csvRows = ['Installment,Due Date,Expected,Paid,Due,Status'];

schedules.schedules.forEach(function(schedule) {
  var row = [
    schedule.installmentNumber,
    schedule.dueDate,
    schedule.totalExpected.toFixed(2),
    schedule.totalPaid.toFixed(2),
    schedule.totalDue.toFixed(2),
    schedule.scheduleStateDescription
  ].join(',');
  
  csvRows.push(row);
});

return {
  csv: csvRows.join('\n'),
  filename: 'loan_schedule_' + schedules.summary.accountNumber + '.csv'
};

Example 6: Calculate Remaining Payments with Dates

var schedules = doCmd('GetExistingLoanSchedules', {
  accountNumber: context.accountNumber
});

var remainingPayments = [];
var totalRemaining = 0;

schedules.schedules.forEach(function(schedule) {
  if (schedule.scheduleState === 'Pending' || schedule.scheduleState === 'Late') {
    remainingPayments.push({
      installment: schedule.installmentNumber,
      dueDate: schedule.dueDate,
      principal: schedule.principalDue,
      interest: schedule.interestDue,
      total: schedule.totalDue
    });
    totalRemaining += schedule.totalDue;
  }
});

return {
  remainingPaymentCount: remainingPayments.length,
  totalAmountRemaining: totalRemaining,
  payments: remainingPayments
};

Example 7: Paginated Schedule Retrieval

For loans with many installments, use pagination to retrieve schedules in batches:

// Get first page (10 schedules per page)
var page1 = doCmd('GetExistingLoanSchedules', {
  accountNumber: 'LN00001234',
  pageNumber: 1,
  pageSize: 10
});

console.log('Total schedules:', page1.totalRecords);
console.log('Total pages:', page1.totalPages);
console.log('Current page:', page1.pageNumber);

// Process first batch
page1.schedules.forEach(function(schedule) {
  console.log('Schedule #' + schedule.Order + ' - Due: ' + schedule.dueDate);
});

// Get second page if available
if (page1.totalPages > 1) {
  var page2 = doCmd('GetExistingLoanSchedules', {
    accountNumber: 'LN00001234',
    pageNumber: 2,
    pageSize: 10
  });
  
  // Process second batch
  page2.schedules.forEach(function(schedule) {
    console.log('Schedule #' + schedule.Order + ' - Due: ' + schedule.dueDate);
  });
}

return {
  totalSchedules: page1.totalRecords,
  pagesProcessed: Math.min(2, page1.totalPages),
  summary: page1.summary
};

Example 8: Export All Schedules

To export all schedules without pagination:

var allSchedules = doCmd('GetExistingLoanSchedules', {
  accountNumber: context.accountNumber,
  isExport: true  // Returns all schedules
});

// Generate CSV data
var csvData = 'Installment,Due Date,Expected,Paid,Balance,Status\n';

allSchedules.schedules.forEach(function(schedule) {
  csvData += schedule.Order + ',';
  csvData += schedule.dueDate + ',';
  csvData += schedule.totalExpected + ',';
  csvData += schedule.totalPaid + ',';
  csvData += schedule.totalDue + ',';
  csvData += schedule.scheduleStateDescription + '\n';
});

return {
  csvData: csvData,
  totalRecords: allSchedules.totalRecords,
  filename: 'loan_' + context.accountNumber + '_schedules.csv'
};

Error Handling

try {
  var schedules = doCmd('GetExistingLoanSchedules', {
    accountNumber: context.accountNumber
  });
  
  if (!schedules || !schedules.schedules) {
    return {
      success: false,
      message: "No schedules found for this loan"
    };
  }
  
  return {
    success: true,
    data: schedules
  };
  
} catch (error) {
  return {
    success: false,
    error: error.message
  };
}

Common Error Codes

Error Code	Message	Solution
MISSING_LOAN_IDENTIFIER	Please provide accountNumber, loanEncodedKey, or loanId	Include at least one loan identifier
LOAN_NOT_FOUND	Loan account not found	Verify the loan account exists
SCHEDULE_RETRIEVAL_ERROR	Error retrieving loan schedules: [details]	Check logs for specific error

Best Practices

✅ Do

• Cache results if displaying frequently: Use $.cache.set() to store schedule data
• Check for null schedules: Verify the loan has schedules before processing
• Handle overdue payments: Build logic to highlight late payments
• Round amounts: Use round() function for currency display
• Filter schedules: Show only relevant schedules (upcoming, overdue, etc.)

❌ Don't

• Don't modify schedules: This is read-only; use reschedule commands to modify
• Don't use for preview: Use GenerateLoanSchedulesCommand for what-if scenarios
• Don't call repeatedly: Cache the result if used multiple times
• Don't assume order: Sort schedules by installmentNumber or dueDate

Performance Tips

// Cache schedule data for 5 minutes
var cacheKey = 'loan_schedules_' + context.accountNumber;

var schedules = $.cache.getOrSet(cacheKey, function() {
  return doCmd('GetExistingLoanSchedules', {
    accountNumber: context.accountNumber
  });
}, 300); // 5 minutes

return schedules;

Related Commands

• GenerateLoanSchedulesCommand - Preview schedules before loan creation
• CreateLoanCommand - Create a new loan with schedules
• RepayLoanCommand - Record loan repayments (updates schedules)
• RestructureLoanCommand - Modify existing loan terms and schedules

See Also

• BankLingo Functions Reference
• Loan Management Guide
• Command Execution Engine