Free Loan Tracking Spreadsheet Template (Excel)
TLDR We built a free loan tracking spreadsheet template for private and specialist lenders — covering loan registration, repayment tracking, missed payment logging, and a live portfolio dashboard. Download it free below, use it immediately, and read on for how to get the most out of it.
Most private lenders start with a spreadsheet. It is fast to set up, free, and familiar. But somewhere between your tenth and twentieth loan, it stops being a tool and starts being a job.
The problem is that most loan tracking spreadsheets are not built well. Missing formulas, no portfolio view, no way to track missed payments systematically — they end up creating more admin, not less.
This template is different. We built it specifically for private and specialist lenders, based on the operational patterns we see across hundreds of lending businesses.
It is ready to use immediately, pre-populated with example data so you can see how it works, and structured to scale with your book — up to the point where a spreadsheet becomes the wrong tool entirely.
What’s in the Template
The template has four tabs, each with a specific job:
Tab 1: Instructions
A quick-reference guide covering how to use each tab, the colour coding system, valid status values, and tips for keeping the template accurate. Read this first.
Tab 2: Loan Register
The core of the template. One row per loan, capturing:
- Loan ID, borrower name, loan type, and status
- Loan amount, interest rate, and term
- Repayment type (P&I, Interest Only, or Bullet)
- Start date, with auto-calculated end date
- Auto-calculated monthly payment, total interest, and total repayable
- Security details and broker / introducer
The monthly payment formula handles all three repayment types automatically — P&I uses Excel’s PMT function, Interest Only calculates monthly interest on the principal, and Bullet loans show £0 during the term. You do not need to write any formulas yourself. Yellow cells are inputs; everything else is calculated.
Tab 3: Repayment Tracker
Log each payment as it falls due and as it is received. For each payment entry:
- Loan ID and borrower name (to link back to the loan register)
- Payment number and due date
- Amount due and amount received
- Payment status — Paid, Missed, Partial, or Upcoming
- Auto-calculated shortfall (amount due minus amount received)
- Notes field for any context
Missed payments are automatically highlighted in red. Paid payments appear in green. Upcoming payments in amber. At a glance, you can see the state of your repayment pipeline without scanning individual rows.
Tab 4: Portfolio Dashboard
A live summary of your whole book, pulling data automatically from the Loan Register and Repayment Tracker. The dashboard shows:
- Total loans issued, active, completed, and defaulted
- Total capital deployed and total book value
- Total interest expected across the portfolio
- Average interest rate and average loan term
- Total missed payments and total shortfall value
- Upcoming payments due in the next 30 days
- A snapshot of your current active loans
Everything updates automatically as you add data to the other tabs. No manual updating, no pivot tables to refresh. The portfolio analysis you need to run your book — at a glance, every time you open the file.
How to Use the Template
Getting started
- Download the file and open it in Excel or Google Sheets.
- Go to the Instructions tab and read the colour coding and status values.
- Go to the Loan Register tab and delete the example rows (rows 5–9).
- Add your existing loans, one per row, filling in the yellow cells.
- Go to the Repayment Tracker and log any historical payments.
- Open the Portfolio Dashboard — your book summary will populate automatically.
Maintaining it week to week
The ongoing discipline is simple: every time a payment is due, add a row to the Repayment Tracker. Mark it Paid, Missed, Partial, or Upcoming. When a loan is fully repaid, update its Status in the Loan Register to Completed. When you issue a new loan, add a row to the Loan Register.
That is the whole workflow. The dashboard and calculations handle the rest. The 7 ways manual loan processes limit business growth are mostly about inconsistency — this template enforces consistency by giving every loan and every payment a defined place.
Tips for keeping it accurate
- Use Loan IDs consistently. The repayment tracker links to loans via their ID. If you use different IDs in different tabs, the dashboard calculations will miss payments.
- Use exact status values. The formulas use COUNTIF to count by status. ‘active’ and ‘Active’ are treated differently — always use the capitalised versions from the instructions.
- Do not edit formula cells. Black text cells are formulas. Editing them will break calculations. If something looks wrong, check the yellow input cells first.
- Add rows at the bottom. The dashboard formulas cover rows 5–29 in the loan register and rows 5–100 in the repayment tracker. Add new loans and payments within those ranges.
- Back it up. A spreadsheet is only as good as its last save. Keep a dated backup copy whenever you make significant changes.
The Honest Limitations of a Spreadsheet
We built this template to be as useful as possible for lenders who are not yet ready for dedicated software. But it is a spreadsheet, and spreadsheets have real limitations. Understanding them will help you get the most out of this template — and know when it is time to move on.
| Capability | This Spreadsheet | LendFusion |
|---|---|---|
| Loan tracking | YesWorks fine | YesAutomated, real-time |
| Repayment logging | YesWorks fine | YesAutomated from payment provider |
| Missed payment alerts | NoManual check only | YesInstant, automatic |
| Borrower self-service | NoNot possible | Yes24/7 portal included |
| Audit trail | NoNone | YesComplete, date-stamped |
| Portfolio dashboard | PartialManual formulas | YesLive, no maintenance |
| Scales beyond 50 loans | NoGets unwieldy | YesNo limit |
| Multiple users | PartialVersion risk | YesRole-based access |
| Compliance reporting | NoManual rebuild | YesAuto-generated |
The limitations in the middle column are not failures of this template — they are the structural limitations of spreadsheets as a tool for managing a lending business. Moving from Excel to a loan management system removes all of them simultaneously. The template above is a bridge, not a destination.
When Should You Stop Using a Spreadsheet?
The honest answer: sooner than most lenders do. Here are the signals that the spreadsheet has become a constraint rather than a tool:
- You have more than 30–40 active loans and updating the tracker is taking more than an hour a week
- You have missed a payment or caught one late because you did not check the spreadsheet in time
- A borrower has disputed a payment and you cannot easily produce a clean payment history and audit trail
- Your team has more than one person accessing the file and version conflicts are causing errors
- You want to scale your loan volume and you can see the spreadsheet will not handle it
- You need to show an investor or funder a clean, real-time portfolio view
If any of those are true, the business case for loan management software is clear. LendFusion handles everything this template does — automatically, in real time, with a full audit trail — and adds the capabilities a spreadsheet cannot provide: automated repayment collection, borrower self-service, missed payment alerts, and compliance reporting.
What LendFusion Does That This Template Cannot
This template tracks loans and payments. LendFusion automates them. The difference in practice:
- Repayments are collected automatically via direct debit or card — no manual bank transfers or reconciliation
- Missed payments trigger an automatic follow-up sequence — a borrower reminder on day one, an escalation on day seven — without you initiating it
- Borrowers can log in to a self-service portal to check their balance, view their repayment schedule, and download statements — without contacting you
- Every action — payment received, communication sent, status change — is logged automatically in a complete loan audit trail
- The portfolio dashboard updates in real time, not when you open the file and remember to check it
- All loan management workflows — origination, servicing, collections — run in one platform with no manual handoffs between tools
None of this requires a developer or an IT team. LendFusion is designed for lending businesses that want to operate professionally without the infrastructure of a bank. Most lenders go live in days.
The Bottom Line
A well-built loan tracking spreadsheet is a legitimate tool for lenders in the early stages of building a book. This template gives you the structure to use it properly — consistent loan registration, systematic payment tracking, and a portfolio view that does not require manual maintenance.
Use it for as long as it serves you well. And when the signals appear — more than 30 active loans, missed payments slipping through, audit trail gaps, or a team that is spending more time on the spreadsheet than on lending — it is time to look at what comes next.
Ready for something that runs itself?
LendFusion automates everything this template does manually — and adds automated collections, borrower self-service, missed payment alerts, and compliance reporting. Go live the same day, no IT team required.
Book your free demo at lendfusion.com/demo
Vahuri Voolaid, COO
Vahuri is the Chief Operations Officer at LendFusion. Vahuri has 10 years of experience in fintech with loan management software as a product owner and an MBA with a specialisation in IT management.
Connect with Vahuri on LinkedIn.
