A loan book kept in a notebook or a plain spreadsheet breaks the first time a borrower pays late or asks for a statement. The Loan and EMI Management System V1.0 in Excel VBA is one macro-enabled Excel file that records every loan, works out the EMI, builds the schedule and shows who is overdue.
Watch the Loan Management System in Excel VBA with EMI Schedule walkthrough
Launch offerRecord, read and write your own Excel macros and UserForms, and use AI assistants as your VBA tutor without trusting them blindly. 6h 12m of video by PK, with practice files and a capstone.
What Is a Loan Management System in Excel?
It is a workbook with entry forms, registers and dashboards for a small lender. You add a borrower, add a loan with the amount, rate and tenure, and the system works out the monthly EMI on a reducing balance, the maturity date and the total interest. Every payment you receive is split into principal and interest, and the loan status changes to Current, Past Due, Default or Paid Off as dates pass.
Inside the System
MAIN menu
Five live cards (active loans, outstanding principal, collected this year, overdue amount, past due loans) and eight button groups: Loans, EMI Collections, EMI Schedule, Borrowers, Dashboards, Expenses, Print and Documents, and Setup and Help.
EMI schedule in Excel
Pick any loan and see the full loan amortization schedule in Excel: due date, opening balance, EMI, interest, principal and closing balance. Each EMI is shown as Paid, Overdue or Upcoming, with the overdue amount and days past due at the top.
EMI Calculator
Change the amount, rate, tenure or first EMI date and the EMI, total interest and schedule update at once. Nothing is saved, so you can quote a borrower before lending.
Three dashboards
The Loan Portfolio Dashboard shows total disbursed, principal outstanding, principal recovered and interest earned. The EMI Collections Dashboard shows collections by month, payment mode and loan type. The Delinquency and Overdue Dashboard ranks outstanding principal by delinquency bucket and shows the overdue amount by loan type and loan officer. Slicers filter every card and chart.
Income Statement
Interest income, late fees and origination fees against operating expenses, month by month, with net income and net margin for the report year.
Registers, receipts and settings
Loan, Borrower, Payment, Expense and Document registers keep every record in one place. Print an EMI payment receipt or a loan summary on A4, 4 inch or 3 inch paper. In Settings you set the report date, late fee, company details, currency and numbering.
How a Day Works
You open the MAIN menu and read the five cards. A new borrower asks for a loan: you press New Loan, pick the borrower, loan type, branch and officer, enter the amount, rate and tenure, and save. The EMI and schedule appear on their own. When a payment arrives you press Receive EMI Payment, pick the loan, and the form suggests the EMI; add a late fee if needed and save. To chase late payers, press Overdue Loans List, which shows only loans with an unpaid EMI and their days past due. The demo workbook holds a sample lender with 120 loans, 105 borrowers and 2,798 payments, so you can see every dashboard working before you enter your own records.
Tips for a Clean Loan Book
Set the report date in Settings first, because every overdue amount and days past due figure is worked out against it. Enter borrowers before loans so each loan picks up the phone number and borrower number by itself. Record every payment the day it arrives, so the EMI schedule and the overdue list stay true. Attach the signed agreement and ID proof to the loan from the Document Library, so the papers sit next to the record. Use the loan tracking spreadsheet registers as a source for your own pivot tables if you need more cuts.
Pros and Limits
- EMI, schedule, principal and interest split are automatic
- Overdue loan tracker in Excel with days past due and delinquency buckets
- Three dashboards, a calculator and an income statement in one file
- No subscription, and your data stays on your PC
- Needs Excel for Windows 2016 or later with macros enabled
- Does not work on Mac, in Excel for the web or in Google Sheets
- No bank connection, payment gateway or automatic reminders
Frequently Asked Questions
What does the Loan and EMI Management System do?
It records loans and borrowers, works out the monthly EMI on a reducing balance, builds the EMI schedule, receives payments, tracks overdue EMIs and shows portfolio, collections and delinquency on dashboards.
How is the EMI calculated?
EMI uses the standard reducing balance method, so interest is charged on the unpaid principal each month. Each payment is split into principal and interest by formula.
Can I try it first?
Yes. A 7-day free trial is available from the product page. It is a separate file that stops working after 7 days.
Which Excel do I need?
Microsoft Excel for Windows 2016 or later with macros enabled. It does not work on Mac or in Excel for the web.
Can I print receipts?
Yes, on A4 or on a 4 inch or 3 inch receipt printer, as a preview, a direct print or a PDF.
Is sample data included?
Yes. A demo lender with 120 loans, 105 borrowers, 2,798 payments and 381 expenses is included, plus an illustrated PDF user manual.
Get the Loan and EMI Management System
Get the system here: Loan and EMI Management System V1.0 in Excel VBA. You can try it free for 7 days first. For another macro-enabled tool, see the Customer Payment Reminder Tool in Excel VBA.