How to Migrate From Excel to Loan Management Software

Moving your loan book out of Excel isn’t just about uploading a file. It’s about changing how your lending operation runs while borrowers keep paying.

The spreadsheet isn’t just a record. It holds your balances, your schedules, your penalty rules, and often, the only place your repayment history lives.

That’s why the real answer to “how do I migrate without losing data” isn’t a single import button. You protect your data by cleaning it first, mapping every field deliberately, reconciling balances before go-live, and running the old and new records side by side for one full collection cycle before you switch off the sheet.

Done right, a migration for a lender with a few hundred active loans takes a few weeks of part-time work. Most of that time goes into the boring part: chasing down which version of the file is real, and why the outstanding balance on loan 214 doesn’t match the cash you banked.

This guide walks through the process in the order a lending operation actually needs it, from setting scope to retiring Excel. If you’d rather not do the first import yourself, Lendbox will take your Excel file and set the account up for you. You can start a free trial at lendbox.io to see your real portfolio position before you commit to anything.

Set the Migration Scope Before Moving Any Data

Before you even open an import template, decide what “done” looks like. A tight scope keeps a migration from dragging on for months and quietly breaking collections in the middle.

The two big decisions: which records must stay live throughout, and whether you move everything at once or in stages.

Identify the Records That Must Keep Operating

Split your loan book into what’s live and what’s history.

Live records are active loans with a remaining balance, loans in arrears, restructured loans, and anything with a repayment due in the next 60 days. These must move with their schedules intact, because a loan officer will be collecting against them next week.

History is different. Fully settled loans, written-off accounts, and old borrower files can follow later, or just sit in an archive.

Nothing in your migration plan should put an active repayment schedule at risk in order to preserve a loan that closed in 2023.

Write the list out:

• Active loans and their remaining schedules
• Loans in arrears, with days overdue and accrued penalties
• Borrower profiles for anyone with an active or recent loan
• Collateral held against active loans
• Opening balances for cash, loans receivable and interest income
• Repayment history for at least the current financial year

Choose a Phased Migration or Full Migration

A full migration means everything moves and you go live on one date. It’s cleaner conceptually and works well if you have one branch, under a few hundred active loans, and a single person who knows the spreadsheet.

A phased migration moves one slice at a time: one branch, one loan product, or new disbursements only. New loans get written in the new system while existing loans run down in Excel. It reduces risk but doubles the reporting work while both run.

• Full migration — Fits when: One or two branches, one clear source file, small active book. Main risk: Everything depends on one cutover weekend going well.
• Phased by branch — Fits when: Multiple branches, uneven data quality. Main risk: Head office reports on two systems for weeks.
• Phased by product — Fits when: Distinct products with different interest methods. Main risk: Staff must remember which loan lives where.
• New loans only — Fits when: Short-term book that turns over in 3 to 6 months. Main risk: Old book never gets structured data.

In practice, most small lenders do best with a full migration of active loans plus a partial history load. Settled loans get archived as a read-only Excel file.

Assign Owners, Success Checks and a Migration Timeline

Migrations stall when everyone is responsible and nobody is accountable. Name one owner for data, usually whoever knows the spreadsheet best.

Name one owner for configuration, usually the operations manager. Name one approver, usually the director, who signs off go-live.

Then define success in numbers you can actually check:

• Total loans receivable in the new system matches the reconciled spreadsheet figure to the cent
• Active loan count matches
• Sum of arrears matches your aging report
• 20 sampled loans match on principal, balance, next due date and penalties
• Chart of accounts opening balances tie to your last trial balance

Give yourself a realistic timeline. Maybe two weeks for audit and cleaning, a week for mapping and configuration, another for a pilot import and reconciliation, then a full collection cycle in parallel. Compress it if your book is tiny, but don’t skip the parallel run.

Audit and Clean the Existing Loan Book

An import copies what you give it. Feed it a spreadsheet with merged cells, three date formats, and two rows for the same borrower, and you’ll just get a faster version of the same mess.

This stage is unglamorous. But honestly, it’s where most of the value sits.

Inventory Every Excel File and Source of Loan Data

Start by finding every place loan data lives. It’s almost never just one file.

Typical inventory for a small lender:

