Hướng dẫn tạo bảng kê khai lãi suất của vay tiêu dùng trên Excel

Bất cứ ai cũng có thể nhận thấy các ngân hàng thường xuyên đề xuất các khoản vay tiêu dùng. Tuy nhiên, không phải ai cũng có khả năng tính toán một cách chính xác số tiền lãi cần thanh toán cho từng khoản vay. Bạn có chắc chắn rằng lời đề nghị từ ngân hàng là lựa chọn tốt nhất? Đồng hành cùng Siêu Marketing để thiết lập một bảng tính lãi suất vay tiêu dùng dựa trên phương pháp dư nợ giảm dần trên phần mềm Excel và làm sáng tỏ điều này.

Thu thập các thông tin cần thiết từ hợp đồng vay

Mỗi hợp đồng vay chứa các thông số cụ thể, rõ ràng cần được ghi chép lại để tính toán. Đầu tiên, ta cần lưu lại những thông số này làm cơ sở cho các bước tính toán sau đó. Vì có thể cần điều chỉnh, các thông số này cần được lập trong bảng tính để có thể dễ dàng thay đổi khi cần thiết.

Xem thêm: Kỹ thuật tính lãi kép trong Excel cực kỳ đơn giản và dễ hiểu

Ví dụ, xét một hợp đồng vay có các thông số như sau:

  • Số tiền vay dự kiến là 100 triệu đồng
  • Thời hạn vay 4 năm
  • Thanh toán hàng tháng cả gốc và lãi
  • Mức lãi suất áp dụng là 16%/năm
  • Phương thức tính lãi dựa theo dư nợ giảm dần

Mở Excel và bắt đầu nhập những thông tin này vào:

Cách tính lãi suất vay tiêu dùng trên excel 4
Cách tính lãi suất vay tiêu dùng trên excel 4

Điền các thông tin cần thiết từng thông số vào từng ô. Khi cần chỉnh sửa thông tin gì chúng ta chỉ cần sửa trực tiếp tại các ô đó.

Cài đặt định dạng cho dữ liệu như sau:

  • Chọn StylesHeading1 cho A1:C1
  • Đặt Row height là 24 (40 pixels) cho dòng 1
  • Đặt Row height là 15.6 (26 pixels) cho dòng từ 2 đến 6
  • Thiết lập chiều rộng cho cột A là 22 (205 pixels)
  • Thiết lập chiều rộng cho cột B là 14 (133 pixels)
  • Thiết lập chiều rộng cho cột C là 8 (79 pixels)

Các ô C3, B4, C5 cần được thiết đặt Data Validation với các tuỳ chọn:

  • Allow=List
  • Source=Tháng, Quý, Năm
Cách tính lãi suất vay tiêu dùng trên excel 5
Cách tính lãi suất vay tiêu dùng trên excel 5

Những ô B2:B6, C3, C5 cần chọn Cell Styles Input để đánh dấu đây là khu vực nhập dữ liệu

Cách tính lãi suất vay tiêu dùng trên excel 6
Cách tính lãi suất vay tiêu dùng trên excel 6

Ta vừa hoàn thành bước thiết lập các thông số cho bảng kê khai. Bất kỳ sự thay đổi nào từ thông số của phương án vay khác, chúng ta có thể nhanh chóng cập nhật bằng cách chỉnh sửa trực tiếp tại các ô từ B2:C6.

Điều chỉnh đơn vị tính để thống nhất cho các thông số

Ta cần lưu ý đến:

  • Thời gian vay (C3) và lãi suất (C5) được định nghĩa theo Năm
  • Chu kỳ thanh toán lãi được ghi tại ô B4 lại được tính theo Tháng

Nhìn vào điều này, nhiệm vụ kế tiếp chính là chuyển đổi các đơn vị để chúng ta có thể tiến hành tính toán chính xác.

Hệ số chuyển đổi:

  • 1 Năm tương đương 12 Tháng
  • 1 Quý bằng 3 Tháng
  • 1 Năm bằng 4 Quý

Dưới đây là bảng chuyển đổi:

Cách tính lãi suất vay tiêu dùng trên excel 3
Cách tính lãi suất vay tiêu dùng trên excel 3

Chúng ta có thể đặt bảng chuyển đổi này vào ô I1:L10 từ cột Đơn vị 1 đến cột Hệ số, trừ cột Cách nhập chứa hướng dẫn về cách nhập hệ số, không cần nhập lại nội dung của cột này.

Phương pháp chuyển đổi

Chuyển đổi đơn vị dựa vào B4 (được xem như cơ sở để chuyển đổi)

Cách tính lãi suất vay tiêu dùng trên excel 2
Cách tính lãi suất vay tiêu dùng trên excel 2

Các ô E3:G3E5:G5 cần được định dạng Cells StylesCalculation

Chúng ta sẽ có:

  • E3=B4 (hệ số chuyển đổi thứ nhất)
  • E5=B4 (hệ số chuyển đổi thứ nhất)
  • F3=VLOOKUP(E3&C3,$K$2:$L$10,2,0) – Hàm Vlookup tìm kiếm kết hợp giữa hai ô E3 và C3, nơi E3 là hệ số chuyển đổi thứ nhất và C3 là hệ số tiếp theo; khu vực tham chiếu là bảng chuyển đổi giữa các cột GhépHệ số
  • G3=B3*F3 đem lại tổng thời gian vay sau chuyển đổi
  • F5=VLOOKUP(E5&C5,$K$2:$L$10,2,0) hoạt động giống như F3
  • G5=B5/F5 là lãi suất sau khi được chuyển đổi

