An Excel template for lenders (and exactly where it will break)

Yes, you can track your loans in Excel, and for a small portfolio it is a perfectly reasonable choice. A well-built spreadsheet gives you a schedule, a balance and today's collections without paying a cent. What almost nobody tells you is the exact point where it stops serving you: it is not when you have "a lot" of loans, it is when arrears that depend on the actual payment date, a second collector and cash reconciliation show up. This article gives you both things: how to build it, and how to know it is no longer enough.

The five sheets you need

A loan template is not one table, it is five related tables. If you try to squeeze everything into a single sheet, in three months you will have rows with duplicated data and no idea which one is right.

The golden rule: Schedule is what should happen; Payments is what happened. The day you mix the two in the same table, you lose the ability to know who is late.

Minimum columns for each sheet

Clients: client ID, full name, ID document, phone, address, references.

Loans: loan ID, client ID, amount disbursed, interest rate, interest type, frequency, number of instalments, disbursement date, arrears rate, grace days, assigned collector, status.

Schedule: loan ID, instalment number, due date, total instalment, principal, interest, balance after the instalment.

Payments: payment ID, loan ID, instalment number, actual payment date, amount received, arrears charged, collector, payment method.

Cash: date, collector, movement type, amount, reference.

The field most templates forget is the actual payment date. Without it you cannot calculate arrears, you cannot assess the client and you cannot reconcile anybody's cash. It is the single most important column in the whole file.

The formulas that do the work

If you do not want a fixed instalment but interest on the original amount (the most common approach in street lending), the instalment is even simpler: =(amount × rate × number_of_instalments + amount) / number_of_instalments. Careful: that is flat interest on the initial balance, and its real cost is considerably higher than the rate suggests. We explain it in How much interest to charge for lending money.

Daily, weekly, biweekly and monthly in the same sheet

This is where nearly every downloadable template falls apart: the date of each instalment. Do not type the dates by hand. Generate the first one and let the frequency do the rest.

Two warnings that cost money:

• Monthly is not "+30 days". If you add 30, a 12-instalment loan starting on 31 January ends up drifting. Use EDATE, which respects month-ends.
• Daily almost never means seven days a week. If you do not collect on Sundays, your real "daily" schedule is six days. Build it with WORKDAY or you will have instalments landing on days when nobody is out collecting.

And now, where it breaks

Excel handles 1,048,576 rows per sheet. That is not the problem. Forty daily loans at 90 instalments is 3,600 schedule rows: Excel does not even blink. Size is never the problem. It comes down to four concrete things.

1. Arrears depend on the day you collect, and your sheet does not know it

This is the big one. Arrears are not a number you write once: they change every day that passes. And they only freeze when the client pays.

A client with an outstanding instalment of 5,000, an arrears rate of 1% per day and 3 grace days:

In Excel you can calculate today's arrears with TODAY(). What you cannot do is keep a record, instalment by instalment, of how much arrears were actually charged on the day the client paid, because TODAY() recalculates every time you open the file and wipes the history. Tomorrow the sheet will show you a different figure. And a month later you will no longer know whether you charged the client the arrears or waived them. It is a silent hole: it throws no error, it just leaves you with no data. (The full formula, with calculation base and grace days, is in How to calculate arrears on a loan).

2. Three collectors, one file

One collector is easy: you. Two is already a problem, and three is a serious one. The file lives in one place and the collections happen out on the street. Every way out is bad:

• Everyone with their own copy: at the end of the day there are three files with three truths and somebody has to merge them by hand. A transcription error is not a possibility, it is a statistical certainty.
• One file in the cloud: two people write the same cell and the last one wins. The other one's collection disappears without a trace.
• Everybody dictates to one person over messaging: it works, and that person is your bottleneck. If they fall ill, your portfolio stops.

And none of the three tells you who changed a figure, when, or what it was before. Excel has no audit trail.

3. Cash reconciliation

At the end of the day, the collector hands you cash. The question is simple: does what they bring match what they recorded?

That 400 gap only shows up if you have the three figures above for the same day and the same collector. In Excel that means somebody builds a cash count by hand every afternoon, for every collector, with SUMIFS. It gets done properly the first week, halfway the first month, and then it stops. The day it stops, the cash stops balancing and nobody notices. (The right procedure is in How to reconcile your collectors' cash).

4. The client's history

Your sheet knows whether a client is late today. It does not know whether they tend to be late. And that is the information that decides whether you lend to them again.

To know it you would need, per client and per loan: how many instalments they paid on time, how many late, how many days late on average, how many loans they repaid in full and how many were written off. All of that is derivable from the Payments sheet… if you saved the actual date of every payment and nobody deleted old rows. In practice, when the loan is settled most people archive the row and the history is lost. (How to assess a borrower with data: How to tell whether a client pays well).

How to know the spreadsheet is no longer enough

So, Excel or not?

Yes, if you collect on your own, have fewer than 15 or 20 active loans and your arrears are more of a conversation than a calculation. The spreadsheet will serve you and you need nothing else.

No, the moment a second collector appears or arrears become real money. It is not a matter of volume: there are operations (freezing arrears at the moment of collection, reconciling cash per collector, keeping the history attached to the client's ID document) that a spreadsheet structurally cannot do. It is not that Excel is bad. It is that this is not what Excel does.

Prestafolio does exactly those four things: it calculates arrears daily with their grace days and freezes them on collection, keeps a cash till per collector with its reconciliation, stores every movement with its audit trail and builds the borrower's history from their ID document. If you recognised yourself in the table above, take a look at the plans.