Bảng Excel Tính Lãi Vay Ngân Hàng

     
Chắc hẳn các bạn không kỳ lạ gì với đầy đủ lời mời vay chi tiêu và sử dụng của ngân hàng. Nhưng bạn lại không có đủ kỹ năng và kiến thức tài chính để tự tay tính toán được số lãi đề nghị trả trên 1 hợp đồng vay mượn là bao nhiêu, liệu lời mời của bank 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 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: Bảng excel tính lãi vay ngân hàng


Xác định các thông số kỹ thuật trong hợp đồng vay

*
Cách tính lãi suất vay chi tiêu và sử dụng trên excel 4Các thông số bạn đề nghị nhập riêng biệt vào từng ô, để khi cần thay đổi thông số kỹ thuật nào chúng ta sẽ thay đổi tại riêng ô đó.Định dạng những dữ liệu như sau: A1:C1 chọn StylesHeading1 loại 1 có Row height= 24 (40 pixels) dòng 2 đến mẫu 6 có Row height= 15.6 (26 pixels) Cột A tất cả Column Width = 22 (205 pixels) Cột B tất cả 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=List Source=Tháng, Quý, Năm
*
Cách tính lãi suất vay chi tiêu và sử dụng trên excel 5Các ô B2:B6, C3, C5 lựa chọn Cell Styles Input để xác định đây vẫn là nơi để nhập dữ liệu
*
Cách tính lãi suất vay vay chi tiêu và sử dụng trên excel 6Như vậy chúng ta đã thiết lập 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ố kỹ thuật nào thì bạn có thể thay đổi trực tiếp vào các vùng ô từ bỏ 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ángDo đó vấn đề cần làm tiếp theo là quy đổi về đồng nhất đơn vị. Khi đó bọn họ mới có thể tính đúng được.

Xem thêm: Hãy Yêu Nhau Dưới Ánh Trăng Tròn Truyện, Hãy Yêu Nhau Dưới Ánh Trăng Tròn

Hệ số quy đổi:

một năm = 12 tháng 1 Quý = 3 tháng một năm = 4 QuýTa bao gồm bảng quy đổi như sau:
*
Cách tính lãi vay vay tiêu dùng trên excel 3Bảng quy đổi này còn có thể đặt tại ô I1:L10 từ cột Đơn vị 1 mang đến cột Hệ số (riêng cột Cách nhập là diễn giải bí quyết nhập ra hệ số, không buộc phải nhập lại ngôn từ 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ách tính lãi suất vay vay tiêu dùng trên excel 2Các ô E3:G3E5:G5 định dạng Cells StylesCalculationTa có: E3=B4 (hệ số quy đổi đồ vật 1) E5=B4 (hệ số quy đổi máy 1) F3=VLOOKUP(E3&C3,$K$2:$L$10,2,0) Đối tượng tra cứu kiếm của hàm Vlookup là ghép hệ số quy đổi tại 2 ô E3 với C3, trong đó E3 là thông số 1, C3 là thông số 2; vùng bảng tham chiếu là bảng quy đổi, vào 2 cột Ghép cùng 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ư như F3 G5=B5/F5 là lãi suất được quy đổi(để hiểu rõ hơn nguyên nhân là phép phân chia thì vui lòng xem lại bảng thông số quy đổi)Sau khi đã thống độc nhất về 1 đơn vị, chúng ta có thể bắt đầu thực hiện xây dựng bảng tính lãi mang đến khoản vay mượn như sau:

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

Khi xét planer trả vay, bọn họ cần đánh giá 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 vào Tổng thời gian vay (G3) và Kỳ trả lãi (B4) Nợ đầu kỳ: Số chi phí vay nơi bắt đầu còn lại ở đầu mỗi kỳ = Nợ thời điểm 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 / toàn bô kỳ trả vay đã quy đổi Lãi: Số chi phí lãi nên trả tại kỳ đó = Nợ đầu kỳ * lãi suất vay đã quy đổi Tổng yêu cầu trả: =Gốc + Lãi Nợ cuối kỳ: Số tiền còn lại tại cuối kỳ
*
Cách tính lãi suất vay vay chi tiêu và sử dụng trên excel 1Cách định dạng: A11:F11 – Cell Styles – Heading 1 (hoặc Heading 2 tùy các bạn chọn) A12:F12 – Cell Styles – Heading 3 A12:F12: Căn lề phảiCách tính các chỉ tiêu như sau:

Kỳ

trên A13 nhập số 1, vày kỳ luôn bắt đầu từ 1 A14 đặt công thức: trường hợp A13+1 to hơn tổng số kỳ tại ô G3 thì đến giá trị rỗng, giả dụ không to 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ốngA14=IF(A13+1>$G$3,””,A13+1)

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

Xem thêm: Giải Bài Tập Toán 12 Trang 43, Giải Bài 1 Trang 43 Sgk Giải Tích 12

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 ý: giả dụ trường hợp không tồn tại 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 đó đề xuất xét thêm trường hòa hợp này trước lúc tính. Do đó:

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


*

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

Từ B15 trở đi: Filldown cách làm từ B14 xuống

Gốc

Tại kỳ 1: ô C13

Tổng khoản vay mượn (B2) chia cho tổng cộng kỳ vay (G3)

C13=$B$2/$G$3

Lưu ý: giả dụ trường hợp không tồn tại giá trị tương ứng ở cột A (Kỳ) thì sẽ không có giá trị ở những cột còn lại. Do đó cần xét thêm trường phù hợp này trước lúc 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 đang quy đổi, chú ý cố định địa điểm tham chiếu ô G5

D13=B13*$G$5

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 tồn tại giá trị ở những cột còn lại. Do đó yêu cầu xét thêm trường vừa lòng này trước khi tính. Do đó:

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

Từ D14 trở đi: Filldown cách làm từ D13 xuống

Tổng yêu cầu 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 công thức từ F13 xuốngKết quả cuối cùng ta bao gồm như sau:
*

Hàm tài bao gồm và bí quyết sử dụng các hàm tài thiết yếu trong Excel, Full