Import & Export Loan / Advance Detail

Navigation Path

Export & Import → Loan/Adv.

URL

/export-import/import-loan-adv-detail

Description

• Allows bulk import and export of employee loan and advance details
• Supports managing loan/advance records for multiple employees
• Provides Excel template for structured data import/export
• Enables filtering by employee, client, and unit for targeted exports
• Streamlines loan and advance management process

1. Page Components

Top Action Link

Export Example In Excel

• Location: Top-center of the page
• Function: Downloads pre-formatted Excel template
• File Format: Excel (.xlsx)
• Contents:
  • Column headers for loan/advance data
  • Built-in instructions for each field
  • Data format examples and validation rules
  • Sample loan/advance entries
  • Notes on loan types and repayment schedules

Important: The template includes comprehensive instructions on:

• Loan vs Advance differentiation
• Amount and EMI calculations
• Repayment schedule format
• Interest rate specifications
• Start and end date formats
• Required and optional fields

2. Filter Section

Filter Fields

Employee Field

• Type: Multi-select dropdown with search functionality
• Features:
  • Search employees by code or name
  • Select multiple employees simultaneously
  • Clear all selections (X button)
  • Upload icon for bulk selection
• Display Format: [Employee Code] | [Employee Name]
• Examples:
  • PFSBBAN691 | GOWRAMMA
  • PFSBHYD119 | SK JAHIDUL ALMO SK JAHIDUL ALMO
  • PFSBDDN229 | BINESH DEVI
  • PFSBB5763 | RAM GOCHHAYAT

Client Field

• Type: Multi-select dropdown with search functionality
• Features:
  • Search clients by company name
  • Select multiple clients simultaneously
  • Clear all selections (X button)
  • Upload icon for bulk selection
• Display: Shows "NULL" if no client assigned
• Purpose: Filter loan/advance records by client association

Unit Field

• Type: Multi-select dropdown with search functionality
• Features:
  • Search units by code or name
  • Select multiple units simultaneously
  • Clear all selections (X button)
  • Upload icon for bulk selection
• Display Format: [Unit Code] | [Unit Name]
• Examples:
  • UNT00007683 | TATA TELE SERVICES, NOIDA
  • UNTPFS00008321 | BLS INTERNATIONAL SERVICES LTD.
  • UNT004989 | GOLDEN SPARROW KESSEL MALL, KURUKSHETRA

3. Action Buttons

Search and Reset Buttons

4. Import Sections

The page has two separate import sections for different purposes:

Section 1: Import New Loan/Advance Records (Left Side)

Section Title: Select (Loan / Advance Excel) File:

Purpose:

• Import completely new loan/advance records into the system
• Add loan/advance entries that don't exist
• Bulk creation of loan/advance data

Use Case:

• Disbursing new loans to employees
• Recording new advances given
• Initial setup of loan/advance data
• Beginning of financial year data entry

Section 2: Import Exported Loan/Advance Records (Right Side)

Section Title: Select (Loan / Advance Excel) Exported Sheet File:

Purpose:

• Re-import previously exported records with modifications
• Update existing loan/advance configurations
• Apply changes made to exported data
• Bulk update EMI amounts or repayment schedules

Use Case:

• Modifying existing loan terms
• Updating repayment amounts
• Adjusting loan balances
• Bulk updating loan/advance records

5. Workflow: Export Loan/Advance Records

Step-by-Step Export Process

1. Select Filters (Optional)

   • Select specific employees (or leave blank for all)
   • Select clients (if needed)
   • Select units (if needed)
   • Filters help narrow down records to export

2. Search/Generate

   • Click "Search" button
   • System prepares loan/advance data based on filters
   • If no filters selected, exports all records

3. Download Excel

   • System generates Excel file with loan/advance data
   • File downloads automatically
   • Contains loan/advance records matching criteria

4. Review Data

   • Open downloaded Excel file
   • Review exported loan/advance records
   • Verify employee details and amounts
   • Check repayment schedules

6. Workflow: Import New Loan/Advance Records

Step-by-Step Import Process

1. Download Template

   • Click "Export Example In Excel"
   • Save template to local system
   • Review built-in instructions carefully

