Thời gian đọc ước tính: 12 phút
Những điểm chính
- Cách thiết lập bảng tính tự động phân bổ nợ gốc và lãi vay.
- Sử dụng các hàm tài chính chuyên dụng: PMT, PPMT, IPMT và SEQUENCE.
- Xây dựng mô hình linh hoạt cho phép trả trước hạn (Prepayments) để giảm lãi hoặc giảm thời gian vay.
- Công thức tính toán chính xác số tiền tiết kiệm được khi thay đổi kế hoạch trả nợ.
Việc quản lý các khoản vay thế chấp, vay mua nhà hay vay tiêu dùng cá nhân đòi hỏi sự chính xác cao về số liệu. Ngân hàng thường cung cấp cho bạn một bảng lịch trình trả nợ, nhưng việc tự mình xây dựng một hệ thống theo dõi trên Excel mang lại quyền kiểm soát tốt hơn. Bạn có thể mô phỏng các kịch bản khác nhau, chẳng hạn như: “Điều gì sẽ xảy ra nếu mình trả thêm 5 triệu mỗi tháng?” hay “Lãi suất thay đổi sẽ ảnh hưởng thế nào đến dòng tiền?”.
Bài viết này sẽ hướng dẫn bạn từng bước xây dựng một bảng lịch trả nợ vay (Loan Amortization Schedule) hoàn chỉnh. Chúng ta sẽ đi từ mô hình cơ bản nhất đến mô hình nâng cao cho phép xử lý các khoản trả trước hạn.
1. Thiết lập bảng tính trả nợ cơ bản
Để bắt đầu, chúng ta cần một khu vực để nhập liệu các thông số của khoản vay. Đây là nền tảng để Excel thực hiện các tính toán phía sau.
Tôi sẽ sử dụng cột B để chứa các giá trị đầu vào quan trọng sau đây:
- Số tiền vay gốc (Principal Amount – Ô B1): Tổng số tiền bạn vay ngân hàng. Ví dụ: 100,000 USD (hoặc đơn vị tiền tệ bạn muốn).
- Thời hạn vay (Duration – Ô B2): Tổng thời gian vay tính bằng năm. Ví dụ: 20 năm.
- Số lần thanh toán mỗi năm (Payments per year – Ô B3): Nếu bạn trả góp hàng tháng, hãy nhập 12. Nếu trả hàng tuần, nhập 52. Ví dụ: 12.
- Lãi suất hàng năm (Annual Interest Rate – Ô B4): Mức lãi suất quy định trên hợp đồng. Ví dụ: 6.5%.
Các giá trị đầu vào cho mẫu lịch trình trả nợ vay
Sau khi đã có dữ liệu đầu vào, chúng ta sẽ xây dựng khung sườn cho bảng lịch trả nợ. Bảng này sẽ bao gồm 5 cột chính để theo dõi dòng tiền qua từng kỳ thanh toán.
Mẫu lịch trình trả nợ vay trống
Tạo số thứ tự kỳ thanh toán (Tháng)
Thay vì gõ thủ công từ 1 đến 240 (cho 20 năm), chúng ta sử dụng hàm SEQUENCE để tạo danh sách tự động. Điều này giúp bảng tính linh hoạt thay đổi độ dài khi bạn điều chỉnh số năm vay.
Công thức tại ô đầu tiên của cột Tháng:
=SEQUENCE(B2*B3)
Tính toán tháng bằng hàm sequence
Hàm SEQUENCE sẽ lấy số năm (B2) nhân với số kỳ trả mỗi năm (B3) để tạo ra một dãy số liên tục tương ứng với tổng số kỳ phải trả.
Tính toán số tiền phải trả hàng tháng (Hàm PMT)
Đây là con số quan trọng nhất mà người đi vay quan tâm: “Mỗi tháng tôi phải đóng bao nhiêu tiền?”. Chúng ta sử dụng hàm PMT (Payment).
Công thức tại ô E6:
=PMT($B$4/$B$3, $B$2*$B$3, $B$1)
Tính toán khoản thanh toán hàng tháng bằng hàm PMT
Giải thích chi tiết các tham số:
$B$4/$B$3: Lãi suất áp dụng cho mỗi kỳ. Vì lãi suất ngân hàng (B4) là theo năm, ta phải chia cho 12 (B3) để ra lãi suất tháng.$B$2*$B$3: Tổng số kỳ thanh toán (20 năm * 12 tháng = 240 kỳ).$B$1: Giá trị hiện tại của khoản vay (100,000).
Lưu ý việc sử dụng ký hiệu $ (như $B$1) để tạo tham chiếu tuyệt đối. Điều này đảm bảo khi bạn sao chép công thức xuống các hàng dưới, Excel vẫn lấy đúng dữ liệu từ ô đầu vào mà không bị trượt dòng.
Tách biệt phần gốc trả hàng tháng (Hàm PPMT)
Trong khoản trả cố định hàng tháng, tỷ lệ giữa gốc và lãi sẽ thay đổi theo thời gian. Ban đầu, bạn trả chủ yếu là lãi, càng về sau phần gốc càng tăng lên. Hàm PPMT (Principal Payment) giúp tính toán phần gốc cụ thể trong từng kỳ.
Công thức:
=PPMT($B$4/$B$3, D7, $B$2*$B$3, $B$1)
Tính toán phần gốc trong Lịch Trả Nợ Thế Chấp.
Điểm khác biệt ở đây là tham số thứ hai (D7). D7 là số thứ tự của kỳ thanh toán hiện tại (tháng 1, tháng 2,…). Vì không có dấu $, giá trị này sẽ tự động chạy theo từng dòng (D8, D9…) khi bạn kéo công thức.
Tính toán phần lãi trả hàng tháng
Có hai cách để tính số tiền lãi. Cách phức tạp là dùng hàm IPMT. Tuy nhiên, vì chúng ta đã có Tổng tiền phải trả và Tiền gốc, cách đơn giản và nhanh nhất là dùng phép trừ.
Công thức:
=E7-F7
Tính toán Phần Lãi trong khoản thanh toán hàng tháng
Nguyên tắc tài chính cơ bản: Tổng tiền trả = Tiền Gốc + Tiền Lãi. Do đó, lấy Tổng trừ đi Gốc sẽ ra Lãi.
Tính dư nợ còn lại cuối kỳ (Outstanding Balance)
Con số này cho biết sau khi đóng tiền, bạn còn nợ ngân hàng bao nhiêu.
Công thức:
=$B$1+SUM($F$7:F7)
Tính toán Dư Nợ Vay hàng tháng
Cơ chế hoạt động:
- Công thức bắt đầu với số nợ gốc ban đầu (
$B$1). - Hàm
SUM($F$7:F7)tính tổng lũy kế số tiền gốc đã trả từ tháng đầu tiên đến tháng hiện tại. - Lưu ý quan trọng: Các hàm tài chính như PMT hay PPMT thường trả về số âm (thể hiện dòng tiền chi ra). Do đó, khi cộng số âm này vào nợ gốc dương, ta sẽ có kết quả là dư nợ giảm dần.
Tổng hợp lãi suất phải trả
Để biết tổng số tiền lãi bạn sẽ mất cho ngân hàng sau 20 năm, hãy dùng hàm SUM cho cột Lãi.
=SUM(G7:G1000)
Tổng Lãi Phải Trả
Như vậy, bạn đã hoàn thành một bảng tính cơ bản. Tuy nhiên, thực tế cuộc sống thường phức tạp hơn khi bạn có thêm thu nhập và muốn trả nợ trước hạn.
Tải xuống Mẫu Lịch Trả Nợ Vay Cơ Bản Tại Đây
2. Xây dựng lịch trả nợ nâng cao với khoản trả trước (Prepayments)
Đây là phần giá trị nhất của bài viết. Nhiều người vay muốn trả thêm một khoản tiền (extra payment) khi có thưởng tết hoặc thu nhập phụ để giảm bớt gánh nặng nợ nần. Khi trả trước, bạn thường có hai lựa chọn:
- Giảm thời gian vay (Reduce Term): Giữ nguyên số tiền đóng hàng tháng, nhưng nợ sẽ hết sớm hơn.
- Giảm số tiền đóng hàng tháng (Reduce Payment): Giữ nguyên thời gian vay (ví dụ vẫn là 20 năm), nhưng số tiền phải đóng mỗi tháng sẽ ít đi.
Chúng ta sẽ nâng cấp bảng tính để xử lý cả hai kịch bản này một cách tự động.
Trước hết, thêm một ô chọn trong phần Input (Ô B5) để người dùng chọn chiến lược: “Reduce Term” hoặc “Reduce Payment”. Đồng thời thêm một cột “Extra Payment” trong bảng dữ liệu chính.
Nhập chuỗi tháng cho lịch trả nợ thế chấp vay
Công thức tính tiền trả hàng tháng linh hoạt
Công thức này cần kiểm tra xem người dùng chọn chiến lược nào (ở ô B5) để đưa ra con số chính xác.
=IF($B$5="Reduce Term", PMT($B$4/$B$3, $B$2*$B$3, $B$1), PMT($B$4/$B$3, $B$2*$B$3-D8+1, IF(D8=1, $B$1, I7), 0))
Thanh toán hàng tháng với khoản trả thêm
Phân tích logic của công thức:
-
Trường hợp 1: Reduce Term (Giảm thời gian)
Nếu chọn phương án này, công thứcPMT($B$4/$B$3, $B$2*$B$3, $B$1)được kích hoạt. Nó giống hệt công thức ở phần cơ bản vì số tiền trả mỗi tháng không đổi. Khoản trả thêm sẽ chạy thẳng vào việc trừ nợ gốc, làm nợ hết sớm hơn. -
Trường hợp 2: Reduce Payment (Giảm tiền đóng)
Đây là phần phức tạp. Khi bạn trả thêm tiền, Excel cần coi mỗi tháng tiếp theo như một khoản vay mới với dư nợ mới và thời gian còn lại ngắn hơn.$B$2*$B$3-D8+1: Tính toán thời gian còn lại của khoản vay (Tổng kỳ hạn – Kỳ hiện tại + 1).IF(D8=1, $B$1, I7): Xác định số dư nợ hiện tại để tính lãi. Nếu là tháng 1, lấy nợ gốc ban đầu. Các tháng sau, lấy dư nợ của tháng trước đó (cột I).
Công thức tính phần Gốc trả hàng tháng (Nâng cao)
Khi có trả trước, phần gốc trong khoản thanh toán định kỳ cũng biến động theo logic của chiến lược bạn chọn.
=IF($B$5="Reduce Term", IF(ABS(E8-G8)>I7, -I7, E8-G8), PPMT($B$4/$B$3, 1, $B$2*$B$3-D8+1, IF(D8=1, $B$1, I7)))
Thanh toán gốc mỗi tháng trong mẫu tính toán trả nợ thế chấp
Phân tích chi tiết:
-
Với Reduce Term:
Công thứcIF(ABS(E8-G8)>I7, -I7, E8-G8)xử lý tình huống đặc biệt ở tháng cuối cùng. Khi bạn trả thêm tiền, nợ sẽ hết sớm. Nếu không có hàm IF này, Excel sẽ tiếp tục trừ tiền dù nợ đã hết. Hàm này kiểm tra: Nếu số tiền định trả lớn hơn số nợ còn lại (I7), thì chỉ trả đúng số nợ còn lại (-I7) để đưa dư nợ về 0. -
Với Reduce Payment:
Sử dụng hàmPPMTnhưng với các tham số động tương tự như phần tính PMT ở trên. Nó tính lại phần gốc dựa trên dư nợ mới nhất sau khi đã trừ khoản trả thêm.
Công thức tính phần Lãi trả hàng tháng (Nâng cao)
Tương tự, phần lãi cũng cần điều chỉnh để khớp với số dư nợ thực tế đang giảm nhanh hơn dự kiến.
=IF($B$5="Reduce Term", IPMT($B$4/$B$3, 1, $B$2*$B$3+1-D8, IF(D8=1, $B$1, I7)), E8-F8)
Thanh toán lãi mỗi tháng trong mẫu tính toán trả nợ thế chấp
- IPMT (Interest Payment): Hàm này tính tiền lãi cho một kỳ cụ thể. Trong trường hợp “Reduce Term”, ta dùng IPMT để đảm bảo lãi tính chính xác trên dư nợ thực tế (đã bị giảm bởi các khoản trả thêm).
- E8-F8: Trong trường hợp “Reduce Payment”, ta quay lại phép trừ đơn giản (Tổng trả – Gốc = Lãi) vì số liệu đã được cân đối ở các bước trước.
Cột Trả thêm (Extra Payment)
Cột này (ví dụ Cột H) được để trống để bạn nhập liệu thủ công. Bất cứ khi nào bạn có tiền nhàn rỗi, hãy nhập vào dòng tương ứng với tháng đó.
Tính Dư nợ cuối kỳ với khoản trả thêm
Công thức dư nợ cuối cùng cần trừ đi cả phần gốc định kỳ VÀ phần trả thêm.
=$B$1 + SUM($F$8:F8) - SUM($H$8:H8)
Thanh toán dư nợ trong tính toán trả nợ thế chấp
Hàm này lấy: Nợ Gốc ban đầu + Tổng Gốc định kỳ đã trả (là số âm) – Tổng Tiền trả thêm (số dương, nên phải trừ). Kết quả là số nợ thực tế còn lại.
3. Tổng hợp các chỉ số hiệu quả tài chính
Sau khi hoàn thiện bảng tính, điều quan trọng nhất là bạn nhìn thấy được lợi ích của việc trả trước. Chúng ta sẽ tạo một bảng tổng kết nhỏ ở đầu trang.
Tổng lãi suất thực tế phải trả
=SUMIF(G8:G1001, "<0")Hàm này cộng tất cả các khoản lãi trong cột G. Kết quả cho thấy tổng chi phí lãi vay của bạn.
Số tiền lãi tiết kiệm được
Đây là động lực lớn nhất để trả trước hạn.
=ABS(SUM(IPMT(B4/B3, SEQUENCE(B2*B3), B2*B3, B1)) - B6)Công thức này so sánh Tổng lãi của kế hoạch gốc (không trả trước) với Tổng lãi thực tế (có trả trước) ở ô B6. Chênh lệch chính là số tiền bạn giữ lại được trong túi mình.
Thời gian rút ngắn được (Time Saved)
Nếu chọn phương án “Giảm thời gian”, bạn sẽ muốn biết mình thoát nợ sớm bao nhiêu tháng.
=COUNTIF(G8:G1001, ">=0")Hàm đếm số ô trong cột lãi suất không phải trả tiền (do nợ đã hết). Mỗi ô tương ứng với một tháng được tự do tài chính sớm hơn.
Tải xuống Mẫu Lịch Trả Nợ Vay Nâng Cao (Có Trả Trước)
Câu hỏi thường gặp (FAQ)
Tại sao kết quả hàm PMT và PPMT lại là số âm?
Trong Excel, các hàm tài chính tuân theo quy tắc dòng tiền (cash flow). Tiền chảy ra khỏi túi bạn (trả nợ) được ghi nhận là số âm, tiền chảy vào túi (khoản vay nhận được) là số dương.
Tôi có thể dùng bảng này cho vay mua xe không?
Hoàn toàn được. Nguyên tắc tính lãi của vay mua xe, mua nhà hay vay tiêu dùng trả góp đều dựa trên dư nợ giảm dần (amortization), nên bảng tính này áp dụng chính xác cho mọi loại vay trả góp.
Làm sao để in bảng tính này gọn gàng trên một trang giấy A4?
Do bảng tính rất dài (240 dòng cho 20 năm), bạn không nên in toàn bộ. Hãy sử dụng chức năng “Print Area” trong Excel để chỉ in phần tổng hợp và 1-2 năm đầu tiên, hoặc sử dụng bộ lọc (Filter) để in theo từng năm cụ thể.
Nguồn tham khảo
- Microsoft Support. (n.d.). Hàm PMT. Truy xuất từ https://support.microsoft.com/vi-vn/office/hàm-pmt-e2213008-013b-4835-a7db-d856d833e6f9
- Corporate Finance Institute. (2024). Loan Amortization Schedule. Truy xuất từ https://corporatefinanceinstitute.com/resources/commercial-lending/loan-amortization-schedule/
- Investopedia. (2023). Amortization: Definition, Formula, and How It Works. Truy xuất từ https://www.investopedia.com/terms/a/amortization.asp