(Xem bảng hệ số chuyển đổi để hiểu rõ hơn về việc tại sao là phép chia)

Sau khi đồng nhất về một đơn vị, ta có thể tiến hành thiết kế bảng tính lãi cho khoản vay cụ thể như sau:

Bảng kế hoạch chi tiết trả nợ

Trong việc lập kế hoạch trả nợ, cần xem xét một số yếu tố sau:

  • Kỳ: đây là mỗi lần thanh toán gốc và lãi. Số kỳ này được xác định dựa vào Tổng thời gian vay (G3) và Chu kỳ trả lãi (B4)
  • Nợ đầu kỳ: Là số tiền gốc còn nợ tại đầu mỗi kỳ, tương đương nợ cuối kỳ trước đó
  • Gốc: Là số tiền gốc cần trả cho mỗi kỳ, tính bằng Tổng số tiền vay chia cho Tổng số kỳ trả nợ đã chuyển đổi
  • Lãi: Là số tiền lãi cần trả cho mỗi kỳ, tính bằng Nợ đầu kỳ nhân với Lãi suất đã chuyển đổi
  • Tổng cần trả: bằng Gốc cộng Lãi
  • Nợ cuối kỳ: Là số tiền nợ còn lại tại cuối mỗi kỳ
Cách tính lãi suất vay tiêu dùng trên excel 1
Cách tính lãi suất vay tiêu dùng trên excel 1

Về cách định dạng:

  • A11:F11 – Cell Styles – Heading 1 (hoặc bạn có thể chọn Heading 2)
  • A12:F12 – Cell Styles – Heading 3
  • Căn chỉnh lề phải cho A12:F12

Các chỉ tiêu được tính như sau:

Kỳ

  • Tại A13, nhập số 1 vì kỳ luôn khởi đầu từ 1
  • Tại A14, thiết lập công thức
    • Nếu tổng cộng A13 với 1 vượt quá giá trị tại ô G3 thì sẽ đặt giá trị trống, nếu không thì dùng A13 cộng thêm 1. Đảm bảo rằng việc tham chiếu tới ô G3 luôn cố định
    • Cho A15 trở đi: Sử dụng lệnh Filldown để áp dụng công thức ở A14 cho các ô phía dưới

    A14=IF(A13+1>$G$3,””,A13+1)

    Lưu ý: Nếu kỳ liền trước không có giá trị thì những kỳ sau cũng sẽ không được xem xét. Vì vậy, công thức được hoàn thành như sau:

    A14=IF(A13=””,””,IF(A13+1>$G$3,””,A13+1))

    Nợ đầu kỳ

    Cho kỳ thứ nhất: tại ô B13

    Khoản vay ban đầu

    B13=$B$2

    Đối với kỳ thứ hai: tại ô B14

    Số nợ mở đầu kỳ hai bằng số nợ kỳ cuối của kỳ một

    B14=F13

    Chú ý: Không tồn tại giá trị tại cột B nếu không có giá trị tương ứng ở cột A (Kỳ). Do đó, cần phải xem xét điều này:

    B13=IF(A13=””,””,$B$2)

    B14=IF(A14=””,””,F13)

    Áp dụng công thức từ B14 xuống các ô dưới cho các ô từ B15 trở đi

    Gốc

    Cho kỳ thứ nhất: tại ô C13

    Chia tổng số tiền vay (B2) cho tổng số kỳ vay (G3)

    C13=$B$2/$G$3

    Chú ý: Cần phải kiểm tra giá trị cột A trước khi áp dụng các giá trị cho cột khác:

    C13=IF(A13=””,””,B2/G3)

    Áp dụng công thức bắt đầu từ C13 xuống dưới cho từ C14 trở đi

    Lãi

    Lãi tính bằng việc nhân nợ đầu kỳ với lãi suất đã được quy đổi, nhớ giữ nguyên tham chiếu đến ô G5

    D13=B13*$G$5

    Chú ý: Kiểm tra giá trị cột A rồi mới tính giá trị cho các cột sau:

    D13=IF(A13=””,””,B13*$G$5)

    Áp dụng công thức bắt đầu từ D13 cho các ô từ D14 trở đi

    Tổng phải trả

    =Gốc cộng Lãi

    E13=IF(A13=””,””,C13+D13)

    Áp dụng công thức từ E13 cho từ E14 trở đi

    Nợ cuối kỳ

    =Nợ đầu kỳ trừ Gốc

    F13=IF(A13=””,””,B13-C13)

    Áp dụng công thức từ F13 cho từ F14 trở đi

    Kết quả cuối cùng hiển thị như sau:

    Cách tính lãi suất vay tiêu dùng trên excel
    Cách tính lãi suất vay tiêu dùng trên excel

    Chú ý: Sử dụng lệnh Filldown tới khi số kỳ vượt qua giá trị trong ô G3

    Để có được tổng số tiền gốc và lãi, ta đặt công thức tại dòng 11 phía trên, đối ứng với các cột cần thiết

    Đến đây, bảng tính lãi suất vay tiêu dùng theo dư nợ giảm dần đã hoàn tất. Hãy thử điều chỉnh các thông số để xem sự thay đổi của bảng tính.

    Chúc mọi người thành công!

    Tải xuống file mẫu đi kèm với bài viết tại đây: http://bit.ly/2N4p19Q

    Xem thêm:

    Các hàm tài chính và hướng dẫn sử dụng chúng trong Excel, chi tiết

    Hướng dẫn sử dụng hàm PMT để tính số tiền góp mỗi kỳ cho khoản vay trên Excel

    Cách phân tích việc trả nợ gốc và lãi qua loạt hàm PMT trong Excel

    Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop