Lập bảng kế hoạch trả nợ gốc và lãi vay trên Excel
Lập bảng kế hoạch trả nợ gốc và lãi vay trên Excel là kỹ năng nghiệp vụ mà cán bộ tín dụng, giao dịch viên và nhân viên quan hệ khách hàng thường xuyên phải thực hiện khi thẩm định phương án vay vốn của khách hàng cá nhân và doanh nghiệp. Bảng kế hoạch này là một bảng tính chi tiết, trong đó liệt kê theo từng kỳ hạn (tháng hoặc quý) toàn bộ lịch trả nợ gốc, lãi vay, tổng dư nợ còn lại và dòng tiền phục vụ nghĩa vụ trả nợ. Việc sử dụng Excel để dựng bảng giúp tự động hóa tính toán nhờ các hàm tài chính, giảm sai sót thủ công và tạo cơ sở để đánh giá khả năng trả nợ của khách hàng — một trong những nội dung quan trọng nhất trong quy trình thẩm định tín dụng tại các ngân hàng thương mại Việt Nam hiện nay.
Cấu trúc chuẩn của một bảng kế hoạch trả nợ
Một bảng kế hoạch trả nợ gốc và lãi vay trên Excel thường bao gồm các cột thông tin cốt lõi sau: Kỳ hạn trả nợ (số thứ tự kỳ), Ngày trả nợ dự kiến, Dư nợ đầu kỳ, Gốc phải trả trong kỳ, Lãi phải trả trong kỳ, Tổng nghĩa vụ phải trả (gốc + lãi), và Dư nợ cuối kỳ. Hai cột quan trọng nhất là Gốc phải trả trong kỳ và Lãi phải trả trong kỳ, bởi tỷ trọng giữa hai thành phần này thay đổi qua từng kỳ theo phương thức trả nợ mà ngân hàng và khách hàng đã thỏa thuận.
Phương thức trả nợ gốc phổ biến gồm ba dạng: trả góp đều (gốc chia đều cho các kỳ, lãi giảm dần theo dư nợ thực tế), trả gốc cuối kỳ (toàn bộ gốc đến hạn khi kết thúc khoản vay, lãi trả định kỳ), và trả theo dòng tiền thực tế của khách hàng (áp dụng cho doanh nghiệp có chu kỳ kinh doanh không đồng đều). Mỗi phương thức cho ra một cấu trúc bảng tính khác nhau, đòi hỏi cán bộ tín dụng phải nắm rõ đặc thù từng loại để tư vấn cho khách hàng lựa chọn phương án phù hợp với khả năng tài chính.
Các hàm Excel thường sử dụng
Để lập bảng kế hoạch trả nợ hiệu quả, ba nhóm hàm Excel được sử dụng phổ biến nhất gồm:
- Nhóm hàm tài chính: PMT (tính số tiền trả định kỳ cố định), IPMT (tính phần lãi trong kỳ), PPMT (tính phần gốc trong kỳ), RATE (tính lãi suất), NPER (tính số kỳ). Bộ bốn hàm này cho phép dựng bảng trả nợ theo phương thức góp đều chỉ với vài thao tác.
- Nhóm hàm logic và điều kiện: IF, IFS, AND, OR — dùng để thiết lập ràng buộc như kỳ ân hạn (grace period) chỉ trả lãi chưa trả gốc, hoặc khoản vay có lãi suất thay đổi theo từng giai đoạn.
- Nhóm hàm ngày tháng và tham chiếu: EOMONTH (tính ngày cuối tháng), DATE, VLOOKUP/XLOOKUP — dùng để tự động cập nhật lịch trả nợ khi khách hàng thay đổi ngày giải ngân hoặc điều chỉnh kỳ hạn.
Ví dụ minh họa: Với khoản vay 500 triệu đồng, lãi suất 12%/năm, thời hạn 60 tháng, trả góp đều hàng tháng, công thức PMT(1%, 60, -500000000) sẽ cho ra số tiền khoảng 11.122 triệu đồng mỗi tháng. Khi đó, IPMT cho biết phần lãi kỳ đầu (khoảng 5 triệu) và PPMT cho biết phần gốc (khoảng 6,1 triệu), còn lại các kỳ sau lãi giảm dần, gốc tăng dần nhưng tổng không đổi.
Ứng dụng trong nghiệp vụ tín dụng ngân hàng
Trong thực tế thẩm định tại các ngân hàng thương mại, bảng kế hoạch trả nợ trên Excel là một trong những hồ sơ bắt buộc trong hồ sơ phương án vay vốn, đặc biệt với các khoản vay trung và dài hạn phục vụ sản xuất kinh doanh, mua nhà, mua xe. Cán bộ tín dụng dựa vào bảng này để:
- Đánh giá tỷ lệ nghĩa vụ trả nợ trên thu nhập (Debt Service Ratio — DSR) của khách hàng cá nhân, đảm bảo không vượt ngưỡng cho phép theo chính sách nội bộ của từng ngân hàng.
- Phân tích dòng tiền doanh nghiệp, đối chiếu dòng tiền từ hoạt động kinh doanh với nghĩa vụ trả nợ định kỳ để xác định khả năng hoàn trả bền vững.
- Xây dựng các kịch bản stress test đơn giản (lãi suất tăng, doanh thu giảm, thời gian ân hạn kéo dài) nhằm đánh giá rủi ro tín dụng tiềm ẩn.
- Làm cơ sở để khách hàng ký cam kết trả nợ theo lịch đã thỏa thuận trong hợp đồng tín dụng.
Ngoài ra, khi khoản vay được phê duyệt và giải ngân, bảng kế hoạch này còn được chuyển sang bộ phận quản lý khoản vay và xử lý nợ để theo dõi thực tế thanh toán, đối chiếu với lịch dự kiến, làm cơ sở nhắc nợ và phân loại nợ theo quy định hiện hành.
Một số lưu ý khi lập bảng và câu hỏi thường gặp trong phỏng vấn
Khi lập bảng kế hoạch trả nợ, cán bộ tín dụng cần lưu ý sử dụng đúng lãi suất danh nghĩa hay lãi suất thực, phân biệt lãi suất cố định với lãi suất thả nổi theo lãi suất huy động kỳ hạn 12 tháng hoặc 24 tháng cộng biên độ, đồng thời xác nhận ngày giải ngân thực tế để tính kỳ trả nợ đầu tiên chính xác. Một số ứng viên tham gia thi tuyển vào vị trí tín dụng thường gặp câu hỏi: "Hãy lập bảng trả nợ cho khoản vay X tỷ đồng trong N tháng với phương thức góp đều", hoặc "So sánh tổng lãi phải trả giữa phương thức trả góp đều và trả gốc cuối kỳ" — thành thạo Excel giúp ứng viên xử lý các tình huống này nhanh chóng và tạo ấn tượng tốt với ban phỏng vấn.
Nội dung mang tính tham khảo, không thay thế tư vấn chuyên môn về nghiệp vụ tín dụng và tài chính ngân hàng.