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
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á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á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

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:

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á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:
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 đị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
Tổng các khoản cho vay (B2) chia cho Tổng thời gian cho vay (G3)
C13 = $ B $ 2 / $ G $ 3
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:
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
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:
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