Tôi Tính Lãi Vay Trong 5 Giây Thay Vì 10 Phút Nhờ Hàm ISPMT Trong Excel

Mỗi khi cần tính bảng trả lãi cho khoản vay theo dư nợ giảm dần, tôi thường mất ít nhất 10 phút. Nhập công thức cho cột đầu, kéo xuống, kiểm tra từng ô xem có sai không. Rồi khi số liệu thay đổi, lại phải tính lại từ đầu. Giờ đây cùng một bảng tính, tôi chỉ cần 5 giây nhờ hàm ISPMT.

Cách cũ tôi dùng phức tạp không cần thiết

Trước khi biết hàm ISPMT, tôi tính lãi bằng cách thủ công. Mỗi kỳ thanh toán cần một công thức riêng: lấy số nợ đầu kỳ nhân với lãi suất. Nghĩa là phải có cột nợ đầu kỳ, cột nợ cuối kỳ, rồi mới tính được cột lãi.

Với khoản vay 100 triệu đồng trả trong 36 tháng, bảng tính của tôi cần tối thiểu 5 cột: Kỳ, Nợ đầu kỳ, Trả gốc, Trả lãi, Nợ cuối kỳ. Công thức trả lãi phụ thuộc vào nợ đầu kỳ, nghĩa là nếu sai một ô thì toàn bộ các ô phía sau đều sai theo.

Điều tệ hơn là khi khách hàng thay đổi thời hạn vay từ 36 tháng xuống 24 tháng. Tôi phải xóa 12 dòng cuối, điều chỉnh lại công thức để tổng gốc vẫn bằng 100 triệu. Mất thêm 5 phút chỉ để cập nhật.

Hàm ISPMT tính trực tiếp không cần cột phụ

Hàm ISPMT có cú pháp đơn giản: =ISPMT(rate, per, nper, pv). Trong đó rate là lãi suất mỗi kỳ, per là kỳ cần tính, nper là tổng số kỳ, và pv là số tiền vay ban đầu.

XEM THÊM: Cách Tính Lợi Tức Chứng Khoán Kỳ Lẻ Với Hàm ODDFYIELD Trong 5 Bước

Điểm mạnh của ISPMT là tính lãi trực tiếp cho từng kỳ mà không cần biết nợ đầu kỳ là bao nhiêu. Hàm tự động tính dựa trên nguyên tắc gốc giảm đều: chia số tiền vay cho tổng số kỳ để ra gốc mỗi kỳ, rồi tính lãi dựa trên số gốc còn lại.

Với khoản vay 100 triệu trong 36 tháng lãi suất 1% mỗi tháng, công thức tính lãi kỳ đầu tiên chỉ đơn giản: =ISPMT(1%, 0, 36, 100000000). Kéo công thức xuống các ô tiếp theo, chỉ cần thay đổi tham số per từ 0 lên 1, 2, 3… là xong.

Điểm quan trọng nhất nhiều người hay nhầm

Tham số per của ISPMT bắt đầu từ 0 chứ không phải từ 1. Đây là điểm tôi mất 2 giờ để tìm ra lỗi khi lần đầu sử dụng. Kỳ đầu tiên phải nhập 0, kỳ thứ hai nhập 1, kỳ thứ ba nhập 2, và cứ thế.

Nếu bạn có cột đánh số thứ tự từ 1 đến 36, công thức phải là =ISPMT(1%, A2-1, 36, 100000000) chứ không phải =ISPMT(1%, A2, 36, 100000000). Trừ đi 1 là bắt buộc để hàm hiểu đúng kỳ nào đang được tính.

Lỗi này khiến tất cả số liệu lãi lệch đi một kỳ. Kỳ 1 hiển thị lãi của kỳ 2, kỳ 2 hiển thị lãi của kỳ 3. Khi kiểm tra tổng lãi cuối bảng, số liệu sai hoàn toàn so với thực tế nhưng khó phát hiện nguyên nhân.

