Theo dõi khoản vay: gốc, lãi, phí và lãi tính theo dư nợ giảm dần thực tế
Đầu bài
Công ty có 4 khế ước vay, trả gốc không đều, lãi tính theo dư nợ thực tế từng ngày. Ngân hàng gửi thông báo lãi mỗi tháng, cần kiểm lại xem họ tính đúng không và dự trù tiền lãi các tháng tới.
Các bước làm
- 1
Một bảng giao dịch cho mỗi khế ước
Ngày · Loại (Giải ngân/Trả gốc) · Số tiền · Lãi suất áp dụng từ ngày này. Lãi suất để trong bảng giao dịch chứ không để một ô — vay thả nổi thì nó đổi giữa kỳ.
- 2
Dư nợ sau mỗi lần biến động
=SUM($D$2:D2) với giải ngân dương, trả gốc âm. Ô kiểm tra: dư nợ không bao giờ được âm, =MIN(cột) phải ≥ 0.
- 3
Số ngày giữ mỗi mức dư nợ
=ngày_dòng_sau - ngày_dòng_này. Dòng cuối thì lấy tới ngày chốt kỳ, dùng ô ngày cứng chứ đừng TODAY().
- 4
Lãi từng đoạn
=dư_nợ * lãi_suất_năm / 365 * số_ngày. Đây là công thức ngân hàng dùng; cơ sở 365 hay 360 phải xem hợp đồng, để trong một ô có nhãn.
- 5
Cộng lãi theo tháng để đối chiếu thông báo
=SUMIFS(lãi_đoạn, ngày, ">="&đầu_tháng, ngày, "<="&cuối_tháng). Đoạn nào vắt qua hai tháng phải cắt đôi — nếu không thì lệch đúng vào tháng có trả gốc.
- 6
So với thông báo của ngân hàng
=IF(ABS(lãi_mình - lãi_ngân_hàng)>10000, "Hỏi lại", "Khớp"). Chênh nhỏ thường do cơ sở ngày 360/365; chênh lớn là có ngày giải ngân hoặc lãi suất khác nhau.
- 7
Dự trù lãi các tháng tới
Giữ nguyên bảng, thêm dòng kế hoạch trả gốc dự kiến vào cuối. Bảng tự tính tiếp — đây là số đưa vào dự báo dòng tiền, khỏi ước lượng.
Phím dùng trong bài
Bẫy hay dính
Tính lãi bằng dư_nợ * lãi_suất_năm / 12 cho mọi tháng: tháng 28 ngày và tháng 31 ngày ra bằng nhau, và mọi lần trả gốc giữa tháng đều bị bỏ qua. Chênh cả năm nhỏ nhưng từng tháng lệch, đối chiếu thông báo là lộ ngay.
Cùng nhóm ngân hàng, thanh toán & ngoại tệ
- Đối chiếu sao kê ngân hàng với sổ tiền gửi, tìm khoản treo hai bênSố dư sao kê 4,182 tỷ, sổ 112 ghi 4,205 tỷ. Lệch 23 triệu. Trong tháng có hơn 400 giao dịch, phải tìm ra khoản nào ngân hàng đã ghi mà mình chưa, khoản nào mình ghi mà ngân hàng chưa về.
- Sao kê ngân hàng tải về: nhiều dòng đầu, số âm trong ngoặc, ngày dạng chữFile sao kê tải từ ngân hàng: 8 dòng đầu là tiêu đề và logo, cột số tiền ghi (1.500.000) cho khoản chi, ngày là chữ, có dòng tổng chen giữa. Dán vào là mọi hàm tính đều ra 0.
- Đánh giá lại số dư gốc ngoại tệ cuối kỳ và chênh lệch tỷ giáCuối năm có số dư USD ở tiền gửi, phải thu và phải trả. Chuẩn mực bắt đánh giá lại theo tỷ giá cuối kỳ, mà tỷ giá dùng cho tài sản khác tỷ giá dùng cho nợ phải trả. Làm sai là kiểm toán trả về.
- Sổ ngoại tệ: tỷ giá lúc thu, tỷ giá lúc chi và lãi lỗ tỷ giá đã thực hiệnTài khoản USD có tiền vào ra suốt năm, mỗi lần một tỷ giá. Khi chi ra phải ghi giảm theo tỷ giá GHI SỔ của số ngoại tệ đó, chênh với tỷ giá thực tế là lãi lỗ. Phần mềm tính bình quân, mình cần kiểm lại.