Theo dõi công nợ phải thu bằng Excel: hạn thanh toán, tuổi nợ và cảnh báo quá hạn
Cách theo dõi công nợ phải thu khách hàng bằng Excel: danh sách hóa đơn, hạn thanh toán, số đã thu, số ngày quá hạn, phân tích tuổi nợ theo nhóm, tổng hợp theo khách hàng và quy trình nhắc nợ, kèm ví dụ.
Theo dõi công nợ phải thu bằng Excel là lập danh sách mọi hóa đơn bán chịu với ngày lập, hạn thanh toán, số tiền, số đã thu và số còn phải thu; công thức tự tính số ngày quá hạn, xếp từng khoản vào nhóm tuổi nợ (chưa đến hạn, quá hạn 1–30 ngày, 31–60, 61–90, trên 90 ngày) và tổng hợp theo từng khách hàng. Mỗi sáng mở file là biết ngay khách nào cần nhắc, khoản nào đang có nguy cơ khó thu. Với doanh nghiệp nhỏ bán hàng có cho nợ, đây là một trong những bảng tính giúp giữ dòng tiền hiệu quả nhất.
Bài viết thuộc nhóm Ghi sổ thu chi. Số dư phải thu tổng cần khớp với sổ cái tài khoản 131 — xem cách lập sổ cái bằng Excel.
Cấu trúc file
| Sheet | Mỗi dòng là | Cột chính |
|---|---|---|
| KhachHang | Một khách hàng | Mã KH, tên, người liên hệ, điện thoại, hạn mức nợ, số ngày được nợ |
| HoaDon | Một hóa đơn/chứng từ bán chịu | Số chứng từ, ngày, mã KH, số tiền, hạn thanh toán, đã thu, còn phải thu, ngày quá hạn, nhóm tuổi nợ, trạng thái |
| ThuTien | Một lần thu | Ngày thu, số chứng từ được thu, số tiền, hình thức |
| TongHop | Một khách hàng | Tổng còn phải thu, phần quá hạn, phân theo nhóm tuổi nợ, so với hạn mức |
Tách sheet ThuTien giúp ghi nhận cả trường hợp khách trả nhiều lần cho một hóa đơn.
Công thức
Hạn thanh toán và số đã thu
- Hạn thanh toán:
=B5+XLOOKUP(C5,KhachHang!A:A,KhachHang!F:F,30)(ngày chứng từ + số ngày được nợ của khách, mặc định 30). - Đã thu:
=SUMIFS(ThuTien!C:C,ThuTien!B:B,A5)(cộng các lần thu cho số chứng từ này). - Còn phải thu:
=D5-F5.
Số ngày quá hạn và nhóm tuổi nợ
- Ngày quá hạn:
=IF(G5<=0,0,MAX(0,TODAY()-E5)) - Nhóm tuổi nợ:
=IF(G5<=0,"Đã thu đủ",IF(H5=0,"Chưa đến hạn",IF(H5<=30,"1-30",IF(H5<=60,"31-60",IF(H5<=90,"61-90","Trên 90")))))
Định dạng có điều kiện: tô vàng nhóm 1–30, cam nhóm 31–60, đỏ từ 61 ngày trở lên. Với Excel thiết lập vùng Việt Nam, dấu phân cách đối số có thể là dấu chấm phẩy (;).
Tổng hợp theo khách hàng
- Còn phải thu:
=SUMIFS(HoaDon!G:G,HoaDon!C:C,A5) - Phần quá hạn 31–60 ngày:
=SUMIFS(HoaDon!G:G,HoaDon!C:C,A5,HoaDon!I:I,"31-60") - Vượt hạn mức:
=IF(B5>XLOOKUP(A5,KhachHang!A:A,KhachHang!E:E),"Vượt hạn mức","")
Ví dụ: bảng công nợ ngày 06/11/2026
Đơn vị: triệu đồng.
| Chứng từ | Khách | Ngày | Hạn | Số tiền | Đã thu | Còn lại | Quá hạn | Nhóm |
|---|---|---|---|---|---|---|---|---|
| HĐ001 | Khách A | 05/09 | 05/10 | 48 | 20 | 28 | 32 ngày | 31-60 |
| HĐ002 | Khách B | 20/09 | 20/10 | 35 | 0 | 35 | 17 ngày | 1-30 |
| HĐ003 | Khách C | 10/10 | 25/10 | 22 | 22 | 0 | — | Đã thu đủ |
| HĐ004 | Khách A | 15/10 | 14/11 | 40 | 0 | 40 | — | Chưa đến hạn |
| HĐ005 | Khách D | 01/08 | 31/08 | 18 | 6 | 12 | 67 ngày | 61-90 |
| HĐ006 | Khách B | 28/10 | 27/11 | 30 | 0 | 30 | — | Chưa đến hạn |
Bảng tuổi nợ
| Nhóm | Số tiền (triệu đ) | Tỷ trọng |
|---|---|---|
| Chưa đến hạn | 70 | 48,3% |
| Quá hạn 1–30 ngày | 35 | 24,1% |
| Quá hạn 31–60 ngày | 28 | 19,3% |
| Quá hạn 61–90 ngày | 12 | 8,3% |
| Trên 90 ngày | 0 | 0% |
| Tổng còn phải thu | 145 | 100% |
Nhận xét: hơn một nửa công nợ (75 triệu, 51,7%) đã quá hạn. Khách A còn nợ tổng 68 triệu, trong đó 28 triệu quá hạn hơn một tháng — nên gọi điện trước khi giao đơn mới. Khách D có khoản nhỏ nhưng đã quá hạn 67 ngày, cần xử lý dứt điểm trước khi sang nhóm trên 90 ngày. Số liệu chỉ để minh họa.
Quy trình nhắc nợ gợi ý
| Thời điểm | Việc cần làm |
|---|---|
| 3–5 ngày trước hạn | Nhắn nhắc lịch sự kèm số chứng từ, số tiền, thông tin chuyển khoản |
| Quá hạn 1–7 ngày | Gọi điện xác nhận, hỏi có vướng mắc gì không (thiếu chứng từ, sai số tiền) |
| Quá hạn 8–30 ngày | Gửi thông báo bằng văn bản/email, đề xuất lịch trả |
| Quá hạn trên 30 ngày | Tạm dừng giao hàng chịu, chủ doanh nghiệp làm việc trực tiếp |
| Quá hạn lâu, có tranh chấp | Lưu đầy đủ hồ sơ, đối chiếu công nợ có xác nhận của hai bên, tham khảo tư vấn pháp lý khi cần |
Định kỳ (tháng hoặc quý) nên gửi biên bản đối chiếu công nợ để hai bên xác nhận số dư, tránh chênh lệch kéo dài.
Chỉ số nên theo dõi
- Tỷ lệ quá hạn: công nợ quá hạn ÷ tổng phải thu.
- Số ngày thu tiền bình quân (DSO): phải thu cuối kỳ ÷ doanh thu bán chịu trong kỳ × số ngày của kỳ. DSO tăng liên tục là dấu hiệu khách trả chậm hơn hoặc chính sách cho nợ quá rộng.
- Top khách nợ: sắp xếp sheet TongHop giảm dần theo số còn phải thu.
Các khoản dự kiến thu nên được đưa vào file dòng tiền doanh nghiệp theo đúng tuần dự kiến khách trả, không phải theo hạn ghi trên hóa đơn. Thông tin liên hệ khách hàng có thể lấy từ file quản lý khách hàng.
Lỗi thường gặp
- Ghi đè số đã thu thay vì cộng các lần thu — mất lịch sử thanh toán.
- Không có hạn thanh toán riêng cho từng khách — cảnh báo sai.
- Gộp nhiều hóa đơn của một khách thành một dòng — không phân tích được tuổi nợ.
- Không đối chiếu với sổ sách — tổng phải thu trong file lệch với số dư tài khoản 131.
- Công thức bị sửa nhầm — khóa các cột tính toán theo hướng dẫn cách khóa công thức trong Excel.
Câu hỏi thường gặp
Có nên tính lãi chậm trả?
Chỉ khi hợp đồng hoặc thỏa thuận với khách có điều khoản về lãi chậm trả. Khi đó, thêm cột tính theo đúng mức và cách tính đã thỏa thuận.
Khách trả gộp nhiều hóa đơn thì ghi thế nào?
Tách số tiền thành nhiều dòng trong sheet ThuTien, mỗi dòng gắn với một số chứng từ, ưu tiên hóa đơn cũ nhất trước (trừ khi khách chỉ định khác).
Theo dõi công nợ phải trả nhà cung cấp có dùng được cấu trúc này?
Có, chỉ đổi khách hàng thành nhà cung cấp và “đã thu” thành “đã trả”; khi đó bảng giúp lên lịch thanh toán đúng hạn.
Dùng Google Sheets được không?
Được. XLOOKUP, SUMIFS, IF, TODAY và định dạng có điều kiện đều có trên Google Sheets.
Bước tiếp theo: nhập các hóa đơn bán chịu còn mở, ghi các lần thu tiền và xem bảng tuổi nợ để chọn ba khách cần liên hệ ngay tuần này. Xem thêm tại Tài chính doanh nghiệp nhỏ.