Ví dụ thực tế với khoản vay 50 triệu

Giả sử vay 50 triệu đồng với lãi suất 12% mỗi năm, trả hàng tháng trong 5 năm. Lãi suất tháng là 12% chia 12 bằng 1%. Tổng số kỳ là 5 năm nhân 12 tháng bằng 60 tháng.

XEM THÊM: Cách Dùng Hàm RRI Để Tính Lãi Suất Cần Thiết Cho Mục Tiêu Đầu Tư

Bảng tính chỉ cần 3 cột: Kỳ, Lãi, Gốc. Công thức ô lãi kỳ đầu tiên: =ISPMT(1%, A2-1, 60, 50000000). Trong đó A2 chứa số 1 đại diện cho kỳ đầu tiên. Kéo công thức xuống 60 dòng là có đủ bảng lãi.

Gốc mỗi kỳ đều nhau: 50 triệu chia 60 bằng 833,333 đồng. Tổng thanh toán mỗi kỳ là gốc cộng lãi. Kỳ đầu trả gốc 833,333 cộng lãi 500,000 bằng 1,333,333 đồng. Kỳ cuối trả gốc 833,333 cộng lãi 8,333 bằng 841,666 đồng.

Khi khách hàng muốn đổi thời hạn từ 5 năm xuống 3 năm, tôi chỉ việc thay nper từ 60 thành 36 trong công thức. Xóa 24 dòng cuối đi là xong. Không cần điều chỉnh bất kỳ ô nào khác. Từ 10 phút giảm xuống còn 15 giây.

ISPMT khác IPMT như thế nào

Nhiều người nhầm lẫn giữa ISPMT và IPMT vì cả hai đều tính lãi. Điểm khác biệt nằm ở cách trả nợ. IPMT dùng cho khoản vay trả đều mỗi kỳ: gốc cộng lãi luôn cố định. ISPMT dùng cho khoản vay trả gốc đều mỗi kỳ: chỉ gốc cố định, lãi giảm dần.

Với IPMT, khoản thanh toán hàng tháng luôn giống nhau, ví dụ 2 triệu đồng mỗi tháng. Phần lãi và phần gốc trong 2 triệu đó thay đổi theo thời gian nhưng tổng vẫn là 2 triệu.

Với ISPMT, phần gốc mỗi tháng cố định, ví dụ 1 triệu đồng. Lãi giảm dần theo số nợ còn lại nên tổng thanh toán giảm dần từ 1.5 triệu xuống 1.01 triệu cuối kỳ.

XEM THÊM: Cách Dùng Hàm NOMINAL Để Tính Lãi Kép Hàng Tháng, Quý, Năm

Ngân hàng thương mại thường dùng IPMT vì khách hàng thích trả đều mỗi tháng. Doanh nghiệp vay ngân hàng hoặc phát hành trái phiếu thường dùng ISPMT vì dòng tiền dễ dự báo hơn khi gốc cố định.

Kết quả sau khi chuyển sang ISPMT

Bảng tính lãi vay của tôi giảm từ 5 cột xuống 3 cột. Thời gian cập nhật khi thay đổi thông số từ 10 phút xuống 5 giây. Lỗi sai công thức giảm 90% vì không còn phụ thuộc cột nào khác.

Hàm ISPMT có sẵn từ Excel 2003 trở đi, hoạt động tốt trên Excel 365, Excel 2021, Excel 2019. Tương thích cả Windows và Mac. Google Sheets cũng hỗ trợ hàm này với cú pháp tương tự.

Nếu bạn đang làm tài chính doanh nghiệp hoặc tính toán khoản vay cá nhân, hãy thử ISPMT cho những trường hợp trả gốc đều. Nhớ tham số per bắt đầu từ 0 để tránh lỗi sai lệch dữ liệu. Với bảng tính nhỏ dưới 100 dòng, kết quả gần như tức thì.