Lịch trả nợ tự lập trên bảng tính chỉ cần năm cột: kỳ, dư nợ đầu kỳ, tiền lãi, tiền gốc, dư nợ cuối kỳ. Với kiểu trả gốc đều, tiền gốc mỗi kỳ bằng số tiền vay chia số kỳ, tiền lãi bằng dư nợ đầu kỳ nhân lãi suất kỳ. Với kiểu trả đều hằng tháng, tổng tiền trả mỗi kỳ tính bằng hàm PMT, rồi tách lãi và gốc bằng IPMT và PPMT.
Tự lập lịch giúp bạn thấy trước tiền phải trả từng tháng, kiểm lại lịch ngân hàng đưa ra, và thử các kịch bản như lãi tăng hay trả bớt gốc. Bài hướng dẫn dựng cả hai kiểu, chuyển sang tính lãi theo ngày thực tế cho sát với cách ngân hàng tính, và liệt kê những nguyên nhân hay gây lệch. Mọi con số là ví dụ giả định.
Hai kiểu trả nợ phổ biến khác nhau thế nào
Trước khi dựng bảng, cần biết khoản vay của bạn theo kiểu nào. Hợp đồng hoặc lịch trả nợ dự kiến thường ghi rõ. Ví dụ giả định: vay 300 triệu đồng trong 60 tháng, lãi 10%/năm.
| Đặc điểm | Trả gốc đều, lãi trên dư nợ giảm dần | Trả đều hằng tháng (niên kim) |
|---|---|---|
| Tiền gốc mỗi tháng | Bằng nhau: 300 triệu chia 60 bằng 5 triệu | Nhỏ ở đầu, lớn dần về cuối |
| Tiền lãi mỗi tháng | Lớn ở đầu, giảm dần | Lớn ở đầu, giảm dần |
| Tổng tiền trả tháng đầu | 5 triệu gốc + 2,5 triệu lãi = 7,5 triệu | khoảng 6,37 triệu |
| Tổng tiền trả tháng cuối | khoảng 5,04 triệu | vẫn khoảng 6,37 triệu |
| Tổng tiền lãi cả khoản vay | thấp hơn | cao hơn một chút vì gốc giảm chậm hơn |
Kiểu trả gốc đều nặng ở những tháng đầu nhưng tổng lãi thấp hơn; kiểu trả đều dễ lập ngân sách vì số tiền cố định. Không kiểu nào "đúng" hơn, nhưng lịch bạn lập phải dùng đúng kiểu của hợp đồng, nếu không mọi phép so sau đó đều sai.
Dựng lịch trả gốc đều trong năm phút
Mở bảng tính trống, làm theo thứ tự:
- Khu nhập liệu ở trên cùng: ô số tiền vay (300 triệu), ô lãi năm (10%), ô số kỳ (60). Đặt tên hoặc ghi nhớ địa chỉ ô để công thức đọc lại.
- Tính ô gốc mỗi kỳ bằng số tiền vay chia số kỳ.
- Hàng tiêu đề bảng: Kỳ, Dư nợ đầu kỳ, Lãi, Gốc, Tổng trả, Dư nợ cuối kỳ.
- Hàng kỳ 1: dư nợ đầu kỳ bằng số tiền vay; lãi bằng dư nợ đầu kỳ nhân lãi năm chia 12; gốc bằng ô gốc mỗi kỳ; tổng trả bằng lãi cộng gốc; dư nợ cuối kỳ bằng dư nợ đầu kỳ trừ gốc.
- Hàng kỳ 2: dư nợ đầu kỳ bằng dư nợ cuối kỳ của hàng trên; các cột khác chép công thức xuống.
- Kéo xuống tới kỳ 60. Kiểm: dư nợ cuối kỳ 60 phải bằng 0; tổng cột gốc phải bằng số tiền vay.
- Thêm một ô tổng cột lãi để thấy tổng tiền lãi cả khoản vay.
- Cố định dòng tiêu đề và định dạng số theo kiểu có dấu phân cách hàng nghìn, làm tròn tới đồng; bảng sáu mươi dòng đọc trên điện thoại sẽ dễ hơn nhiều.
Với ví dụ trên, tổng tiền lãi theo cách chia 12 khoảng 76 triệu đồng. Đổi ô lãi năm, toàn bộ bảng tự tính lại; đây là cách nhanh nhất để thấy tiền trả thay đổi bao nhiêu khi lãi thả nổi tăng một hai điểm phần trăm.
Dựng lịch trả đều bằng PMT, IPMT, PPMT
Với kiểu trả đều, tổng tiền trả mỗi kỳ là một con số cố định tính bằng hàm PMT với ba tham số: lãi suất kỳ (lãi năm chia 12), số kỳ, và số tiền vay. Trong hầu hết bảng tính, PMT trả về số âm vì đó là tiền đi ra; thêm dấu trừ phía trước nếu muốn hiện số dương. Với 300 triệu, 60 kỳ, 10%/năm, kết quả khoảng 6,37 triệu đồng.
Để tách từng kỳ, IPMT cho phần lãi và PPMT cho phần gốc của kỳ thứ k, với cùng ba tham số cộng số thứ tự kỳ. Bạn cũng có thể không dùng hai hàm này: lãi bằng dư nợ đầu kỳ nhân lãi tháng, gốc bằng tiền trả cố định trừ lãi, dư nợ cuối kỳ bằng dư nợ đầu kỳ trừ gốc. Hai cách phải cho cùng một kết quả; nếu khác, có ô nào đó đang trỏ sai.
Một cách kiểm nhanh: ở kỳ đầu, lãi phải bằng 300 triệu nhân 10% chia 12, tức 2,5 triệu; gốc là phần còn lại, khoảng 3,87 triệu. Ở kỳ cuối, dư nợ cuối kỳ phải về 0, có thể lệch vài đồng do làm tròn. Tổng tiền lãi cả khoản vay theo kiểu này khoảng 82 triệu đồng, cao hơn kiểu trả gốc đều ở bảng trước, vì phần gốc ở những năm đầu giảm chậm hơn.
Nếu khoản vay của bạn có thời gian ân hạn gốc, tức một số tháng đầu chỉ trả lãi, cho các hàng ân hạn có gốc bằng 0 và tính PMT cho phần còn lại với số kỳ đã trừ đi số tháng ân hạn. Đây là chỗ các mẫu bảng tính dựng sẵn thường không xử lý, và cũng là chỗ người vay hay bất ngờ khi hết ân hạn tiền trả nhảy lên.
Chuyển sang tính lãi theo ngày thực tế
Vì sao cần. Nhiều ngân hàng tính tiền lãi theo số ngày thực tế giữa hai kỳ trả, năm 365 ngày, thay vì chia đều lãi năm cho 12. Với 300 triệu dư nợ và lãi 10%/năm, tháng 31 ngày cho tiền lãi khoảng 2,55 triệu, tháng 28 ngày khoảng 2,30 triệu, trong khi cách chia 12 luôn ra 2,5 triệu.
Thêm hai cột. Thêm cột ngày trả của từng kỳ và cột số ngày, bằng ngày trả kỳ này trừ ngày trả kỳ trước. Công thức lãi đổi thành dư nợ đầu kỳ nhân lãi năm nhân số ngày chia 365.
Kỳ đầu tiên thường lẻ ngày. Ngày giải ngân hiếm khi trùng ngày trả cố định hằng tháng. Ví dụ giải ngân ngày 20, ngày trả cố định là ngày 5 của tháng sau: kỳ đầu chỉ khoảng nửa tháng. Có hợp đồng dồn sang tháng kế tiếp, kỳ đầu dài gần một tháng rưỡi. Tiền lãi kỳ đầu vì vậy khác hẳn các kỳ sau; đừng ngạc nhiên khi thấy con số này lệch.
Với kiểu trả đều. Khi lãi tính theo ngày, ngân hàng thường vẫn giữ tiền trả cố định và để kỳ cuối điều chỉnh phần chênh, hoặc tính lại tiền trả định kỳ. Hỏi ngân hàng cách họ xử lý để bảng của bạn bám theo cùng một quy tắc.
Lịch của bạn lệch lịch ngân hàng: dò từng nguyên nhân
Lệch vài đồng do làm tròn là bình thường. Lệch hàng chục nghìn trở lên thì thường rơi vào một trong các nguyên nhân dưới đây:
| Hiện tượng | Nguyên nhân hay gặp | Cách kiểm |
|---|---|---|
| Lệch đều mỗi tháng một ít | Bạn chia 12, ngân hàng tính theo ngày thực tế | Thêm cột số ngày như phần trên |
| Lệch lớn ở kỳ đầu | Kỳ đầu lẻ ngày | Hỏi ngày giải ngân chính xác và ngày trả kỳ đầu |
| Lệch tăng dần từ một mốc | Lãi suất đã điều chỉnh, bạn chưa cập nhật | Đối chiếu thông báo điều chỉnh lãi |
| Gốc mỗi kỳ không đều dù hợp đồng ghi trả gốc đều | Có kỳ ân hạn gốc, hoặc đã trả bớt gốc | Xem phụ lục và xác nhận trả trước |
| Tổng trả cao hơn tổng gốc cộng lãi | Có phí định kỳ cộng vào kỳ trả | Đọc biểu phí và sao kê khoản vay |
Khi đã thử hết mà vẫn lệch, đề nghị ngân hàng cung cấp lịch trả nợ chi tiết có cột số ngày và lãi suất áp dụng từng kỳ. Có lịch đó, so từng hàng là thấy ngay chỗ khác.
Nhờ AI dựng bảng mà vẫn giữ dữ liệu ở máy mình
Chatbot AI làm khá tốt việc viết công thức bảng tính và giải thích hàm, kể cả cho người chưa từng dùng PMT. Câu lệnh mẫu: "Hướng dẫn dựng trong bảng tính một lịch trả nợ trả đều hằng tháng, số tiền vay ở ô B1, lãi năm ở B2, số kỳ ở B3, ngày giải ngân ở B4, ngày trả cố định là ngày 5 hằng tháng, lãi tính theo số ngày thực tế chia 365. Ghi công thức cụ thể cho từng cột của hàng đầu tiên và hàng thứ hai."
Cách này giữ được nguyên tắc quan trọng: AI chỉ thấy cấu trúc, còn số liệu thật bạn tự điền trên máy. Không cần dán lịch trả nợ của ngân hàng có tên, số hợp đồng, số tài khoản vào chatbot. Nếu thật sự cần nhờ AI so hai lịch, hãy xoá các cột thông tin định danh, chỉ giữ cột kỳ, ngày, dư nợ, lãi, gốc.
AI có thể viết sai cú pháp hàm cho phiên bản bảng tính bạn đang dùng, nhầm dấu phẩy với dấu chấm phẩy theo thiết lập vùng miền, hoặc đảo thứ tự tham số. Khi công thức báo lỗi, chép nguyên thông báo lỗi và hỏi lại; kiểm kết quả bằng các mốc ở trên như lãi kỳ đầu và dư nợ cuối bằng 0.
Những kịch bản nên thử khi đã có bảng
Bảng trả nợ có giá trị nhất khi bạn dùng nó để hỏi "nếu thì sao". Một vài kịch bản đáng thử trên một bản sao của bảng:
- Lãi năm tăng thêm 1, 2, 3 điểm phần trăm từ một kỳ nhất định, mô phỏng hết ưu đãi.
- Trả bớt một khoản gốc ở kỳ thứ 24, rồi so hai cách: giữ số kỳ hay giữ tiền trả.
- Gộp tiền trả của hai khoản vay vào một bảng để thấy tổng gánh nặng hằng tháng của gia đình.
- Một kỳ trễ hạn vài ngày, cộng lãi quá hạn theo hợp đồng, để thấy chi phí phát sinh.
- So tổng tiền lãi nếu rút ngắn thời hạn vay ngay từ đầu.
- Thu nhập giảm trong sáu tháng: xem tiền trả hằng tháng chiếm bao nhiêu phần thu nhập còn lại, để biết cần quỹ dự phòng cỡ nào.
Mỗi kịch bản là một con số cụ thể bạn có thể mang đi hỏi ngân hàng. Bảng không quyết định thay bạn nên vay bao lâu hay trả trước lúc nào, nhưng giúp mọi quyết định dựa trên con số đã kiểm.
Câu hỏi thường gặp
Hàm PMT trả về số âm có phải lỗi không?
Không. Bảng tính quy ước tiền đi ra là số âm. Thêm dấu trừ trước hàm hoặc nhập số tiền vay là số âm để kết quả hiện số dương.
Nên chia lãi năm cho 12 hay tính theo ngày?
Chia 12 đủ để ước lượng và lập ngân sách. Khi muốn đối chiếu chính xác với lịch ngân hàng, hãy tính theo đúng cách hợp đồng quy định, phần lớn là theo số ngày thực tế chia 365.
Vì sao kỳ trả đầu tiên cao hoặc thấp bất thường?
Thường do kỳ đầu lẻ ngày: từ ngày giải ngân tới ngày trả cố định có thể ngắn hơn hoặc dài hơn một tháng. Tiền lãi kỳ đầu tính theo đúng số ngày đó nên khác các kỳ sau.
Trả gốc đều hay trả đều tốn ít lãi hơn?
Với cùng số tiền vay, lãi suất và thời hạn, trả gốc đều có tổng tiền lãi thấp hơn vì dư nợ giảm nhanh hơn ở những năm đầu. Đổi lại, tiền trả những tháng đầu cao hơn. Cách nào hợp tuỳ dòng tiền của bạn.
Có mẫu bảng tính trả nợ sẵn không?
Nhiều bảng tính có mẫu khoản vay dựng sẵn. Dùng mẫu thì nhanh, nhưng phải kiểm mẫu đang tính theo kiểu trả nào và chia 12 hay theo ngày. Tự dựng một lần theo bài giúp bạn hiểu từng con số.
Đưa lịch trả nợ của ngân hàng vào AI để so có an toàn không?
Chỉ nên đưa sau khi đã xoá họ tên, số hợp đồng, số tài khoản và mọi thông tin định danh, chỉ giữ cột số liệu. Tốt hơn là nhờ AI viết công thức rồi tự so trên máy của mình.