Cách tính toán khoản vay đơn giản trong Excel bằng VBA

📑 Mục lục (bấm để ẩn/hiện)

1. Giới thiệu

Trong cuộc sống, việc vay vốn mua nhà, xe hoặc đầu tư kinh doanh rất phổ biến. Nếu chỉ dùng máy tính tay, bạn khó so sánh nhiều kịch bản vay khác nhau. Với VBA trong Excel, bạn hoàn toàn có thể xây dựng một công cụ tính khoản vay tự động, giúp điều chỉnh lãi suất, kỳ hạn, và hình thức trả nợ chỉ bằng vài cú click.

Bài viết này hướng dẫn các bạn cách tạo một chương trình tính toán khoản vay đơn giản bằng VBA trong Excel. Trang tính excel chứa các điều khiển ActiveX sau: hai thanh cuộn (ScrollBar) và hai nút tùy chọn (OptionButton).

2. Thực hiện các bước sau để tạo bảng tính toán khoản vay:

Thêm hai điều khiển thanh cuộn (ScrollBar). Nhấn vào tab Developer (Nhà phát triển), Bấm vào Insert (Chèn) và sau đó bấm vào Thanh cuộn (ScrollBar) trong phần Điều khiển ActiveX.

Thay đổi các thuộc tính sau của điều khiển thanh cuộn (đảm bảo đã chọn Chế độ thiết kế).

Nhấp chuột phải vào điều khiển thanh cuộn đầu tiên (ScrollBar), sau đó nhấp vào Thuộc tính (Properties). Đặt Min thành 0, Max thành 20, SmallChange thành 0 và LargeChange thành 2. Đổi thuộc tính Name của điều khiển thanh cuộn thành ScrBar1.

Nhấp chuột phải vào điều khiển thanh cuộn thứ hai (ScrollBar), sau đó nhấp vào Thuộc tính (Properties). Đặt Min thành 5, Max thành 30, SmallChange thành 1, LargeChange thành 5 và LinkedCell thành G10. Đổi thuộc tính Name của điều khiển thanh cuộn thành ScrBar2.

Ta có thể giải thích như sau: Khi bạn bấm vào mũi tên, giá trị của thanh cuộn sẽ tăng hoặc giảm theo SmallChange. Khi bạn nhấp vào giữa thanh trượt và mũi tên, giá trị của thanh cuộn sẽ tăng hoặc giảm theo LargeChange.

Thêm hai nút tùy chọn (OptionButton). Nhấn vào tab Developer (Nhà phát triển), Bấm vào Insert (Chèn) và sau đó bấm vào Nút Tùy chọn (OptionButton) trong phần Điều khiển ActiveX.

Thay đổi các thuộc tính Name của điều khiển nút tùy chọn (OptionButton). Nhấp chuột phải vào điều khiển nút tùy chọn (OptionButton), sau đó nhấp vào Thuộc tính (Properties). Đổi thuộc tính Name của điều khiển nút tùy chọn (OptionButton) lần lượt thành Opt1 và Opt2.

Tiếp theo bạn hãy định dạng bảng tính. Bạn cần thay đổi kiểu phông chữ, chèn hàng và cột, thêm đường viền, thay đổi màu nền, v.v.

Kết quả ta có Bảng tính khoản vay đơn giản bằng VBA trong Excel như hình ảnh dưới đây:

Tiếp theo bạn cần tạo một sự kiện thay đổi trang tính. Mã được thêm vào Sự kiện thay đổi trang tính sẽ được thực thi bởi Excel VBA khi bạn thay đổi một ô trên trang tính.

Mở Trình soạn thảo Visual Basic. Nhấp đúp vào Trang tính 1 (Sheet1) trong Project Explorer. Chọn Worksheet từ danh sách thả xuống bên trái và chọn Change từ danh sách thả xuống bên phải.

Sự kiện Worksheet Change xảy ra khi có sự thay đổi trên Sheet1. Ở đây, Chúng ta chỉ muốn Excel VBA chạy thủ tục “Tinh_Khoan_Vay” nếu có gì đó thay đổi trong ô F6. Để đạt được điều này, hãy thêm đoạn mã sau vào Sự kiện thay đổi trang tính (Worksheet_Change):

Private Sub Worksheet_Change(Byval Target as Range)
If Target.Address = "$F$6" Then 
Call Tinh_Khoan_Vay
End if
End Sub

Tiếp theo, Bạn hãy lấy đúng tỷ lệ phần trăm trong ô G8 (thay đổi định dạng của ô G8 thành phần trăm). Nhấp chuột phải vào điều khiển thanh cuộn đầu tiên (ScrollBar), sau đó nhấp vào View code (Xem mã). Thêm đoạn mã sau:

Private Sub ScrBar1_Change()
Range("G8").Value = ScrBar1.Value / 100
Call Tinh_Khoan_Vay
End Sub

Nhấp chuột phải vào điều khiển thanh cuộn thứ hai (ScrollBar), sau đó nhấp vào View code (Xem mã). Thêm dòng mã sau:

Private Sub ScrBar2_Change()
Call Tinh_Khoan_Vay
End Sub

