Trong 6 tháng đầu làm kế toán tại công ty nhỏ, tôi tính nợ gốc khoản vay bằng cách tách từng kỳ thanh toán ra một file riêng. Mỗi tháng phải mở lại file cũ, copy công thức, điều chỉnh số kỳ, rồi cộng dồn bằng tay. Khi phát hiện sai số trong báo cáo quý hai, tôi mới biết mình đã nhập nhầm giá trị trong 14 file khác nhau..
Tại sao tôi không phát hiện sớm hơn
Cách tính thủ công của tôi trông hợp lý. Với khoản vay 500 triệu đồng, lãi suất 9% năm, kỳ hạn 5 năm, tôi tạo một bảng tính riêng cho mỗi năm. Mỗi năm có 12 tháng, mỗi tháng tôi tính tiền gốc phải trả bằng công thức phức tạp kết hợp nhiều hàm khác nhau.
Vấn đề là công thức dài và dễ sai. Khi cần tính tổng nợ gốc từ tháng 1 đến tháng 12, tôi phải cộng 12 ô với nhau. Nếu có một ô sai, toàn bộ báo cáo sẽ sai. Trong trường hợp của tôi, ô tháng 8 bị nhập nhầm số âm thành số dương, khiến tổng nợ gốc cao hơn 18 triệu đồng so với thực tế.
Hàm CUMPRINC giải quyết vấn đề này thế nào
Hàm CUMPRINC tính tổng nợ gốc tích lũy phải trả cho khoản vay từ kỳ đầu tiên đến kỳ cuối cùng trong một công thức duy nhất. Thay vì phải tính từng tháng rồi cộng lại, bạn chỉ cần nhập các tham số một lần và hàm tự động tính toán.
Cú pháp của hàm là:
=CUMPRINC(rate, nper, pv, start_period, end_period, type)Trong đó:
- rate: Lãi suất cho mỗi kỳ hạn (nếu tính theo tháng phải chia lãi suất năm cho 12)
- nper: Tổng số kỳ thanh toán (nếu vay 5 năm trả hàng tháng thì là 5 nhân 12)
- pv: Giá trị hiện tại của khoản vay (số tiền vay ban đầu)
- start_period: Kỳ đầu tiên muốn tính, bắt đầu từ 1
- end_period: Kỳ cuối cùng muốn tính
- type: Kiểu thanh toán, 0 là cuối kỳ, 1 là đầu kỳ
Điều quan trọng nhất là phải nhất quán về đơn vị thời gian. Nếu lãi suất tính theo năm thì phải chia cho 12 để ra lãi suất tháng. Nếu số năm vay là 5 thì phải nhân 12 để ra 60 tháng.
Áp dụng thực tế với ví dụ cụ thể
Quay lại khoản vay của tôi: 500 triệu đồng, lãi suất 9% năm, kỳ hạn 5 năm, trả hàng tháng vào cuối mỗi tháng.
Tính tổng nợ gốc năm đầu tiên (tháng 1 đến tháng 12):
=CUMPRINC(9%/12, 5*12, 500000000, 1, 12, 0)Kết quả: -78.234.517 đồng (số âm vì đây là khoản tiền phải trả)
Công thức này có nghĩa là:
- Lãi suất: 9% chia 12 tháng = 0,75% mỗi tháng
- Tổng số kỳ: 5 năm nhân 12 tháng = 60 tháng
- Số tiền vay: 500 triệu đồng
- Tính từ kỳ 1 đến kỳ 12
- Thanh toán cuối kỳ
Tính tổng nợ gốc năm thứ hai (tháng 13 đến tháng 24):
=CUMPRINC(9%/12, 5*12, 500000000, 13, 24, 0)Kết quả: -85.448.622 đồng
Trong vòng 30 giây, tôi có được con số chính xác cho cả hai năm mà không cần tính toán thủ công từng tháng.
Lỗi thường gặp và cách tránh
Hàm CUMPRINC sẽ trả về lỗi #NUM! trong các trường hợp sau:
Quên chuyển đổi đơn vị thời gian: Đây là lỗi tôi mắc phải lúc đầu. Nếu bạn nhập trực tiếp 9% cho tham số rate nhưng nper là 60 tháng, công thức sẽ tính sai vì lãi suất 9% là theo năm chứ không phải theo tháng. Phải chia 9% cho 12 để có lãi suất tháng.
Tham số start_period hoặc end_period không hợp lệ: Nếu start_period nhỏ hơn 1, end_period nhỏ hơn 1, hoặc start_period lớn hơn end_period, hàm sẽ báo lỗi. Ví dụ, nếu bạn nhập start_period là 13 và end_period là 12, Excel không thể tính được.
Tham số type không đúng: Tham số này chỉ nhận giá trị 0 hoặc 1. Nếu bạn nhập bất kỳ số nào khác, hàm sẽ trả về lỗi #NUM!. Hầu hết các khoản vay trả cuối kỳ nên dùng 0.
Để tránh lỗi:
- Luôn kiểm tra đơn vị thời gian của rate và nper có khớp nhau không
- Đảm bảo start_period bắt đầu từ 1 và nhỏ hơn hoặc bằng end_period
- Chỉ dùng 0 hoặc 1 cho tham số type
- Nhập đúng số tiền vay dương cho pv
Kết quả sau khi chuyển sang hàm CUMPRINC
Thời gian làm báo cáo tài chính hàng tháng của tôi giảm từ 4 giờ xuống 45 phút. Thay vì 14 file Excel riêng biệt, tôi chỉ cần một bảng tính duy nhất với các công thức CUMPRINC cho từng năm. Khi cần xem tổng nợ gốc của bất kỳ khoảng thời gian nào, tôi chỉ cần điều chỉnh hai tham số start_period và end_period.
Quan trọng hơn, độ chính xác tăng lên rõ rệt. Từ khi dùng hàm CUMPRINC, tôi không còn gặp sai số trong báo cáo tài chính nữa. Hàm này có sẵn trong tất cả các phiên bản Excel từ 2007 trở đi, bao gồm cả Excel 365, Excel 2019, Excel 2016, và Excel 2013. Nếu bạn đang tính nợ gốc thủ công, thử chuyển sang CUMPRINC để tiết kiệm thời gian và tránh sai sót.