• The master loan tracker, plus the copies on two laptops
• A separate collections sheet the field officers update
• A cash book or receipt book that isn’t linked to either
• WhatsApp threads where repayments were confirmed but never posted
• Paper loan agreements and ID photocopies in a filing cabinet
• Penalty calculations done on a calculator and written in the margin

For each source, record who owns it, when it was last updated, and whether it disagrees with the master file. Then pick one master and mark the rest read-only.

Renaming the old copies with “ARCHIVE” in front works better than trusting people to remember.

Clean Borrower Records, Loan Data and Repayment History

Now standardise. The goal is one row per record, one format per column, no notes hidden in cell comments.

Work through this in order:

1. Borrowers. One record per person. Merge duplicates created by spelling variations. Standardise phone numbers to one format, because these will drive SMS and WhatsApp reminders later.
2. Identifiers. Give every borrower and every loan a unique ID. If you’ve been using names as identifiers, this is the single most valuable hour you’ll spend.
3. Dates. One format everywhere. Disbursement date, first due date, maturity date. Ambiguous dates are the most common source of wrong schedules after import.
4. Amounts. Numbers only. Strip currency symbols, remove thousand separators that were typed as text, and delete rows that were subtotals rather than loans.
5. Loan status. A fixed list: active, in arrears, restructured, settled, written off. No free text.
6. Repayments. Date, amount, and how it was allocated between principal, interest, fees and penalty. If allocation was never recorded consistently, decide one rule now and apply it going forward.

Then reconcile. Take your total outstanding principal from the cleaned file and check it against your cash position and your last set of accounts.

Where it doesn’t tie, fix it while you still remember the context. AI tools can help you spot duplicate borrower records or read a scanned ID, and they’re genuinely useful for organising files, but a machine can’t tell you which of two conflicting balances is the real one. That’s a human call.

Decide What to Migrate, Archive or Rebuild

Not everything deserves to move.

Migrate: active loans with full schedules, borrowers with active or recent loans, current-year repayment history, collateral on active loans, opening balances.

Archive: loans settled more than a year ago, dormant borrowers, historical reports. Keep the cleaned Excel file read-only and stored somewhere other than a laptop.

Rebuild: loan products, interest methods, fee structures, penalty rules and approval limits. These are configuration, not data.

Write down each product’s terms on paper first, including how interest is calculated and when penalties bite. Build them fresh in the new system. Copying a broken formula across achieves nothing.

Map Data and Configure the New Lending Workflow

Mapping is where you decide, field by field, what each spreadsheet column becomes. Get this wrong and balances look right while schedules are quietly off by a month.

Configuration is the other half: loan products, permissions and accounting set up before real data lands.

Create a Field Mapping for Borrowers, Loans and Balances

Build a simple mapping table before you import anything. One row per spreadsheet column.

• Client Name → Borrower first name + surname (Text) — Split into two fields.
• Cell No → Borrower phone (+63XXXXXXXXXX) — Drives SMS and WhatsApp reminders.
• Amount Given → Principal (Number) — Excludes upfront fees.
• Date Out → Disbursement date (YYYY-MM-DD) — Sets schedule start.
• Balance → Outstanding principal (Number) — Must exclude accrued interest.
• Paid To Date → Total repayments (Number) — Cross-check against principal + interest.
• Status → Loan status (Fixed list) — Map free text to the five statuses.

Two mapping decisions cause most post-import pain. First, whether your “balance” column includes accrued interest or only principal. Second, whether an active loan gets imported at its original disbursement amount and then has repayments replayed, or imported at its current balance with a fresh schedule.

Replaying repayments gives you real history. Importing at current balance is faster. Pick one, apply it to every loan, and record which you chose.

Import in the right order: borrowers first, then loans, then repayments. Loans need a borrower to attach to, and repayments need a loan.

Set Up Loan Products, Roles and Approval Rules

Configure the system to match how you actually work, not how you wish you worked.

For each loan product, define the interest method, term, repayment frequency, upfront or spread fees, late penalty rule, and any discount you offer. Then generate a test schedule and check it by hand against a loan you know well.

If the third instalment is off by a few pesos, your interest method is wrong. It’s way cheaper to find that now.