Nhấp chuột phải vào điều khiển nút tùy chọn đầu tiên (OptionButton), sau đó nhấp vào View code (Xem mã). Thêm dòng mã sau:

Private Sub Opt1_Click()
If Opt1.Value = True Then 
Range("E14").Value = "Thanh toan hang thang"
End if
Call Tinh_Khoan_Vay
End Sub

Nhấp chuột phải vào điều khiển nút tùy chọn thứ hai (OptionButton), sau đó nhấp vào View code (Xem mã). Thêm dòng mã sau:

Private Sub Opt2_Click()
If Opt2.Value = True Then 
Range("E14").Value = "Thanh toan hang nam"
End if
Call Tinh_Khoan_Vay
End Sub

Cuối cùng, bạn cần thêm một thủ tục để tính khoản vay, bạn có thể đặt thủ tục có tên là “Tinh_Khoan_Vay” vào một mô-đun. Trong cửa sổ soạn thảo mã Visual Basic Editor, bạn nhấp vào Insert (Chèn), rồi nhấp vào Module (Mô-đun). Sau đó thêm đoạn mã sau:

Sub Tinh_Khoan_Vay()
Dim khoan_vay As Long, lai_suat As Double, tg_vay As Integer
khoan_vay = Range("F6").Value
lai_suat = Range("G8").Value
tg_vay = Range("G10").Value
If Sheet1.Opt1.Value = True Then
    lai_suat = lai_suat / 12
    tg_vay = tg_vay * 12
End If
Range("F14").Value = -1 * WorksheetFunction.Pmt(lai_suat, tg_vay, khoan_vay)
End Sub

Giải thích mã:

Thủ tục nhận đúng tham số cho hàm trang tính Pmt. Hàm PMT trong Excel tính toán các khoản thanh toán cho khoản vay dựa trên các khoản thanh toán không đổi và lãi suất không đổi. Nếu bạn thực hiện thanh toán hàng tháng (Sheet1.Opt1.Value = True), Excel VBA sử dụng lãi suất/ 12 cho lãi suất và tg_vay *12 cho tg_vay (tổng số lần thanh toán). Kết quả là một số âm, bởi vì các khoản thanh toán được coi là một khoản ghi nợ. Nhân kết quả với -1 sẽ cho kết quả dương.

Bây giờ bạn hãy lưu lại các đoạn mã trên, rồi quay trở lại trang tính. Bạn nhập một số tiền vào ô F6 sẽ kích hoạt sự kiện Worksheet_Change(), bạn điều chỉnh lãi suất, thời gian vay bằng cách rê kéo hoặc nhấn mũi tên từ điều khiển thanh cuộn ScrollBar. Sau đó bạn lựa chọn hình thức thanh toán bằng cách nhấn vào một trong hai nút tùy chỉnh OptionButton. Bây giờ kết quả sẽ hiển thị như hình dưới đây:

3. Case Study và ví dụ

Ví dụ thực tế: Tính khoản vay mua nhà

Giả sử bạn vay 800 triệu trong vòng 15 năm với lãi suất 9%/năm. Sử dụng bảng tính VBA, bạn có thể thay đổi lãi suất theo từng ngân hàng và so sánh ngay số tiền phải trả hàng tháng. Đây là cách để bạn đưa ra quyết định vay vốn thông minh, tiết kiệm hàng trăm triệu đồng tiền lãi.

Case Study: Quản lý danh mục vay của doanh nghiệp nhỏ

Một doanh nghiệp nhỏ có nhiều khoản vay ngắn hạn và dài hạn. Bằng cách áp dụng bảng tính VBA này, họ có thể lập kế hoạch dòng tiền, kiểm soát lãi vay và đưa ra chiến lược trả nợ tối ưu. Điều này giúp doanh nghiệp giảm rủi ro tài chính và cải thiện dòng tiền kinh doanh.

4. Kết luận

Công cụ tính khoản vay bằng VBA trong Excel không chỉ hỗ trợ học tập mà còn có giá trị ứng dụng thực tế cao. Bạn có thể dùng để lập kế hoạch tài chính cá nhân, tư vấn khách hàng hoặc quản lý tài chính doanh nghiệp. Hãy bắt đầu với ví dụ trong bài và tự xây dựng bảng tính của riêng mình!

👉 Internal link: “Xem thêm: Cách tạo hộp tìm kiếm nhanh trong Excel bằng VBA”

❓ Câu hỏi thường gặp (FAQ)

1. Công cụ này có áp dụng được cho vay mua xe không?

Có, bạn chỉ cần thay đổi số tiền vay, thời gian vay và lãi suất trong bảng tính VBA.

2. Tôi có thể thêm chức năng trả nợ trước hạn không?

Hoàn toàn có thể. Bạn có thể bổ sung thêm đoạn code VBA để mô phỏng trả nợ trước hạn và tính toán lại lãi suất.

3. VBA có khó học không?

Không quá khó. Nếu bạn đã quen với Excel, chỉ cần làm theo ví dụ trong bài viết này là có thể nắm bắt nhanh chóng.