Chắc hẳn bạn không lạ gì với những lời mời vay tiêu dùng của ngân hàng. Nhưng bạn lại không có đủ kiến thức tài chính để tự tay tính toán được số lãi phải trả trên 1 hợp đồng vay là bao nhiêu, liệu lời mời của ngân hàng có thực sự hấp dẫn? Hãy cùng Học Excel Online lập bảng tính lãi suất vay tiêu dùng theo dư nợ giảm dần trên Excel để hiểu rõ vấn đề này nhé.

Xác định các thông số trong hợp đồng vay

Mỗi một hợp đồng vay đều có sẵn một vài thông số cơ bản được thể hiện rõ ràng, cụ thể. Việc đầu tiên là chúng ta cần ghi lại các thông số đó để làm căn cứ tính toán. Các thông số này có thể thay đổi theo từng loại hợp đồng, nên chúng ta cần trình bày sao cho các con số này cũng có thể thay đổi được.

Xem thêm: Công thức tính lãi kép trong excel vô cùng dễ dàng và đơn giản

Ví dụ bạn xét 1 hợp đồng bao gồm các yếu tố sau:

• Số tiền dự định vay là 100 triệu đồng
• Thời gian vay trong 4 năm
• Gốc và lãi trả dần theo tháng
• Lãi suất phải trả là 16%/năm
• Phương pháp tính lãi theo dư nợ giảm dần

Hãy mở 1 file Excel lên và trình bày những nội dung này nào:

Các thông số bạn cần nhập riêng vào từng ô, để khi cần thay đổi thông số nào chúng ta sẽ thay đổi tại riêng ô đó.

Định dạng các dữ liệu như sau:

• A1:C1 chọn Styles là Heading1
• Dòng 1 có Row height= 24 (40 pixels)
• Dòng 2 đến dòng 6  có Row height= 15.6 (26 pixels)
• Cột A có  Column Width = 22 (205 pixels)
• Cột B có Column Width = 14 (133 pixels)
• Cột C có Column Width = 8 (79 pixels)

Thiết lập Data Validation tại các ô C3, B4, C5 là:

• Allow=List
• Source=Tháng, Quý, Năm

Các ô B2:B6, C3, C5 chọn Cell Styles là Input để xác định đây sẽ là nơi để nhập dữ liệu

Như vậy chúng ta đã thiết lập xong phần Khai báo thông số. Với mỗi 1 phương án vay khác nhau, hoặc có sự thay đổi của bất kỳ 1 thông số nào thì chúng ta có thể thay đổi trực tiếp vào các vùng ô từ B2:C6.

Quy đổi để thống nhất đơn vị tính cho các thông số

Chúng ta thấy :

• Thời gian vay (C3) và lãi suất (C5) tính theo đơn vị là Năm
• Kỳ trả lãi (ô B4) lại tính theo đơn vị là Tháng

Do đó việc cần làm tiếp theo là quy đổi về đồng nhất đơn vị. Khi đó chúng ta mới có thể tính đúng được.

Hệ số quy đổi:

• 1 Năm = 12 Tháng
• 1 Quý = 3 tháng
• 1 Năm = 4 Quý

Ta có bảng quy đổi như sau:

Bảng quy đổi này có thể đặt tại ô I1:L10 từ cột Đơn vị 1 đến cột Hệ số (riêng cột Cách nhập là diễn giải cách nhập ra hệ số, không cần nhập lại nội dung cột này)

Phương pháp quy đổi

Quy về đơn vị tại B4 (làm căn cứ quy đổi)

Các ô E3:G3 và E5:G5 định dạng Cells Styles là Calculation

Ta có:

• E3=B4 (hệ số quy đổi thứ 1)
• E5=B4 (hệ số quy đổi thứ 1)
• F3=VLOOKUP(E3&C3,$K$2:$L$10,2,0) Đối tượng tìm kiếm của hàm Vlookup là ghép hệ số quy đổi tại 2 ô E3 với C3, trong đó E3 là hệ số 1, C3 là hệ số 2; vùng bảng tham chiếu là bảng quy đổi, trong 2 cột Ghép và Hệ số
• G3=B3*F3 là tổng số kỳ được quy đổi
• F5=VLOOKUP(E5&C5,$K$2:$L$10,2,0) tương tự như F3
• G5=B5/F5 là lãi suất được quy đổi

(để hiểu rõ hơn tại sao là phép chia thì vui lòng xem lại bảng hệ số quy đổi)

Sau khi đã thống nhất về 1 đơn vị, chúng ta có thể bắt đầu tiến hành xây dựng bảng tính lãi cho khoản vay như sau:

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

