Mẫu Google Sheets Tính Lãi Vay Ngân Hàng Chuẩn 2026

Công thức tính lãi vay ngân hàng: PMT không phải lúc nào cũng đúng

Vay 500 triệu, lãi suất 9%/năm, trả trong 5 năm — số tiền phải trả hàng tháng là bao nhiêu? Nếu ngân hàng tính theo dư nợ giảm dần (hầu hết vay mua nhà, mua xe ở Việt Nam đều theo kiểu này), công thức chuẩn trong Google Sheets là hàm PMT. Nhưng vấn đề nằm ở chỗ nhiều người áp PMT cho cả khoản vay tính lãi trên dư nợ gốc (flat rate) — kết quả sai lệch có khi tới vài trăm nghìn mỗi tháng.

Cú pháp: =PMT(lãi_suất_tháng, số_kỳ, -số_tiền_vay). Với ví dụ trên, lãi suất tháng là 9%/12 = 0.75%, số kỳ là 60 tháng, công thức sẽ là =PMT(9%/12,60,-500000000), cho ra khoảng 10.379.000đ/tháng — đây là số cố định mỗi tháng, nhưng phần gốc và phần lãi trong đó thay đổi theo từng kỳ.

Muốn tách riêng phần gốc và lãi của từng tháng, dùng thêm PPMT và IPMT với cùng bộ tham số:

• =IPMT(9%/12,1,60,-500000000) — tiền lãi phải trả ở tháng 1
• =PPMT(9%/12,1,60,-500000000) — tiền gốc phải trả ở tháng 1
• Tổng hai giá trị này luôn bằng kết quả của PMT ở trên

Cái hay của việc tách riêng là bạn nhìn được xu hướng: tháng đầu tiên phần lãi chiếm gần 40% khoản trả (khoảng 3.75 triệu trên 10.38 triệu), nhưng đến tháng 50 thì phần lãi chỉ còn chưa tới 5%. Đây chính là lý do trả nợ trước hạn ở những năm đầu tiết kiệm được nhiều lãi hơn hẳn so với trả trước hạn ở cuối kỳ.

Dư nợ giảm dần vs lãi cố định trên gốc: hai kiểu tính hoàn toàn khác nhau

Nhiều người vay tiêu dùng hoặc vay tín chấp ở các công ty tài chính hay bị nhầm giữa hai cách tính này, và bảng Sheets tính sai kiểu là sai luôn cả kế hoạch trả nợ.

Tiêu chí	Dư nợ giảm dần	Lãi cố định trên gốc (flat)
Cách tính lãi	Lãi tính trên số dư nợ còn lại mỗi kỳ	Lãi tính cố định trên số tiền vay ban đầu
Số tiền trả hàng tháng	Cố định (PMT), nhưng gốc/lãi thay đổi	Gốc trả đều, lãi trả đều — nhưng lãi thực tế cao hơn nhiều
Công thức Sheets	PMT/PPMT/IPMT	Tự tính: lãi tháng = gốc vay × lãi suất năm / 12
Lãi suất thực tế (APR)	Đúng bằng lãi suất công bố	Gấp khoảng 1.8-2 lần lãi suất công bố
Thường áp dụng cho	Vay mua nhà, mua xe ngân hàng	Vay tiêu dùng, thẻ tín dụng trả góp

Với vay flat, công thức trong Sheets đơn giản hơn nhiều: lãi tháng = số tiền vay gốc × lãi suất năm / 12, cố định suốt kỳ vay. Gốc trả mỗi tháng = số tiền vay / số kỳ. Nghe thì có vẻ nhẹ nhàng vì số tiền lãi trên giấy thấp, nhưng vì lãi không giảm theo dư nợ thực tế, lãi suất hiệu dụng (APR) sẽ cao gấp gần đôi con số quảng cáo. Một khoản vay flat 10%/năm thực chất tương đương khoảng 18-19% tính theo dư nợ giảm dần — đây là điểm mà file Sheets tự làm nên có riêng một dòng quy đổi APR để không bị nhầm khi so sánh gói vay.

Xây bảng amortization schedule hoàn chỉnh trong Sheets

Bảng lịch trả nợ (amortization schedule) là phần quan trọng nhất của file, vì nó cho biết chính xác mỗi tháng bạn trả bao nhiêu, dư nợ còn lại là bao nhiêu, tính đến khi tất toán.

