Công thức Excel tính lãi cầm đồ: lập bảng theo dõi tiền lãi và ngày đến hạn

Công thức Excel tính lãi cầm đồ theo ngày chỉ cần một dòng: =ROUND(Số_tiền_vay*Lãi_năm/365*(Ngày_chuộc−Ngày_cầm);0). Ví dụ giả định: vay 8 triệu đồng ngày 5/10/2026, lãi 18%/năm, chuộc ngày 19/11/2026. Excel trừ hai ngày ra 45 ngày, tiền lãi là 8.000.000 × 18% ÷ 365 × 45 ≈ 177.534 đồng.

Từ công thức đó, bạn dựng được một bảng nhỏ khoảng mười cột: tự đếm ngày, tự tính lãi đến hôm nay, tự báo hợp đồng nào sắp đến hạn. Người cầm nhiều món hoặc cầm nhiều lần trong năm sẽ thấy ngay mình đang trả bao nhiêu và có tiệm nào tính lệch hay không.

Hướng dẫn dưới đây dùng được cho cả Excel và Google Sheets. Tên hàm giống nhau; chỉ khác dấu ngăn đối số tuỳ cài đặt vùng của máy, sẽ nói rõ ở phần lỗi hay gặp.

Dựng khung bảng: mười một cột cần có

Mở một trang tính mới, dòng 1 làm tiêu đề, mỗi hợp đồng cầm đồ là một dòng từ dòng 2 trở xuống. Cột nhập tay để nền trắng, cột có công thức tô nền xám nhạt để khỏi vô tình gõ đè.

Cột G để trống thì bảng hiểu là chưa chuộc và tính lãi tới ngày hôm nay, mỗi lần mở tệp con số tự nhảy. Khi đi chuộc, gõ ngày chuộc vào G, số lãi đứng yên để đối chiếu với biên nhận của tiệm. Cột D nên là tiền thực cầm về tay, không phải con số tiệm ghi nếu hai số lệch nhau.

Viết công thức từng bước và kiểm tra bằng ví dụ

Nhập dòng mẫu trước, kiểm khớp với số tính tay rồi mới kéo công thức xuống các dòng dưới.

1. Gõ C2 là 05/10/2026, D2 là 8000000, E2 là 18%. Bấm Ctrl+1 kiểm C2 đang ở định dạng ngày, E2 ở định dạng phần trăm.
2. Ô F2 gõ =EDATE(C2;1), kết quả 05/11/2026. Nếu tiệm tính kỳ 30 ngày thay vì một tháng lịch, dùng =C2+30, kết quả 04/11/2026.
3. Để G2 trống tạm thời. Ô H2 gõ =IF(G2="";TODAY();G2)-C2 rồi định dạng ô là số, không phải ngày, nếu không Excel sẽ hiện một ngày vô nghĩa kiểu năm 1900.
4. Ô I2 gõ =ROUND(D2*E2/365*H2;0). Thử gõ 19/11/2026 vào G2: H2 phải ra 45 và I2 phải ra 177534. Khớp thì công thức đúng.
5. Ô K2 gõ =D2+I2+J2. Chọn ô F2 đến K2, kéo nút vuông góc dưới bên phải xuống cho các dòng tiếp theo.

Phép trừ G2−C2 không tính ngày cầm. Nếu hợp đồng ghi tính cả ngày nhận tiền lẫn ngày chuộc, sửa H2 thành =IF(G2="";TODAY();G2)-C2+1. Chênh một ngày nghe nhỏ, nhưng với khoản 50 triệu đồng ở mức 18%/năm là khoảng 24.658 đồng mỗi lần chuộc; hỏi tiệm đếm ngày thế nào rồi chỉnh cho khớp.

Tiệm báo lãi theo đồng/triệu/ngày hoặc %/tháng thì sửa công thức thế nào

Nhiều tiệm nói lãi kiểu "500 đồng một triệu một ngày". Cách gọn nhất là thêm cột L ghi đúng con số tiệm báo, rồi để cột E tự quy đổi ra %/năm bằng công thức =L2*365/1000000. Gõ 500 vào L2, E2 hiện 18,25%; gõ 548, E2 hiện khoảng 20%. Mọi cột phía sau giữ nguyên, bảng vẫn chạy.

Nếu tiệm báo theo tháng, ví dụ 1,5%/tháng, có hai cách. Cách thứ nhất quy ra năm bằng =L2*12 rồi tính theo ngày như trên; đây là cách công bằng với người vay khi chuộc giữa kỳ. Cách thứ hai đúng kiểu nhiều tiệm làm là tính tròn tháng: số tháng =ROUNDUP(H2/30;0), tiền lãi =D2*L2*số_tháng. Lập cả hai cột cạnh nhau giúp bạn thấy ngay khoản chênh khi tiệm làm tròn lên một tháng.

Thêm một cột kiểm trần: =IF(E2>20%;"Vượt trần";""). Với cột E đã quy đổi, mọi dòng hiện chữ "Vượt trần" là hợp đồng có lãi cao hơn giới hạn 20%/năm tại Điều 468 Bộ luật Dân sự 2015. Nếu có phí định kỳ, cộng phí quy năm vào E trước khi so thì mới thấy chi phí thật.

Nhắc ngày đến hạn và tính phần lãi quá hạn

Cột số ngày còn lại. Thêm cột M với công thức =IF(G2="";F2-TODAY();""). Số dương là còn bao nhiêu ngày, số âm là đã quá hạn bấy nhiêu ngày. Hợp đồng đã chuộc thì ô để trống.

