Khi gói vay mua căn hộ hết thời gian ưu đãi, tiền lãi có thể thay đổi ngay cả khi số dư nợ vẫn giảm đều. Một bảng trả nợ theo từng kỳ giúp người vay nhìn thấy tháng nào lãi suất thay đổi, số tiền gốc còn lại và khoản cần chuẩn bị. Hướng dẫn dưới đây sử dụng Excel hoặc Google Sheets và ví dụ giả định để bạn tự kiểm tra phương án vay, không thay thế lịch trả nợ của ngân hàng.
Vì sao cần lập lại bảng trả nợ khi hết ưu đãi lãi suất?
Khoản trả hàng tháng có thể thay đổi như thế nào?
Một số khoản vay có giai đoạn lãi suất ưu đãi và cơ chế điều chỉnh sau ưu đãi. Nếu hợp đồng áp dụng lãi suất tham chiếu cộng biên độ, mức lãi suất mới phụ thuộc chỉ số tham chiếu, biên độ và thời điểm điều chỉnh được ghi trong hợp đồng. Không thể mặc định mọi khoản vay đều tăng lãi suất sau ưu đãi. Để hiểu tác động đối với phương án đầu tư, có thể tham khảo bài tác động của lãi vay tăng.
Vì sao cần kiểm tra dư nợ tại thời điểm kết thúc ưu đãi?
Lãi kỳ sau được tính trên cơ sở dư nợ theo phương thức hợp đồng. Vì vậy, trước khi thay lãi suất, cần xác định đúng dư nợ cuối kỳ ngay trước thời điểm điều chỉnh. Nếu lấy nhầm số tiền vay ban đầu để tính toàn bộ các kỳ sau, bảng có thể phóng đại tiền lãi.
Chuẩn bị những thông tin nào trước khi lập bảng Excel?
Số tiền vay, kỳ hạn và lịch trả nợ
Ghi số tiền giải ngân thực tế, ngày giải ngân, tổng số kỳ trả, ngày đến hạn từng kỳ và phương thức trả gốc. Nếu giải ngân nhiều đợt, cần bảng riêng hoặc công thức bổ sung theo từng lần giải ngân. Trước khi quyết định vay, nên xem thêm cách kiểm soát rủi ro khi sử dụng vốn vay.
Lãi suất ưu đãi, lãi suất tham chiếu và biên độ
Đọc hợp đồng để biết ưu đãi áp dụng trong bao lâu, mức lãi suất sau ưu đãi xác định bằng cách nào, chu kỳ điều chỉnh là bao nhiêu tháng và có mức sàn hoặc trần hay không. Chỉ nhập công thức “tham chiếu + biên độ” nếu hợp đồng thực sự quy định như vậy.
Ngày điều chỉnh lãi suất và các khoản phí cần lưu ý
Đối chiếu ngày kết thúc ưu đãi với ngày đến hạn trả nợ. Hỏi ngân hàng về cách tính lãi theo ngày, phí trả nợ trước hạn và phí khác nếu có. Trang vay mua nhà của Vietcombank có mục thông tin về phương thức trả nợ và biểu phí trả nợ trước hạn; mức phí áp dụng cần xác nhận theo sản phẩm và hợp đồng cụ thể.
Cách lập bảng trả nợ vay mua căn hộ từng tháng
Bước 1: Tạo bảng và các cột dữ liệu
Tạo vùng thông số đầu vào: B2 là số tiền vay, B3 là số tháng vay, B4 là lãi suất ưu đãi theo năm, B5 là số tháng ưu đãi và B6 là lãi suất giả định sau ưu đãi. Tạo bảng từ dòng 10 với các cột A đến H: Kỳ, Dư nợ đầu kỳ, Lãi suất năm, Tiền lãi, Tiền gốc, Tổng trả, Dư nợ cuối kỳ, Ghi chú. Có thể thêm cột ngày đến hạn và số ngày tính lãi khi cần đối chiếu lịch thực tế.
Bước 2: Tính dư nợ đầu kỳ và tiền lãi
Tại kỳ đầu, dư nợ đầu kỳ bằng số tiền vay. Ở các kỳ tiếp theo, dư nợ đầu kỳ bằng dư nợ cuối kỳ trước. Công thức minh họa, giả định mỗi kỳ bằng một tháng và lãi năm chia 12:
B11=$B$2
C11=IF(A11<=$B$5,$B$4,$B$6)
D11=B11*C11/12
B12=G11Công thức D11 chỉ là mô hình tháng quy đổi. Nếu hợp đồng tính lãi theo số ngày thực tế, phải thay bằng phương pháp và mẫu số ngày theo hợp đồng, chẳng hạn tính theo số ngày của kỳ và cơ sở ngày do ngân hàng quy định. Không mặc định mẫu số luôn là 365.
Bước 3: Tính tiền gốc, tổng trả và dư nợ cuối kỳ
Với phương thức gốc đều, tiền gốc mô phỏng mỗi kỳ bằng số tiền vay chia tổng số kỳ, trừ kỳ cuối điều chỉnh sai số. Công thức tại dòng 11:
E11=MIN(B11,$B$2/$B$3)
F11=D11+E11
G11=MAX(0,B11-E11)Bước 4: Sao chép công thức và kiểm tra kết quả
Điền kỳ từ 1 đến 240, sao chép công thức xuống dưới và kiểm tra dư nợ cuối kỳ không âm, tổng gốc xấp xỉ số tiền vay, tiền lãi không âm trong giả định lãi suất dương. Nếu làm tròn đến đồng từng kỳ, điều chỉnh kỳ cuối để dư nợ về 0. Excel có thể dùng dấu phẩy hoặc dấu chấm phẩy làm dấu phân cách tham số tùy thiết lập.
Cách tính lại bảng trả nợ sau khi hết ưu đãi
Xác định kỳ đầu tiên áp dụng lãi suất mới
Trong ví dụ, ưu đãi kéo dài trọn 12 kỳ và lãi suất mới bắt đầu từ kỳ 13. Đây là giả định để minh họa; lịch thực tế phải theo ngày điều chỉnh và quy ước tính lãi trong hợp đồng. Nếu lãi suất thay đổi giữa hai ngày trả nợ, một kỳ có thể cần chia thành hai đoạn tính lãi.
Phương pháp trả gốc đều
Trong mô hình gốc đều không có trả trước hạn, tiền gốc mỗi kỳ không đổi. Khi lãi suất thay đổi, chỉ cần cập nhật lãi suất của kỳ tương ứng để tính lại tiền lãi và tổng khoản trả. Tiền gốc cố định không đồng nghĩa tổng khoản trả cố định.
Phương pháp trả góp đều và hàm PMT
Trong mô hình khoản trả đều, dùng hàm PMT cho từng giai đoạn lãi suất cố định. Theo tài liệu PMT của Microsoft, hàm này giả định các khoản trả đều và lãi suất không đổi trong giai đoạn tính. Ví dụ:
=PMT(8%/12,240,-2000000000)
=PMT(11%/12,228,-DuNoCuoiKy12)Dòng thứ hai sử dụng dư nợ thực tế cuối kỳ 12, không sử dụng lại số tiền vay ban đầu. Trong bảng Excel, thay DuNoCuoiKy12 bằng tham chiếu ô dư nợ cuối kỳ 12. Để tách gốc và lãi có thể dùng IPMT, PPMT trong một giai đoạn lãi suất cố định, hoặc tính trực tiếp tiền lãi = dư nợ đầu kỳ × lãi suất kỳ và tiền gốc = tổng trả − tiền lãi. Xem IPMT và PPMT của Microsoft.
Khi nào phải cập nhật công thức lần tiếp theo?
Mỗi khi hợp đồng quy định điều chỉnh lãi suất, cần cập nhật mức lãi áp dụng. Nếu hợp đồng yêu cầu tính lại khoản trả đều, dùng dư nợ đầu kỳ điều chỉnh và số kỳ còn lại để tính PMT mới. Không tự suy ra rằng mọi ngân hàng đều tính lại khoản trả theo đúng cách này.
Ví dụ lập bảng trả nợ khoản vay 2 tỷ đồng trong 20 năm
Giả định vay 2 tỷ đồng trong 240 tháng, lãi suất 8%/năm trong 12 kỳ đầu và 11%/năm từ kỳ 13 đến hết hạn; không trả trước hạn, không tính phí, tính lãi mỗi tháng bằng lãi suất năm chia 12. Đơn vị các khoản tiền trong bảng là triệu đồng, làm tròn hai chữ số. Bảng không phải lịch thu nợ thực tế của ngân hàng.
Bảng minh họa các kỳ quan trọng
| Kỳ | Dư nợ đầu kỳ gốc đều | Lãi gốc đều | Gốc gốc đều | Tổng trả gốc đều | Tổng trả góp đều | Dư nợ cuối kỳ góp đều |
|---|---|---|---|---|---|---|
| 1 | 2.000,00 | 13,33 | 8,33 | 21,67 | 16,73 | 1.996,60 |
| 11 | 1.916,67 | 12,78 | 8,33 | 21,11 | 16,73 | 1.961,38 |
| 12 | 1.908,33 | 12,72 | 8,33 | 21,06 | 16,73 | 1.957,73 |
| 13 | 1.900,00 | 17,42 | 8,33 | 25,75 | 20,51 | 1.955,17 |
| 14 | 1.891,67 | 17,34 | 8,33 | 25,67 | 20,51 | 1.952,58 |
| 240 | 8,33 | 0,08 | 8,33 | 8,41 | 20,51 | 0,00 |
So sánh gốc đều và trả góp đều
Ở phương thức gốc đều, khoản gốc theo mô hình là khoảng 8,33 triệu đồng/kỳ; tổng trả giảm dần khi dư nợ giảm nếu lãi suất không đổi. Ở phương thức trả góp đều, tổng khoản trả ổn định trong mỗi giai đoạn lãi suất cố định, nhưng phần gốc và lãi thay đổi qua từng kỳ. Hai phương thức không thể so sánh chỉ bằng khoản trả của tháng đầu.
Kiểm tra dư nợ và tổng tiền phải trả
Trong cả hai mô hình, tổng tiền gốc hoàn trả bằng 2 tỷ đồng trước sai số làm tròn. Tổng lãi là kết quả của toàn bộ 240 kỳ, phụ thuộc phương thức và các mức lãi đã giả định. Khi dùng dữ liệu thực tế, cần đối chiếu lại lịch trả nợ ngân hàng và các khoản phí.
Nếu lãi suất sau ưu đãi tăng hoặc giảm thì sao?
So sánh ba kịch bản 9%, 11% và 13%
| Lãi suất sau ưu đãi | Kỳ 13 gốc đều | Kỳ 13 góp đều | Tổng lãi gốc đều | Tổng lãi góp đều |
|---|---|---|---|---|
| 9% | 22,58 | 17,95 | 1.787,96 | 2.293,42 |
| 11% | 25,75 | 20,51 | 2.150,54 | 2.876,22 |
| 13% | 28,92 | 23,20 | 2.513,13 | 3.489,68 |
Ba kịch bản chỉ thay lãi suất sau ưu đãi; các thông số khác được giữ nguyên. Bảng dùng một mức lãi cố định từ kỳ 13 đến kỳ 240 để dễ so sánh, không dự báo diễn biến lãi suất thực tế trong 19 năm còn lại.
Cách ước tính phần tiền cần dự phòng mỗi tháng
So sánh khoản phải trả trong kịch bản lãi suất cao hơn với kịch bản cơ sở, rồi cộng thêm chi phí sinh hoạt, chi phí sở hữu căn hộ và quỹ dự phòng phù hợp khả năng tài chính. Người mua có thể cân nhắc lựa chọn giữa vốn tự có và vốn vay. Với căn hộ đầu tư cho thuê, nên tính riêng dòng tiền ròng khi cho thuê căn hộ thay vì coi tiền thuê là nguồn trả nợ chắc chắn.
Những lỗi thường gặp khi tự lập bảng trả nợ
Nhầm lãi suất năm và lãi suất tháng
Lãi suất 11%/năm không có nghĩa là 11%/tháng. Với mô hình tháng, lãi suất kỳ được giả định là 11%/12. Nếu ngân hàng tính theo ngày, cần chuyển sang cách tính theo hợp đồng.
Dùng PMT cố định cho toàn bộ thời gian vay
Một PMT tính từ mức lãi ưu đãi không phản ánh việc thay đổi lãi suất sau ưu đãi. Khi điều chỉnh lãi suất, cần tính lại theo dư nợ và kỳ hạn còn lại nếu phương thức hợp đồng yêu cầu.
Bỏ qua ngày tính lãi và phí trả trước hạn
Ngày giải ngân, ngày trả nợ, số ngày trong kỳ và phí trả trước hạn có thể làm kết quả thực tế khác mô hình. Không điền mức phí giả định vào kết quả rồi coi đó là phí ngân hàng đang áp dụng.
Không đối chiếu với lịch trả nợ của ngân hàng
Trước khi dùng bảng để quyết định vay hoặc tất toán, hãy xin lịch trả nợ và điều kiện điều chỉnh lãi suất từ ngân hàng. Nếu chênh lệch, kiểm tra phương thức trả nợ, số ngày tính lãi, thời điểm điều chỉnh, phí và các khoản giải ngân bổ sung.
Câu hỏi thường gặp
Hết ưu đãi lãi suất có phải trả thêm tiền gốc không?
Không thể kết luận chỉ từ việc hết ưu đãi. Tiền gốc theo lịch trả nợ phụ thuộc phương thức đã thỏa thuận. Lãi suất thay đổi có thể làm thay đổi phần lãi hoặc khoản trả đều theo cơ chế hợp đồng.
Excel có tự cập nhật lãi suất thả nổi không?
Excel chỉ cập nhật khi dữ liệu hoặc công thức được thiết lập để cập nhật. Nếu không có nguồn dữ liệu lãi suất đáng tin cậy, cần nhập mức lãi mới sau khi đối chiếu thông báo của ngân hàng.
Có thể sử dụng Google Sheets thay Excel không?
Có thể sử dụng các phép tính và hàm tài chính tương đương trong Google Sheets, nhưng cần kiểm tra dấu phân cách, định dạng phần trăm và kết quả từng kỳ.
Nên dự phòng bao nhiêu khi lãi suất tăng?
Không có một tỷ lệ phù hợp cho mọi gia đình. Hãy chạy kịch bản lãi suất cao hơn, so với thu nhập còn lại sau chi tiêu thiết yếu và cân nhắc quỹ dự phòng. Nếu đầu tư cho thuê, xem thêm vốn cần chuẩn bị khi đầu tư căn hộ.
Kết luận và tư vấn lựa chọn căn hộ
Một bảng trả nợ hữu ích cần bắt đầu từ điều khoản vay thực tế, cập nhật đúng kỳ điều chỉnh lãi suất và kiểm tra dư nợ sau mỗi lần trả. Mô hình Excel giúp nhìn trước các kịch bản, nhưng quyết định vay nên dựa trên lịch trả nợ và điều kiện được ngân hàng xác nhận.
Cần tư vấn lựa chọn căn hộ và cân đối phương án tài chính? Để lại thông tin để được hỗ trợ tìm hiểu sản phẩm phù hợp với nhu cầu và ngân sách. Gửi yêu cầu tư vấn.