Khi xét kế hoạch trả vay, chúng ta cần đánh giá trên 1 số yếu tố sau:

• Kỳ: là mỗi lần thực hiện trả gốc + lãi.  Số kỳ xác định dựa theo Tổng thời gian vay (G3) và Kỳ trả lãi (B4)
• Nợ đầu kỳ: Số tiền vay gốc còn lại ở đầu mỗi kỳ = Nợ cuối kỳ trước đó
• Gốc: Số tiền trả tính vào gốc tại kỳ đó = Tổng số tiền vay / Tổng số kỳ trả vay đã quy đổi
• Lãi: Số tiền lãi phải trả tại kỳ đó = Nợ đầu kỳ * Lãi suất vay đã quy đổi
• Tổng phải trả: =Gốc + Lãi
• Nợ cuối kỳ: Số tiền còn lại tại cuối kỳ

Cách định dạng:

• A11:F11 – Cell Styles – Heading 1 (hoặc Heading 2 tùy bạn chọn)
• A12:F12 – Cell Styles – Heading 3
• A12:F12: Căn lề phải

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

Kỳ

• Tại A13 nhập số 1, bởi kỳ luôn bắt đầu từ 1
• A14 đặt công thức: Nếu A13+1 lớn hơn tổng số kỳ tại ô G3 thì cho giá trị rỗng, nếu không lớn hơn G3 thì tính theo A13+1. Chú ý cố định vị trí ô G3
• A15 trở đi: Filldown công thức từ A14 xuống

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

Chú ý: Nếu kỳ trước đó đã rỗng thì không xét các kỳ sau đó nữa. Do đó công thức hoàn chỉnh là:

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

Nợ đầu kỳ

Tại kỳ 1: ô B13

Khoản vay gốc ban đầu

B13=$B$2

Tại kỳ 2: ô B14

Nợ đầu kỳ 2 = Nợ cuối kỳ 1

B14=F13

Lưu ý: Nếu trường hợp không có giá trị tương ứng ở cột A (Kỳ) thì sẽ không có giá trị ở các cột còn lại. Do đó phải xét thêm trường hợp này trước khi tính. Do đó:

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

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

Từ B15 trở đi: Filldown công thức từ B14 xuống

Gốc

Tại kỳ 1: ô C13

Tổng khoản vay (B2) chia cho Tổng số kỳ vay (G3)

C13=$B$2/$G$3

Lưu ý: Nếu trường hợp không có giá trị tương ứng ở cột A (Kỳ) thì sẽ không có giá trị ở các cột còn lại. Do đó phải xét thêm trường hợp này trước khi tính. Do đó:

C13=IF(A13=””,””,$B$2/$G$3)

Từ C14 trở đi: Filldown công thức từ C13 xuống

Lãi

Lãi = Nợ đầu kỳ * Lãi suất vay đã quy đổi, chú ý cố định vị trí tham chiếu ô G5

D13=B13*$G$5

Lưu ý: Nếu trường hợp không có giá trị tương ứng ở cột A (Kỳ) thì sẽ không có giá trị ở các cột còn lại. Do đó phải xét thêm trường hợp này trước khi tính. Do đó:

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

Từ D14 trở đi: Filldown công thức từ D13 xuống

Tổng phải trả

=Gốc + Lãi

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

Từ E14 trở đi: Filldown công thức từ E13 xuống

Nợ cuối kỳ

=Nợ đầu kỳ – Nợ gốc

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

Từ F14 trở đi: Filldown công thức từ F13 xuống

Kết quả cuối cùng ta có như sau:

Chú ý: Filldown tới số kỳ lớn hơn giá trị ở ô G3

Để tính tổng số tiền gốc, lãi, chúng ta có thể đặt công thức tính tổng trên dòng 11, tương ứng với các cột

Như vậy là chúng ta đã hoàn thành bảng tính lãi suất vay tiêu dùng theo dư nợ giảm dần trên Excel rồi. Hãy thử thay đổi các điều kiện thông số để xem bảng tính của chúng ta thay đổi thế nào nhé.

Chúc các bạn thành công!

Tải file mẫu kèm theo bài viết tại địa chỉ: http://bit.ly/2N4p19Q

Xem thêm:

Hàm tài chính và cách sử dụng các hàm tài chính trong Excel, Full

Cách sử dụng hàm PMT tính số tiền thanh toán hàng kỳ cho khoản vay trong Excel

Hướng dẫn cách phân tích trả nợ gốc và lãi cùng chuỗi hàm PMT trong Excel