Moving from spreadsheets or a notebook to loan software

Most small lenders put off switching because the move looks risky: years of loans, partly paid, in a notebook or a dozen spreadsheet tabs. The good news is that you rarely have to move everything, and you do not have to switch in a single day. The usual approach is gradual, and it works the same whether your records are on paper or on a screen.

Step 1: decide what actually needs to move

Not every loan belongs in the new system. Sort your records into three piles:

• Active loans, where money is still owed. These must move, with their current balance.
• Closed loans that were repaid in full. Usually you can leave these in the old file and keep it safe. Move them only if you want a full history in one place.
• Written-off or disputed loans. Treat them case by case, and keep the paperwork either way.

Starting with active loans only is the single biggest way to shorten the job. A lender with a few hundred records often finds that only a fraction are still running.

Step 2: clean the data before you move it

Moving messy data into a new system just gives you a tidy-looking mess. An afternoon of cleaning saves days later.

• Give each borrower one record. Merge duplicates such as the same person written two ways.
• Write phone numbers in one format, with the country code.
• Check that every active loan has an amount, a rate, a start date, a term and a repayment method (flat, reducing balance or interest only).
• Make sure payments recorded in the old file add up to the balance you believe is owed. Fix the disagreements now, while you still remember the story behind them.
• Gather the documents for each active loan: ID, signed agreement, photographs of collateral.

Step 3: pick a cut-over date

Choose a clear day after which the new system is the record. Month end is the easiest, because balances are settled and you can reconcile cleanly. On that date, each active loan moves in with its balance as it stood that day, plus its original terms.

There are two honest ways to carry a half-repaid loan across. You can enter the loan as it was originally made and record every past payment, which gives a complete history but takes longer. Or you can enter it with an opening balance on the cut-over date, which is faster, and keep the old history in the archive. Many lenders use the first for large loans and the second for the rest.

Step 4: choose how to switch over

If you are unsure, start with the parallel run. Capture every new loan in both places from the first day. The old file stays up to date as a backup while you learn the new system, and you can compare totals at the end of each month.

Step 5: test with a small batch

Before moving everything, enter five to ten real loans of different kinds: a flat-rate loan, a reducing-balance loan, a loan with a part payment, one that is overdue, and one with collateral. For each, check that the system shows the same balance, next due date and total interest as your records. Any difference points to a rule you need to understand before you scale up.

Step 6: reconcile, then switch off the old one

• Compare the total outstanding principal in the new system with the total in your old records. They should match, or the gap should be explained loan by loan.
• Compare the amount due this month in both.
• Pick three borrowers at random and check their balances against what they believe they owe.
• When the numbers agree for a full cycle, stop writing in the old file.

Do not delete the old file or throw away the notebook. Mark it as closed on the cut-over date, store a copy somewhere safe, and keep it for as long as the rules where you lend say you should.

Step 7: bring your people along

• Show the person who records payments how to enter, correct and print a receipt before cut-over, not on the day.
• Tell collectors which borrowers are theirs and where to find the list.
• Let borrowers know their receipts and statements may look different, and who to contact if a balance seems wrong.

Common mistakes

• Moving closed loans first and running out of energy before the active ones.
• Skipping the clean-up and carrying old arguments about balances into the new system.
• Switching in the middle of the month, when it is hardest to tell what was paid before and after.
• Keeping both systems forever. Set an end date for the parallel run when you start it.