Cấu trúc cột nên có:

• Cột A: Kỳ (1, 2, 3... đến số kỳ vay)
• Cột B: Dư nợ đầu kỳ
• Cột C: Tiền lãi = B × lãi suất tháng
• Cột D: Tiền gốc = PMT cố định − C
• Cột E: Tổng trả trong kỳ = C + D
• Cột F: Dư nợ cuối kỳ = B − D

Ở hàng 2 (kỳ 1), dư nợ đầu kỳ (B2) tham chiếu tới ô chứa số tiền vay gốc. Từ hàng 3 trở đi, B3 = F2, cứ thế kéo công thức xuống hết số kỳ vay. Với vay 60 tháng, bạn chỉ cần nhập công thức ở hàng 2 rồi kéo xuống tới hàng 61 là xong toàn bộ bảng — không cần gõ tay từng dòng.

Một mẹo hay ít người dùng: chèn thêm cột "Trả thêm gốc" (extra payment) ngay cạnh cột D. Khi người dùng nhập một số tiền bất kỳ vào đây ở tháng nào đó, công thức dư nợ cuối kỳ tự động trừ thêm khoản này, kéo theo toàn bộ các dòng phía sau dịch chuyển — nhờ đó bạn nhìn được ngay việc trả trước 20 triệu ở tháng thứ 12 rút ngắn được bao nhiêu tháng vay và tiết kiệm bao nhiêu tiền lãi. Đây là tính năng khiến file tính lãi vay khác hẳn với máy tính lãi vay online chỉ cho ra một con số PMT tĩnh.

So sánh nhiều gói vay cùng lúc bằng Data Table

Khi cầm 3-4 báo giá vay từ các ngân hàng khác nhau, việc so sánh bằng mắt các con số lãi suất, thời hạn, phí trả trước hạn dễ gây nhầm lẫn. Cách xử lý gọn nhất trong Sheets là dựng một bảng so sánh với input là lãi suất, số kỳ, số tiền vay của từng gói, rồi dùng PMT tính ra số tiền trả hàng tháng và tổng lãi phải trả cho mỗi phương án.

Ví dụ thực tế: vay 800 triệu mua nhà, ba ngân hàng chào các gói khác nhau —

• Ngân hàng A: 8.5%/năm cố định 12 tháng đầu, sau đó thả nổi quanh 11%, vay 20 năm
• Ngân hàng B: 9.2%/năm cố định 24 tháng, sau đó 10.5%, vay 20 năm
• Ngân hàng C: 7.8%/năm cố định 6 tháng, sau đó 11.5%, vay 15 năm

Nhìn con số ưu đãi ban đầu, ngân hàng C có vẻ rẻ nhất. Nhưng khi dựng bảng amortization đầy đủ 3 giai đoạn lãi suất cho từng gói và cộng tổng lãi phải trả suốt vòng đời khoản vay, thường thì gói B lại tiết kiệm hơn nhờ thời gian ưu đãi dài và lãi suất thả nổi thấp hơn. Đây chính xác là kiểu bài toán mà bảng tính giải quyết tốt hơn nhiều so với máy tính lãi vay đơn giản trên web, vì bạn kiểm soát được từng giả định lãi suất theo từng giai đoạn.

Nếu công việc của bạn liên quan tới quản lý dòng tiền cá nhân song song với khoản vay, có thể tham khảo thêm mẫu Google Sheets quản lý ngân sách hàng tháng để theo dõi khả năng trả nợ hàng tháng có đang lấn vào các khoản chi tiêu thiết yếu hay không.

Tính lãi vay theo ngày thực tế thay vì làm tròn tháng

Một sai số phổ biến khi tự làm file Sheets: giả định mỗi tháng đúng 30 ngày hoặc lãi suất năm chia đều cho 12 tháng, trong khi ngân hàng thường tính lãi theo số ngày thực tế trong kỳ (actual/365). Với khoản vay lớn, sai số này cộng dồn qua nhiều kỳ có thể lệch vài trăm nghìn so với số ngân hàng gửi.

Cách xử lý: dùng hàm DAYS hoặc DATEDIF để lấy số ngày thực tế giữa hai kỳ trả nợ, rồi tính lãi = dư nợ × lãi suất năm × số ngày / 365. Ví dụ tháng 2 chỉ có 28 ngày trong khi tháng 7 có 31 ngày, nếu bạn có kỳ trả nợ đúng vào ngày mùng 5 hàng tháng, công thức sẽ là:

=B2 * lãi_suất_năm * DAYS(ngày_trả_kỳ_này, ngày_trả_kỳ_trước) / 365

Cách tính này khớp sát với sao kê ngân hàng hơn nhiều so với việc chia đều 12 tháng, đặc biệt hữu ích khi bạn cần đối chiếu số liệu hoặc giải trình khoản vay với kế toán. Nếu cần tính khoảng cách ngày cho nhiều mục đích khác ngoài lãi vay — như tính tuổi hợp đồng, thời gian còn lại đến hạn tất toán — có thể xem thêm cách tính số ngày, tháng, năm giữa hai mốc thời gian trong Google Sheets.

Dashboard trực quan: biểu đồ gốc-lãi và cảnh báo dư nợ

Bảng số liệu 60-240 dòng amortization đầy đủ thông tin nhưng khó đọc nhanh. Một dashboard tóm tắt phía trên bảng chi tiết giúp nhìn ra bức tranh tổng thể trong vài giây.

Ba yếu tố nên có trên dashboard:

• Biểu đồ cột chồng (stacked column chart) thể hiện tỷ lệ gốc/lãi qua từng năm — nhìn phát hiện ngay giai đoạn nào lãi chiếm phần lớn khoản trả
• Biểu đồ đường thể hiện dư nợ còn lại giảm dần theo thời gian, có thể chèn thêm đường so sánh khi có trả thêm gốc để thấy sự khác biệt trực quan
• Vài ô KPI tổng hợp: tổng lãi phải trả cả kỳ vay, tổng tiền trả cả gốc lẫn lãi, số tháng còn lại tính đến hiện tại (dùng TODAY() so sánh với ngày giải ngân)

Dùng Conditional Formatting để tô đỏ những kỳ có dư nợ vượt một ngưỡng cảnh báo, hoặc tô xanh các kỳ đã hoàn thành trả — cách này tiện khi bạn dùng file để theo dõi tiến độ trả nợ thực tế hàng tháng chứ không chỉ để dự tính ban đầu. Với người quản lý nhiều khoản vay cùng lúc (ví dụ vừa vay mua nhà vừa vay kinh doanh), việc tách riêng từng sheet cho mỗi khoản vay rồi gom về một dashboard tổng ở sheet đầu tiên giúp nhìn tổng nợ toàn bộ mà không phải mở từng file — nguyên lý này tương tự cách các mẫu SheetStore xây cho phần theo dõi hoa hồng theo bậc, có thể tham khảo cách tổ chức multi-sheet trong mẫu tính hoa hồng bán hàng theo bậc thang để lấy ý tưởng cấu trúc.

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

Cách tính lãi vay ngân hàng trong Google Sheets bằng công thức nào?

Dùng hàm PMT để tính số tiền trả hàng tháng: =PMT(lãi_suất_tháng, số_kỳ, -số_tiền_vay). Muốn tách riêng phần gốc và lãi từng kỳ thì kết hợp thêm PPMT và IPMT theo cùng tham số.

Lãi suất giảm dần và lãi suất cố định khác nhau thế nào trên bảng tính?

Lãi giảm dần tính lãi trên dư nợ còn lại mỗi tháng nên số tiền lãi giảm dần theo thời gian, dùng hàm IPMT. Lãi cố định tính lãi trên toàn bộ gốc vay ban đầu suốt kỳ hạn, chỉ cần công thức nhân đơn giản (gốc x lãi suất năm / 12).

Mẫu này có tính được lãi vay mua nhà, mua xe trả góp không?

Có. Chỉ cần nhập số tiền vay, lãi suất năm và số tháng vay, bảng tự xuất lịch trả nợ chi tiết từng kỳ, phù hợp cho vay mua nhà, mua xe, vay tiêu dùng hay vay tín chấp.

Làm sao kiểm tra tổng lãi phải trả trong suốt thời gian vay?

Dùng hàm SUM cộng toàn bộ cột lãi trong lịch trả nợ, hoặc lấy tổng số tiền đã trả (SUM cột trả hàng tháng) trừ đi số tiền vay gốc ban đầu để ra tổng lãi thực trả.