Hướng dẫn cách 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

By excel-cracker.com On Th1 15, 2022

Chắc hẳn bạn không còn xa lạ với những lời mời vay tiêu dùng từ các ngân hàng. Nhưng bạn chưa có đủ kiến thức về tài chính để tự tính số lãi phải trả trên hợp đồng vay, lời mời của ngân hàng có thực sự hấp dẫn? Hãy Học Excel Online tạo ra Công cụ tính lãi suất vay tiêu dùng Theo dõi số dư giảm dần trên Excel để hiểu vấn đề này.

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

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

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

Ví dụ: hãy xem xét một hợp đồng bao gồm các yếu tố sau:

Số tiền vay dự kiến là 100 triệu đồng
Thời gian vay trong 4 năm
Trả gốc và lãi hàng tháng
Lãi phải trả là 16% / năm
Phương pháp tính lãi theo số dư giảm dần

Hãy mở một tệp Excel và trình bày các nội dung sau:

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

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

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

A1: C1 chọn Phong cách được Heading1
Dòng 1 có Chiều cao hàng= 24 (40 pixel)
Từ dòng 2 đến dòng 6 có Chiều cao hàng= 15,6 (26 điểm ảnh)
Cột A có Chiều rộng Cột = 22 (205 pixel)
Cột B có Chiều rộng Cột = 14 (133 pixel)
Cột C có Chiều rộng Cột = 8 (79 pixel)

Thiết lập Xác nhận dữ liệu trong những cái hộp C3, B4, C5 được:

Cho phép = Danh sách
Nguồn = Tháng, Quý, Năm

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

Các tế bào B2: B6, C3, C5 lựa chọn Kiểu dáng ô được Đầu vào để xác định đây sẽ là nơi nhập dữ liệu

>>> Xem thêm: Hướng dẫn cách kiểm tra hóa đơn có số lớn hơn nhưng sử dụng trước với hàm COUNTIFS

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

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

Chuyển đổi để thống nhất các đơn vị cho các tham số

Chúng ta có thể nhìn thấy :

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

Do đó, việc cần làm tiếp theo là chuyển đổi sang các đơn vị đồng nhất. Chỉ khi đó chúng ta mới có thể làm đúng.

Hệ số chuyển đổi:

1 năm = 12 tháng
1 quý = 3 tháng
1 năm = 4 khu

Chúng tôi có bảng chuyển đổi sau:

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

Bảng chuyển đổi này có thể được đặt trong hộp I1: L10 từ cột Bài 1 vào cột Hệ số (riêng cột) Làm thế nào để nhập là giải thích 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

Tham khảo đơn vị tại B 4 (làm cơ sở để chuyển đổi)

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

Các tế bào E3: G3 và E5: G5 Định dạng Kiểu ô được Phép tính

Chúng ta có:

E3 = B4 (hệ số chuyển đổi đầu tiên)
E5 = B4 (hệ số chuyển đổi đầu tiên)
F3 = VLOOKUP (E3 & C3, $ K $ 2: $ L $ 10,2,0) Đối tượng tìm kiếm của hàm Vlookup là kết hợp hệ số chuyển đổi trong 2 ô E3 với C3, trong đó E3 là hệ số của 1, C3 là hệ số . 2; khu vực bảng tham chiếu là bảng chuyển đổi, trong 2 cột Ghép nối và Hệ số
G3 = B3 * F3 là tổng số khoảng thời gian được chuyển đổi
F5 = VLOOKUP (E5 & C5, $ K $ 2: $ L $ 10,2,0) giống như F3
G5 = B5 / F5 là lãi suất quy đổi

(Để hiểu rõ hơn tại sao lại phân chia, vui lòng xem lại bảng hệ số chuyển đổi)

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

>>> Xem thêm: Excel kế toán: Ứng dụng hàm SUMPRODUCT – Phần 1

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

Khi xem xét phương án trả nợ, chúng ta cần đánh giá một số yếu tố sau:

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

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

Cách định dạng:

A11: F11 – Kiểu ô – Tiêu đề 1 (hoặc Tiêu đề 2 do bạn chọn)
A12: F12 – Kiểu ô – Tiêu đề 3
A12: F12: Căn phải

Các chỉ số được tính như sau:

Ky

Tại A13 nhập số 1, vì khoảng thời gian luôn bắt đầu từ 1
A14 đặt công thức: Nếu A13 + 1 lớn hơn tổng số chu kỳ trong ô G3, nó sẽ là rỗng, nếu không lớn hơn G3, nó sẽ được tính theo A13 + 1. Chú ý đến vị trí của ô G3
A15 trở đi: Điền công thức từ A14 trở xuống

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

Lưu ý: Nếu kỳ trước đã trống, các kỳ tiếp theo sẽ không được xem xét. 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 ban đầu

B13 = $ B $ 2

Trong khoảng thời gian 2: ô B14

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

B14 = F13

Lưu ý: Nếu không có giá trị tương ứng trong cột A (dấu chấm) thì sẽ không có giá trị ở các cột còn lại. Vì vậy, trường hợp này phải cân nhắc trước khi tính toán. Vì vậy:

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

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

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

Nguồn gốc

Tại kỳ 1: ô C13

>>> Xem thêm: Hướng dẫn Code VBA để lấy 4 ký tự cuối cùng của chuỗi

Tổng các khoản cho vay (B2) chia cho Tổng thời gian cho vay (G3)

C13 = $ B $ 2 / $ G $ 3

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

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

Quan tâm

Tiền lãi = Nợ đầu kỳ * Lãi suất tiền vay quy đổi, chú ý vị trí tham chiếu của ô G5

D13 = B13 * $ G $ 5

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

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

Tổng số tiền phải trả

= Tiền gốc + tiền lãi

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

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

Nợ cuối kỳ

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

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

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

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

Lưu ý: Điền xuống một dấu chấm lớn hơn giá trị trong ô G3

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

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

Chúc may mắn!

Tải file mẫu đính kèm bài viết tại: http://bit.ly/2N4p19Q

Xem thêm:

Các 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 tháng cho một khoản vay trong Excel

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

Nguồn : Blog hocexcel