Vay có ân hạn gốc và lãi thả nổi: lịch trả nợ mà PMT không làm được
Đầu bài
Khế ước 10 tỷ, ân hạn gốc 12 tháng chỉ trả lãi, sau đó trả gốc đều 48 tháng, lãi thả nổi điều chỉnh 6 tháng một lần. PMT chỉ giải được khoản vay đều lãi cố định nên không dùng thẳng được.
Các bước làm
- 1
Dựng bảng theo KỲ, không tìm một công thức duy nhất
Mỗi dòng một kỳ: dư nợ đầu · lãi suất kỳ này · lãi · gốc trả · dư nợ cuối. Bài toán có điều kiện đổi giữa chừng thì bảng luôn thắng công thức gộp.
- 2
Lãi suất thả nổi lấy theo mốc điều chỉnh
Bảng phụ: từ kỳ nào áp lãi suất nào, dò bằng =VLOOKUP(số_kỳ, bảng_lãi, 2, TRUE) — dò GẦN ĐÚNG, bảng phải sắp tăng dần theo số kỳ.
- 3
Gốc trả phụ thuộc giai đoạn
=IF(số_kỳ<=kỳ_ân_hạn, 0, dư_nợ_gốc/số_kỳ_trả_gốc). Một công thức chạy suốt bảng, không tách hai vùng rồi quên đồng bộ khi đổi giả định.
- 4
Lãi tính trên dư nợ đầu kỳ
=dư_nợ_đầu * lãi_suất_năm/12. Muốn chuẩn theo ngày thì nhân số ngày thực tế chia 365 — ngân hàng tính theo ngày, chênh nhỏ nhưng đối chiếu là thấy.
- 5
Trả trước hạn một phần
Thêm cột Trả thêm; dư nợ cuối =đầu - gốc_đều - trả_thêm. Bảng tự rút ngắn: =COUNTIF(cột_dư_nợ_cuối, ">0") ra số kỳ còn lại thực tế.
- 6
Chốt bằng ba ô kiểm tra
Dư nợ cuối kỳ chót phải bằng 0 · =MIN(dư_nợ) không được âm · tổng gốc trả phải bằng số vay. Ba ô này bắt gần hết lỗi của loại bảng này.
- 7
Tổng chi phí vay để đàm phán
=SUM(cột_lãi)+phí. So với phương án lãi cố định để biết thả nổi đang lợi hay hại — và làm luôn bảng nhạy cảm khi lãi tăng 2%.
Phím dùng trong bài
Bẫy hay dính
VLOOKUP dò gần đúng để lấy lãi suất theo kỳ mà bảng mốc chưa sắp tăng dần: trả về mức lãi của giai đoạn khác, bảng vẫn chạy trơn và tổng lãi sai vài trăm triệu. Sắp bảng ngay khi tạo, và ghi chú cấm sắp lại.
Cùng nhóm tài chính, dòng tiền & kế hoạch
- Lập lịch trả nợ vay mua nhà, mua xeVay một khoản, trả đều hàng tháng. Cần biết mỗi tháng trả bao nhiêu, trong đó bao nhiêu là lãi, bao nhiêu là gốc, và tổng lãi cả đời khoản vay.
- Sổ thu chi cá nhân → số dư lũy kế theo ngàyGhi từng khoản thu/chi, muốn có số dư chạy theo từng dòng, tổng thu tổng chi theo loại, và cảnh báo khi âm quỹ.
- Phân bổ chi phí chung cho các bộ phận theo tỉ lệChi phí điện, mặt bằng, quản lý dùng chung, cần chia về từng bộ phận theo tỉ lệ doanh thu (hoặc diện tích, số người) — và tổng phân bổ phải khớp tuyệt đối.
- Dự báo dòng tiền 13 tuần từ công nợ phải thu, phải trảSếp hỏi tuần nào sẽ thiếu tiền và thiếu bao nhiêu. Trong tay chỉ có bảng công nợ phải thu, phải trả kèm ngày đáo hạn, cộng vài khoản cố định hằng tháng.