2. Fill Loan/Advance Data

   • Open template in Excel
   • Read instructions provided in template
   • Add new loan/advance details:
     • Employee information
     • Loan/Advance type
     • Principal amount
     • Interest rate (if applicable)
     • EMI/Monthly deduction amount
     • Start date and duration
     • Repayment schedule
   • Follow format guidelines
   • Include all required fields

3. Validate Data

   • Check employee codes exist in system
   • Verify amount calculations
   • Ensure dates are in correct format
   • Validate EMI calculations match loan amount
   • Check for duplicate entries

4. Upload File

   • Go to left import section
   • Drag file or click to browse
   • Select filled Excel file
   • File name appears in upload area

5. Import Records

   • Click "Import" button (left section)
   • Wait for processing
   • System validates all data
   • Review success/error messages

6. Verify Import

   • Check Loan/Advance master
   • Verify newly imported records
   • Confirm deductions will apply correctly
   • Test with sample salary processing

7. Workflow: Modify and Re-import Loan/Advance Records

Step-by-Step Modification Process

1. Export Existing Records

   • Select employees (or leave blank for all)
   • Select clients/units if needed
   • Click "Search" button
   • Download generated Excel file

2. Modify Loan/Advance Data

   • Open downloaded Excel file
   • Make necessary changes:
     • Update EMI amounts
     • Modify remaining balance
     • Adjust repayment schedule
     • Change loan status
   • Don't change employee codes or loan IDs (primary identifiers)

3. Save Modified File

   • Save changes to Excel file
   • Keep file in xlsx/xls format
   • Use meaningful filename

4. Upload Modified File

   • Go to right import section
   • Drag file or click to browse
   • Select modified Excel file

5. Import Changes

   • Click "Import" button (right section)
   • System updates existing loan/advance records
   • Wait for confirmation message

6. Verify Changes

   • Check Loan/Advance master
   • Verify modifications applied correctly
   • Test deductions in next salary cycle
   • Confirm balances updated

8. Loan/Advance Data Structure

Common Fields in Template

The Excel template typically includes:

Note: Exact fields and structure are detailed in the downloadable Excel template with instructions.

9. Loan vs Advance

Loan

Advance

10. Validation Rules

During Import

11. Success/Error Messages

Success Scenarios

Error Scenarios

12. Common Loan/Advance Types

Typical Loan Types

• Personal Loan - General purpose loan with interest
• Vehicle Loan - For purchasing two-wheeler or car
• Housing Loan - For home purchase or construction
• Education Loan - For self or dependent's education
• Emergency Loan - Urgent financial need
• Festival Loan - Special occasion loans

Typical Advance Types

• Salary Advance - Advance on upcoming salary
• Festival Advance - Diwali, Eid, Christmas advances
• Medical Advance - Healthcare emergency
• Travel Advance - Official or personal travel
• Marriage Advance - Wedding expenses
• Emergency Advance - Unforeseen circumstances

13. Best Practices

Template Usage

1. Download Fresh Template

   • Always get latest template from system
   • Contains updated instructions and structure
   • Reflects current system requirements

2. Read Template Instructions

   • Review all instructions within Excel file
   • Understand each field's purpose
   • Follow format examples provided
   • Pay attention to calculation formulas

3. Understand Loan Types

   • Know difference between loan and advance
   • Apply interest correctly
   • Use appropriate repayment schedules

Data Preparation

1. Verify Employee Eligibility

   • Check employee is active
   • Verify no existing active loans (if policy restricts)
   • Confirm employee's salary can accommodate EMI
   • Check organizational loan policies

2. Calculate EMI Correctly

   • Use proper loan calculation formulas
   • Include interest if applicable
   • Ensure EMI is affordable for employee
   • Verify total repayment amount

3. Plan Repayment Schedule

   • Set realistic start date
   • Consider salary cycle
   • Account for employee's take-home salary
   • Plan number of installments appropriately

Import Strategy

1. Test with Small Batch

   • Import 1-2 loan records first
   • Verify successful import
   • Test deduction in salary processing
   • Then proceed with bulk import

2. Group by Type

   • Import loans separately from advances
   • Easier to track and manage
   • Simpler error handling

