How to build an employee loan ledger? The 6 required fields and template download
Six fields, not one can be missing:Borrower, department, loan amount, agreed repayment date, written-off amount, unwritten-off balance。Most companies' ledgers have only the first three, so year-end checks are unclear. Among them Agreed return date It is the one most easily omitted and also the most critical - without it, it is impossible to determine what counts as overdue.
Six Essential Fields and Their Respective Roles
| Field | Function | Consequences of omission |
|---|---|---|
| Borrower | Responsibility assigned to individuals, verifiable at departure sign-off | Unable to locate the specific person |
| Department | Statistics by department, making it easy to drive department heads | Finance can only chase them one by one. |
| Loan Amount | Original debt amount | — |
| Agreed return date | The sole basis for judging whether it is overdue | Never know which payment should be chased |
| Written-off amount | Record the cumulative reimbursement offsets and refunds | Cannot see which step this transaction has reached |
| Unwritten-off balance | Amounts that truly need to be recovered | Dunning with the original amount, but the amount is wrong |
On the basis of these six fields, add one more Days Overdue calculated column (current date minus the agreed repayment date, showing 0 if not overdue), the ledger is truly usable—sort once in descending order by overdue days, and it is clear at a glance who should be urged first, without any additional analysis.
Why should written-off and unwritten-off items be split into two columns
Because loans are rarely written off all at once. An employee borrows 10,000 yuan for a business trip, returns and reimburses 6,000, returns 2,000, and still has 2,000 unprocessed. At this point, if the ledger has only one "amount" column, you do not know which number to enter.
After splitting into two columns, 8,000 is written off and 2,000 is not written off. The status is clear at a glance, and Written-off plus unwritten-off must equal the loan amount, this itself is a validation rule that can detect backfill errors.
When Excel ledgers begin to become distorted
Using Excel for small-scale is entirely possible. But it has two structural flaws that will be exposed once the scale grows.
First, there is no trigger mechanism. When it expires, no one will be reminded, and the table will not change at all when overdue. It is just a static table that only has meaning if someone actively looks at it—and people will not actively look.
Second, data will not update itself. The matter of employees offsetting loans through reimbursement occurs within the reimbursement process. Excel does not know about it and relies on finance to manually backfill. As long as it is missed once, this sheet begins to become inaccurate, and it is very hard to detect—because nothing will tell you it is wrong.
An empirical judgment:If there are more than dozens of unreconciled loans at the same time, or they span more than three departments, the Excel ledger is basically no longer accurate。You can do a self-test—randomly select three entries, check the balances in the other receivables detail in the general ledger, and see whether they match.
Relationship between ledger and general ledger
The loan ledger is not an off-book table; it should be consistent with the Other Receivables details in the general ledger. The total unwritten-off balance for each person in the ledger should equal the balance of the employee loan portion under the Other Receivables account.
If these two numbers do not match, it means either the ledger has omissions or there are loans that were booked directly without going through the ledger. Regularly reconciling these two numbers is the most direct way to test whether loan management is effective.
How is this scenario handled in Kailing Technology's expense control system?
The loan ledger is automatically generated from loan forms and reimbursement write-offs. Written-off and unwritten-off balances are updated in real time with the reimbursement process and do not require manual backfilling. Overdue days are automatically calculated and support sorting and filtering, with automatic reminders to the handler and finance upon overdue. Ledger balances can be directly compared with the Other Receivables details in the general ledger, with automatic prompts for differences.
Learn about the Kailing Technology expense control and reimbursement system →
Common Questions
Small-scale is possible. But Excel has no trigger mechanism; after reimbursement is written off, it relies on manual backfilling, and overdue items will not be proactively reminded, so once the number of entries grows, it will inevitably become inaccurate.
It is recommended to record it, but it is not a required field. The purpose is to review afterward whether the loan was reasonable; it does not affect collection itself.
Travel loans are generally settled within 5 to 10 working days after the trip ends; petty cash can be cleared monthly or quarterly. The key is not how long the period is, but that it must be defined.
First check whether there are loans directly recorded without going through the ledger, then check whether there are write-offs not backfilled into the ledger. These two are the most common causes.