Chắc hẳn các bạn không lạ gì với rất nhiều lời mời vay chi tiêu và sử dụng của ngân hàng. Nhưng chúng ta lại không có đủ kiến thức và kỹ năng tài chính để từ bỏ 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 bank có thực sự hấp dẫn? Hãy thuộc Học Excel Online lập bảng tính lãi suất vay vay tiêu dùng theo dư nợ sút dần trên Excel để nắm rõ vấn đề này nhé.
Bạn đang xem: Lập bảng kế hoạch trả nợ gốc và lãi vay
Xác định các thông số trong hợp đồng vay
Mỗi một hợp đồng vay đều bao gồm sẵn một vài thông số cơ bản được biểu lộ rõ ràng, vắt 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ố kỹ thuật này có thể thay đổi theo từng các loại hợp đồng, nên chúng ta cần trình bày thế nào cho các số lượng này cũng rất có thể thay đổi được.
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 mượn là 100 triệu đồngThời gian vay vào 4 nămGốc với lãi trả dần theo thángLãi suất yêu cầu trả là 16%/nămPhương pháp tính lãi theo dư nợ bớt dầnHãy mở 1 file Excel lên và trình diễn những ngôn từ này nào:
Các thông số kỹ thuật bạn đề nghị nhập riêng vào từng ô, để khi buộc phải thay đổi thông số kỹ thuật nào bọn họ sẽ thay đổi tại riêng ô đó.
Xem thêm: Dành Cả Thanh Xuân Để Yêu Một Người Vô Tâm, Dành Cả Thanh Xuân
Định dạng những dữ liệu như sau:
A1:C1 lựa chọn Styles là Heading1Dòng 1 có Row height= 24 (40 pixels)Dòng 2 đến mẫu 6 gồm Row height= 15.6 (26 pixels)Cột A bao gồm Column Width = 22 (205 pixels)Cột B gồm Column Width = 14 (133 pixels)Cột C có Column Width = 8 (79 pixels)
Thiết lập Data Validation trên các ô C3, B4, C5 là:
Allow=ListSource=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 bọn chúng ta đã tùy chỉnh cấu hình xong phần Khai báo thông số. Với từng 1 phương án vay không giống nhau, hoặc tất cả sự thay đổi của ngẫu nhiên 1 thông số nào thì bạn có thể thay đổi thẳng vào những vùng ô trường đoản cú B2:C6.
Quy đổi để thống nhất đơn vị tính cho những 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ămKỳ trả lãi (ô B4) lại tính theo đơn vị là ThángDo đó bài toán cần làm tiếp theo sau là quy đổi về đồng nhất đơn vị. Khi đó họ mới hoàn toàn có thể tính đúng được.
Hệ số quy đổi:
1 Năm = 12 Tháng1 Quý = 3 tháng1 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 trường đoản cú cột Đơn vị 1 đến cột Hệ số (riêng cột Cách nhập là diễn giải phương pháp nhập ra hệ số, không bắt buộc nhập lại văn bản 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 cùng 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 sản phẩm 1)F3=VLOOKUP(E3&C3,$K$2:$L$10,2,0) Đối tượng tìm kiếm kiếm của hàm Vlookup là ghép hệ số quy đổi trên 2 ô E3 cùng với C3, trong đó E3 là hệ số 1, C3 là thông số 2; vùng bảng tham chiếu là bảng quy đổi, trong 2 cột Ghép cùng Hệ sốG3=B3*F3 là tổng thể kỳ được quy đổiF5=VLOOKUP(E5&C5,$K$2:$L$10,2,0) tương tự như như F3G5=B5/F5 là lãi suất được quy đổi(để hiểu rõ hơn nguyên nhân là phép chia thì sung sướng xem lại bảng hệ số quy đổi)
Sau khi đã thống độc nhất về 1 đơn vị, bạn có thể bắt đầu thực hiện xây dựng bảng tính lãi đến 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á chỉ trên 1 số yếu tố sau:
Kỳ: là mỗi lần thực hiện trả nơi bắt đầu + lãi. Số kỳ xác định dựa theo Tổng thời hạn vay (G3) và Kỳ trả lãi (B4)Nợ đầu kỳ: Số tiền vay gốc còn lại ở đầu từng kỳ = Nợ vào cuối kỳ trước đóGốc: Số tiền trả tính vào cội tại kỳ đó = Tổng số chi phí vay / tổng số kỳ trả vay đã quy đổiLãi: Số tiền lãi phải trả tại kỳ đó = Nợ đầu kỳ * lãi suất vay vay đang quy đổiTổng đề xuất trả: =Gốc + LãiNợ cuối kỳ: Số tiền còn sót 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 3A12:F12: Căn lề phảiCách tính các chỉ tiêu như sau:
Kỳ
Tại A13 nhập số 1, do kỳ luôn bắt đầu từ bỏ 1A14 đặt công thức: nếu A13+1 lớn hơn tổng số kỳ tại ô G3 thì mang đến giá trị rỗng, nếu như không to hơn G3 thì tính theo A13+1. Chú ý cố định vị trí ô G3A15 trở đi: Filldown phương pháp từ A14 xuốngA14=IF(A13+1>$G$3,””,A13+1)
Chú ý: giả dụ kỳ trước đó đã trống rỗng thì không xét những 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 cội ban đầu
B13=$B$2
Tại kỳ 2: ô B14
Nợ đầu kỳ 2 = Nợ vào cuối kỳ 1
B14=F13
Lưu ý: trường hợp 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 đó cần xét thêm trường vừa lòng này trước khi tính. Do đó:
B13=IF(A13=””,””,$B$2)
B14=IF(A14=””,””,F13)
Từ B15 trở đi: Filldown bí quyết từ B14 xuống
Gốc
Tại kỳ 1: ô C13
Tổng khoản vay (B2) phân tách cho tổng cộng kỳ vay mượn (G3)
C13=$B$2/$G$3
Lưu ý: ví như trường hợp không có giá trị tương ứng ở cột A (Kỳ) thì sẽ không tồn tại giá trị ở các cột còn lại. Do đó cần 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ách làm từ C13 xuống
Lãi
Lãi = Nợ đầu kỳ * lãi suất vay vay sẽ quy đổi, chú ý cố định vị trí tham chiếu ô G5
D13=B13*$G$5
Lưu ý: nếu như trường hợp không có giá trị tương ứng ở cột A (Kỳ) thì sẽ không tồn tại giá trị ở những cột còn lại. Do đó nên xét thêm trường vừa lòng này trước lúc tính. Do đó:
D13=IF(A13=””,””,B13*$G$5)
Từ D14 trở đi: Filldown cách làm từ D13 xuống
Tổng đề nghị trả
=Gốc + Lãi
E13=IF(A13=””,””,C13+D13)
Từ E14 trở đi: Filldown bí quyết từ E13 xuống
Nợ cuối kỳ
=Nợ đầu kỳ – Nợ gốc
F13=IF(A13=””,””,B13-C13)
Từ F14 trở đi: Filldown phương pháp từ F13 xuống
Kết quả ở đầu cuối ta bao gồm như sau:
Chú ý: Filldown cho tới số kỳ lớn hơn giá trị ở ô G3
Để tính tổng số chi phí gốc, lãi, bọn họ có thể đặt cách làm tính tổng trên mẫu 11, tương ứng với những cột
Như vậy là chúng ta đã kết thúc bảng tính lãi vay vay tiêu dùng theo dư nợ bớt dần bên trên Excel rồi. Hãy thử thay đổi các điều khiếu nại thông số để xem bảng tính của bọn họ thay đổi cố nào nhé.