3. Keep Documentation

   • Save copy of import file
   • Maintain loan approval documents
   • Keep audit trail
   • Document disbursement dates

Modification Best Practices

1. Export Before Modifying

   • Always export current records first
   • Make changes to exported file
   • Don't modify loan IDs or employee codes
   • Use right import section for updates

2. Document Changes

   • Note what was modified and why
   • Get approvals for modifications
   • Keep change log
   • Communicate to finance team

3. Verify After Import

   • Check Loan/Advance master
   • Run test salary with new EMI
   • Verify deductions apply correctly
   • Confirm balance calculations

14. Loan/Advance Management Tips

EMI Calculation

For Simple Loan (without interest):

EMI = Principal Amount ÷ Number of Installments

For Loan with Interest:

Use standard EMI formula considering:
- Principal amount
- Annual interest rate
- Loan tenure in months

Balance Tracking

• System automatically tracks remaining balance
• Each salary deduction reduces outstanding amount
• Balance becomes zero when fully repaid
• Can close loan manually if needed

15. Differences Between Two Import Sections

Left Section: New Loan/Advance Import

Right Section: Exported Records Import

16. Troubleshooting

Cannot Export Records

• Cause: No records match selected filters
• Solution: Clear filters or select different employees/units

Import Button Disabled

• Cause: No file uploaded
• Solution: Upload Excel file first, then click Import

EMI Not Deducting in Salary

• Cause: Loan start date in future or loan status inactive
• Solution: Check start date and loan status in master

Import Fails with Calculation Error

• Cause: EMI amount doesn't match total loan amount
• Solution: Verify EMI × Installments = Total Amount (with interest)

Duplicate Loan Error

• Cause: Employee already has active loan of same type
• Solution: Close existing loan first or allow multiple loans in settings

Wrong Balance After Import

• Cause: Incorrect remaining balance in imported file
• Solution: Re-export, recalculate balance, and re-import

17. Integration with Salary Processing

How Loans/Advances Affect Salary

1. Deduction in Salary

   • EMI automatically deducted each month
   • Appears in salary slip under deductions
   • Reduces net salary

2. Deduction Start

   • Begins from specified start date
   • Usually from next salary cycle after disbursement
   • Can be delayed if configured

3. Deduction End

   • Stops automatically when balance reaches zero
   • Or when total installments completed
   • Or when loan manually closed

4. Balance Update

   • Reduces after each successful deduction
   • Tracked in loan/advance master
   • Visible in employee records

18. Reporting and Tracking

Available Reports

• Active Loans Report: All currently active loans
• Completed Loans Report: Fully repaid loans
• Outstanding Balance Report: Employee-wise balances
• Loan Aging Report: Loans by start date
• EMI Schedule Report: Month-wise deduction plan

Monitoring

• Track loan disbursements
• Monitor regular EMI deductions
• Check for missed deductions
• Review outstanding balances
• Audit loan closures

19. Compliance and Policy

Organizational Policies

• Maximum loan amount per employee
• Maximum number of active loans allowed
• Interest rate policies
• Loan eligibility criteria
• Approval workflow requirements
• Repayment period limits

Documentation Requirements

• Loan application form
• Approval documentation
• Disbursement proof
• EMI deduction records
• Closure certificates

Tax Implications

• Interest on housing loans (tax benefits)
• Advance recovery (no tax implication)
• Loan write-offs (taxable as income)
• Perquisite tax considerations

20. Important Notes

Template Usage

• The Excel template is your complete guide - Contains all instructions and formats
• Read instructions carefully - Each field has detailed explanation
• Don't modify structure - Keep column order and headers unchanged
• Use calculation examples - Template shows EMI calculations

Import Considerations

• Verify employee can afford EMI - Check salary vs EMI ratio
• Start date planning - Coordinate with salary cycle
• Interest calculations - Double-check interest-based EMI
• Approval documentation - Maintain proper records
• Test before bulk import - Import few records first

Data Integrity

• All loan/advance data affects salary processing
• Changes impact employee take-home salary
• Ensure accurate EMI calculations
• Maintain proper audit trail
• Keep backup of loan documents
• Coordinate with accounts and HR teams

End of Documentation