Tô màu tự động. Chọn cả cột M, vào Conditional Formatting (Định dạng có điều kiện), đặt quy tắc ô nhỏ hơn hoặc bằng 3 tô cam, nhỏ hơn 0 tô đỏ. Google Sheets có mục tương tự trong menu Định dạng. Tên mục có thể khác theo phiên bản và ngôn ngữ giao diện.

Lãi quá hạn theo hợp đồng. Nếu hợp đồng thoả thuận lãi trên nợ gốc quá hạn bằng 150% lãi trong hạn, theo cách tính tại khoản 5 Điều 466 Bộ luật Dân sự, thêm cột N: =IF(H2+C2>F2;ROUND(D2*E2*150%/365*(H2+C2-F2);0);0). Công thức chỉ tính phần vượt quá ngày đến hạn; phần trong hạn vẫn nằm ở cột I.

Đóng lãi giữa chừng. Lập trang thứ hai tên "Đóng lãi" gồm mã biên nhận, ngày đóng, số tiền. Ở trang chính thêm cột "Đã đóng" bằng =SUMIFS('Đóng lãi'!C:C;'Đóng lãi'!A:A;A2), rồi cột "Còn nợ lãi" bằng tiền lãi trừ đã đóng. Gặp lần trả bớt gốc, cách gọn nhất là đóng dòng cũ ở ngày trả và mở dòng mới với số gốc còn lại.

Lỗi hay gặp và cách sửa

Bảng không ra số đúng thì gần như luôn rơi vào một trong các lỗi sau:

• Ngày bị hiểu thành chữ. Ô ngày căn trái, công thức báo #VALUE!, nghĩa là Excel coi đó là chữ. Thường do máy cài định dạng tháng trước ngày sau, gõ 19/11 không hiểu. Vào cài đặt vùng của máy hoặc File › Settings › Locale trên Google Sheets chọn Việt Nam, rồi gõ lại ngày.
• Ngày bị đảo tháng. Gõ 05/10 nhưng máy hiểu là ngày 10 tháng 5. Không báo lỗi nên nguy hiểm hơn; kiểm bằng công thức =TEXT(C2;"dd mmmm yyyy") ở ô bên cạnh.
• Dấu ngăn đối số. Bài này dùng dấu chấm phẩy, đúng với máy để vùng Việt Nam. Máy để vùng tiếng Anh thì đổi thành dấu phẩy, nếu không Excel báo công thức có vấn đề.
• Gõ lãi 18 thay vì 18%. Tiền lãi ra gấp 100 lần. Định dạng cột E là phần trăm trước khi gõ.
• Làm tròn từng ngày rồi cộng. Tính lãi một ngày, làm tròn, nhân số ngày sẽ lệch vài trăm đến vài nghìn đồng so với tiệm. Để ROUND bọc ngoài cùng như công thức mẫu.
• Quên khoá cột công thức. Ai đó gõ đè số vào cột I, bảng vẫn chạy nhưng sai im lặng. Dùng Protect Range hoặc ít nhất tô nền xám như đã nói ở trên.

Trước khi tin bảng, thử một khoản dễ tính nhẩm: 10 triệu đồng, 20%/năm, 73 ngày. Kết quả phải đúng 400.000 đồng, vì 73 ngày là một phần năm của 365 ngày. Ra số khác là còn lỗi ở một trong các mục trên.

Bảng tính chỉ là công cụ đối chiếu. Khi số của bạn lệch với tiệm, đừng vội kết luận ai sai; mang bảng ra hỏi tiệm đếm ngày thế nào, làm tròn ra sao, có cộng phí gì. Phần lớn chênh lệch nằm ở ba chỗ đó, và hiểu được rồi thì lần cầm sau bạn biết hỏi ngay từ đầu.

Trang tổng hợp: nhìn một lần biết mình đang nợ bao nhiêu

Khi bảng có từ ba hợp đồng trở lên, nên thêm một trang thứ ba tên "Tổng hợp" chỉ gồm vài ô. Tổng gốc đang cầm: =SUMIFS(Chinh!D:D;Chinh!G:G;""), tức cộng số tiền vay của các dòng chưa có ngày chuộc. Tổng lãi đang chạy đến hôm nay: =SUMIFS(Chinh!I:I;Chinh!G:G;""). Số hợp đồng còn dưới bốn ngày là đến hạn: =COUNTIFS(Chinh!M:M;"<=3";Chinh!G:G;""). Đổi chữ Chinh thành đúng tên trang chính của bạn; tên trang có dấu cách hoặc dấu tiếng Việt thì đặt trong cặp nháy đơn.

Ô đáng xem nhất là tổng tiền lãi đã trả trong năm, cộng cột I của các dòng đã chuộc cùng toàn bộ trang "Đóng lãi". Nhiều người chỉ nhìn từng lần đóng vài trăm nghìn nên không để ý; cộng cả năm lại mới thấy con số đủ để cân nhắc phương án khác, chẳng hạn để dành một khoản dự phòng nhỏ thay vì đi cầm mỗi khi kẹt tiền.

Nếu muốn theo dõi từng tài sản, thêm cột "Số lần cầm" bằng =COUNTIF(B:B;B2). Một chiếc xe hay chiếc nhẫn bị cầm đi cầm lại năm, sáu lần trong năm là tín hiệu rõ rằng khoản thiếu hụt đang lặp lại theo chu kỳ, và lãi cầm đồ đang trở thành một khoản chi cố định hằng tháng.

Câu hỏi thường gặp