Building a Collection Report Template in Excel

A collection format in Excel is basically a structured table that tracks who owes money, how much, and whether they have paid. It is not a fancy tool. It is a ledger with aging columns, running balances, and a few conditional formatting rules that make overdue items stand out. The reason people search for a free version is that commercial dunning software costs thousands and is overkill for small operations that just need to know who has not paid this month.

What a Standard Formato De Cobranza En Excel Gratis Looks Like

The layout is usually flat. Column A holds the date. Column B has the invoice number. Column C lists the customer name. Column D shows the total amount due. Column E records the amount actually paid. Column F calculates the remaining balance. Then you add aging buckets starting in column G—current, 1–30 days past due, 31–60, 61–90, and 90+—with formulas that shift the outstanding balance into the correct bucket based on the due date. I built one for a client last year who ran a regional logistics company with about 200 active accounts. Their previous system was a stack of printed statements someone handwritten-notes on. We converted it to this flat layout, added a SUMIF summary row at the top, and the accounts team stopped calling people randomly. They started looking at the 61–90 bucket first. Revenue collection improved noticeably within two months because the right data was visible instead of buried in a notebook. The simplest version just uses these basic formulas across the row. Balance owed equals amount due minus amount paid. For aging, a nested IF function checks how many days have passed since the due date and places the remaining balance in the correct column. It looks something like: =IF(CURRENTDATE()-DUE_DATE<=30,DUE_AMOUNT,0) For the 31–60 range: =IF(AND(CURRENTDATE()-DUE_DATE>30,CURRENTDATE()-DUE_DATE<=60),DUE_AMOUNT,0) You repeat this pattern across all aging buckets. Then you use SUMIF or SUMPRODUCT on the summary row to get totals per bucket. This takes about five minutes if you already know the layout.

Common Pitfalls That Break These Templates

The first issue is hardcoding dates inside formulas. People put 2025-03-15 directly into the aging formula instead of referencing a cell. When the month changes, the whole template is broken and you have to update every single formula manually. Always keep the current date in a single cell somewhere, like H1, and reference that cell everywhere. The second problem is handling partial payments incorrectly. If a customer owes 1,000 and pays 400, the balance is 600. But if they then pay another 700, the balance goes negative. A negative balance in a collection report is confusing and makes it look like the customer still owes money when they actually overpaid. I used to solve this with: =MAX(0,Due_Amount-Paid_Total) That caps the balance at zero. But honestly, that creates its own problem—you lose visibility into overpayments. Better approach is a separate credit column. When the paid amount exceeds the due amount, the excess moves to a credit column instead of disappearing. Then when the next invoice comes, you can apply the credit automatically using a lookup. I ran into a weird edge case once where a customer had three invoices due on different dates, made one lump sum payment, and the template only applied the payment to the oldest invoice by default. That left the newest invoice sitting in the 61–90 bucket while the old one showed zero balance. The fix was adding a payment allocation helper column that used a SMALL function paired with INDEX to match payments to invoices in order of due date, then subtracting sequentially. It added two columns and maybe twenty lines of formula, but it stopped the aging buckets from lying to the collections team.

Advanced Touches That Actually Matter

Conditional formatting saves more time than people think. Apply a rule that turns the entire row red when the balance is greater than zero and the invoice is older than 60 days. Another rule highlights the aging summary row in yellow when the 90+ bucket exceeds a certain threshold. These visual cues matter more than any automated email reminder because the person sitting at the desk sees them instantly without clicking through screens. Data validation on the customer name column prevents duplicate entries that fragment your data. Set it to a dropdown list pulled from a master sheet. Without this, you will get "ACME Corp", "acme corp", and "ACME Corporation" as three separate customers, and your SUMIF totals will be wrong. This sounds obvious but it is the single most common reason these templates produce garbage numbers. An INDEX-MATCH or XLOOKUP combination can pull the customer's contact info from a separate master sheet based on the invoice number. That way the collections team can see the phone number and email address directly in the same row without switching sheets. It takes about three minutes to set up and cuts down the average call time by reducing context switching. There are tradeoffs to be aware of. This approach does not scale past roughly 5,000 rows before Excel starts to feel sluggish. Formulas recalculate every time you make any change, and if you have heavy SUMPRODUCT usage across large ranges, each edit will cause a noticeable freeze. Once you hit that wall, you need to move to a proper database or a purpose-built AR module. Also, this template does not send emails or generate dunning letters automatically. It gives you data. You still have to do the work of contacting people. If you need automation, you are looking at Power Automate, a VBA macro, or an actual AR management system. The free templates you find online often have broken formulas, hardcoded ranges that do not adjust, and no documentation explaining how to modify them. Building one yourself from scratch using the structure above usually takes less time than debugging someone else's broken file. Start with the flat table, add the aging buckets, handle partial payments properly with a credit column, and layer on the conditional formatting last. That order matters because each step depends on the one before it being correct.