Roles come next. A loan officer should see their own book. A branch manager should see their branch. Head office should see everything.

This is the control a shared spreadsheet can never give you, and it’s worth taking seriously. Role-based access plus an audit trail is what turns “someone changed the balance” into a name and a timestamp.

Set approval rules to match your real authority limits. If a loan over a certain amount needs the director, build that in rather than relying on memory.

Prepare Accounting, Notifications and Reporting

Accounting is the part lenders most often postpone—and the one they usually regret leaving for last.

Set up your chart of accounts and enter opening balances as of your cutover date. You'll need to cover cash, bank, loans receivable, interest receivable, interest income, fee income, penalty income, and any provision accounts.

Make sure these tie back to your last trial balance before you post anything new.

When your loan book and accounting system connect, disbursements and repayments can generate journal entries automatically. Platforms with built-in double-entry accounting, like Lendbox, save you from retyping loan activity into a separate ledger at month-end.

Still, check the entries the system produces against one loan manually before trusting them across your whole book. It's worth the extra few minutes.

For notifications, keep them off during import. Nothing ruins borrower trust faster than 400 automated reminders hitting people who are actually up to date.

Turn notifications on deliberately after go-live, maybe start with just one product or branch.

Build reports you'll actually use every day: an arrears list, aging buckets at 30, 60, and 90 days, portfolio at risk (PAR), a repayments report, and a daily business summary. If you don't use a report, skip it for now.

Prove the Migration With a Pilot and Parallel Run

You don't need blind trust in the migration. You need to test it.

A small pilot import, line-by-line reconciliation, and a period where both records run together will tell you more than any vendor promise.

Use a Staging Environment for a Pilot Migration

Do a trial run before the real one. Take a sample—20 to 30 loans covering every product you offer, plus one loan in arrears, one restructured loan, and one nearly settled.

Import those into a test account or a fresh workspace you can toss afterwards.

Then check each one by hand:

• Principal and current outstanding balance
• Next due date and instalment amount
• Total instalments remaining and maturity date
• Accrued penalties and fees
• Repayment history, with correct allocation between principal, interest, and penalty

Edge cases teach you the most. Loans with irregular payments, loans where a borrower paid double one month, loans restructured mid-term.

If the schedule for those comes out right, your mapping is probably sound. If not, fix the mapping and re-run the pilot—don't patch individual loans later.

Reconcile Balances, Schedules and Accounting Entries

Once you've done the full import, reconcile at three levels.

Portfolio level. Total outstanding principal, total active loan count, and total arrears must match your cleaned spreadsheet exactly. If you're off by a few pesos, don't shrug it off—find the cause.

Loan level. Sample at least 10% of active loans, or 20 loans, whichever is bigger. Compare every figure against the source file.

Accounting level. Loans receivable in your ledger should equal total outstanding principal in the loan book. Cash should match your bank statement at the cutover date.

If the two modules disagree, something's off in the loan data or opening balances. Better to know now than after staff start posting.

Keep this reconciliation as a signed-off document. When someone questions a number in three months, you'll be glad you have it.

Test Real Staff Workflows Before Approval

Numbers matching isn't the same as the system working. Have your team run their actual daily tasks in the test data.

Ask a loan officer to record a repayment on a real active loan and confirm the balance and schedule update as expected. Have them try a partial payment, then an early settlement.

Ask the branch manager to approve a loan and check the disbursement posts to the right accounts. Let the bookkeeper run a trial balance.

Each person should confirm they can do their job without needing help. That’s your real acceptance test.

If someone needs a phone call to record a payment, you’re not ready to go live, no matter how clean the data looks.

Plan a Controlled Cutover and Go-Live

Cutover is the short window where you stop recording in Excel and start using the new system. It should be dull, honestly.

Pick the quietest week you can, freeze the spreadsheet, load final figures, and keep collections moving throughout.

Set a Cutover Date and Handle Final Data Changes

Choose a date with the fewest due payments. For most lenders, that's the start of a month or just after a heavy collection day—not before one.

The sequence that works:

1. Announce the date to all staff at least a week ahead, with a one-page how-to.
2. Post every outstanding repayment into the spreadsheet up to the cutover moment.
3. Freeze the spreadsheet. Make it read-only, no exceptions, and have one named person hold edit rights.
4. Export, run the final import, and reconcile totals again.
5. Get the approver's sign-off in writing before staff start entering new transactions.
6. Anything collected during the freeze goes onto a paper log and is keyed into the new system first thing after go-live.

That paper log matters. Field collections don't pause just because you're migrating, and a payment recorded in neither system is exactly the kind of loss you want to avoid.

Keep Collections and Borrower Communication Running

Collections can't go quiet during a migration. Give loan officers a printed arrears list on cutover morning so they can keep working, even if they haven’t logged in yet.

Keep the receipt book handy as a fallback. Assign one person to answer "which system do I use for this?" questions for the first week.

On the borrower side, say little and change less. Borrowers don’t need to know about your migration. What they will notice is a duplicate reminder, a receipt with a new reference format, or a statement showing the wrong balance.

So: keep automated reminders off until you’ve verified arrears data. Then switch them on for one product first.

If receipt numbering changes, tell borrowers who ask, and keep the old numbering visible on statements for a month. If you’re opening a borrower portal, launch it after you’re confident in the balances—not on day one.

Prepare a Rollback and Escalation Plan

Decide ahead of time what would send you back to the spreadsheet, and how.

Write down the conditions: portfolio totals that won’t reconcile, schedules generating wrong instalments across multiple products, or staff unable to record repayments. If any of those hold after 48 hours, revert to the frozen spreadsheet, unfreeze it, key in transactions recorded since cutover, and reschedule.

Rollback only works if you keep the frozen spreadsheet untouched and stored separately for at least three months. Don’t delete it, and don’t let anyone edit it "just to fix one thing."

Also, name your escalation route. Who calls the vendor, through which channel, and what counts as urgent? Knowing support is reachable by email or chat—and that you can book a training session—is worth confirming before cutover, not during it.

Build Confidence After the Switch

The technology isn’t usually the hard part. Trusting new numbers is. The weeks after go-live are about training people on their own tasks and watching the data closely.

Only let go of Excel once the new process has survived a full cycle.

Train the Team on Their Daily Roles

Train by role, not by feature tour. A loan officer doesn’t need to see the chart of accounts.

Keep sessions short and hands-on:

• Loan officers: record a repayment, view their arrears list, check a borrower's history, submit an application.
• Branch manager: approve loans, review the branch book, spot officers whose collections are slipping.
• Bookkeeper: post manual entries, run bank reconciliation, produce a trial balance and financial statements.
• Owner: portfolio at risk, aging buckets, daily business report, branch comparison.

Expect some resistance, and take it seriously. The person who resists most is usually the one who knew the spreadsheet best, and their objections often point at a real gap in your setup.

Let one confident staff member become the in-house go-to. That's worth more than a manual nobody reads.

Monitor Data, Reports and Exceptions in the First Weeks

For the first month, keep a close eye on things. Run a short daily check and a weekly deeper one.

Daily: repayments recorded yesterday against cash banked, any loans with a missing next due date, any new borrower record without an ID.

Weekly: total outstanding principal against the ledger, arrears total against your aging report, and a look at the audit trail for edits to balances or backdated entries.

Backdated repayments are the most common early problem—usually honest, but worth a conversation.

Watch exceptions, not everything. A daily list of loans with no schedule, negative balances, or repayments that failed to allocate will surface real errors faster than reading reports end to end.

Retire Excel Only When the New Process Is Stable

Run both systems side by side for a full collection cycle. Jot down activity in each, and at the end, stack up the totals.

If outstanding principal, arrears, and cash all line up, you’re probably ready to move on. But don’t rush it.

When it’s time to stop, do it for real. Let everyone know the spreadsheet won’t be updated anymore, lock down edit access, and stash a read-only copy in your archives for reference.

Leaving Excel half-alive is a recipe for confusion—two active records just lead to clashing truths, and people will pick whatever’s easiest.

Before you close things out, here’s a handy test: choose a borrower at random. Try to pull up their statement, current balance, and next due date from the new system, all in under a minute.

If you can do that, and your reports match your books, you’re done. That’s when you know the migration’s actually finished.