Hướng dẫn cách lập bảng excel tính lãi vay ngân hàng bằng file excel

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é.Bạn đang xem: Công thức tính lãi vay trong excel

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

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ách tính lãi suất vay tiêu dùng trên excel 4Cá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ách tính lãi suất vay tiêu dùng trên excel 5Các ô B2:B6, C3, C5 chọn Cell Styles là Input để xác định đây sẽ là nơi để nhập dữ liệu

Cách tính lãi suất vay tiêu 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 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.

Bạn đang xem: Bảng excel tính lãi vay ngân hàng

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 đó 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:

Cách tính lãi suất vay tiêu dùng trên excel 3Bả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ách tính lãi suất vay tiêu dùng trên excel 2Các ô E3:G3 và E5:G5 định dạng Cells Styles là CalculationTa 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 tính lãi suất vay tiêu dùng trên excel 1Cá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

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

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:
Cách tính lãi suất vay tiêu dùng trên excel
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

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

Khi bạn đi vay ngân hàng thì vấn đề đầu tiên cần quan tâm đó chính là lãi suất. Nếu biết rõ số lãi mà mình cần trả thì sẽ có kế hoạch trả nợ chủ động hơn. Trong bài viết hôm nay, chúng tôi sẽ gửi đến bạn file bảng tính lãi vay bằng excel khi vay ngân hàng mới nhất 2022.

Cùng tìm hiểu nhé!

Tìm hiểu vòng quay khoản phải trả là gì?

Các Hình Thức Vay Ngân Hàng

Mỗi một gói vay thì sẽ có bản chất, lãi suất, kỳ hạn khác nhau. Trước khi tìm hiểu về cách tính lãi vay ngân hàng excel thì chúng tôi sẽ giới thiệu về các hình thức vay ngân hàng phổ biến.

Vay thấu chi: Đây là hình thức vay chỉ sử dụng khi khách hàng muốn sử dụng vượt mức tiền trong tài khoản của mình. Hạn mức tối đa là gấp 5 lần lương, hồ sơ vay cần chứng minh được thu nhập.

Tải File Exel Tính Lãi Vay Ngân Hàng Mới Nhất Hiện Nay

Có rất nhiều cách được sử dụng để tính lãi suất ngân hàng như tính thủ công, tính bằng công cụ online, tính bằng file excel. Với cách tính thủ công thì bạn chỉ cần bấm máy tính và thực hiện 3 phép tính đơn giản là có thể tạm tính được lãi hàng tháng mà mình phải đóng. Tuy nhiên độ chính các của phương pháp này không cao và không tính được nhiều dữ liệu.

Cách tính lãi vay bằng máy tính cầm tay có nhiều bất tiện

Trên internet có rất nhiều công cụ giúp tính lãi suất ngân hàng rất nhanh chỉ với vài thao tác đơn giản. Tại các trang web của ngân hàng hoặc các trang thông tin tài chính thì bạn đều có thể tìm thấy các công cụ này.

File Excel tính lãi suất ngân hàng sẽ giúp khách hàng nhanh chóng tính số lãi mà mình cần phải trả hàng tháng, bạn chỉ cần vài thao tác đơn giản là đã có thể nhận được kết quả rồi. Hiện nay, nhu cầu vay ngân hàng của mọi người đang ngày càng tăng. Họ sử dụng khoản vay với rất nhiều mục đích như mua nhà, mua xe, kinh doanh…Tất cả các vấn đề tiền lãi, tiền gốc sẽ được nhân viên trao đổi trực tiếp.

Thường thì các ngân hàng sẽ tính sẵn tiền lãi hàng tháng theo phần trăm cam kết. Nhưng khách hàng cũng nên có file theo dõi riêng hàng tháng để tránh trường hợp tính sai mà bản thân mình cũng không biết. Bạn có thể tải bảng tính lãi vay bằng excel khi vay bằng ngân hàng mới nhất 2021 dưới đây.

Tải file bảng tính lãi vay bằng Exel: Download.

Hướng Dẫn Sử Dụng Bảng Exel Tính Lãi Vay Ngân Hàng

Công Thức Tính Lãi Suất

Trong file đã bao gồm các bảng tính lãi vay theo phương thức lãi suất tính trên dư nợ giảm dần và bảng tính lãi vay theo hình thức cuối kỳ trả một lần. Công thức tính như sau:

Tiền lãi tháng đầu = số tiền vay x lãi suất cố định hàng tháng
Tiền lãi những tháng tiếp theo = số tiền gốc còn lại x lãi suất vay

Số tiền mà khách hàng cần phải thanh toán mỗi tháng sẽ là: Số tiền cần phải trả mỗi tháng = số tiền vay/ thời gian vay + số tiền vay x lãi suất cố định mỗi tháng

Cách Điền Thông Tin Vào Bảng Tính Lãi Vay Bằng Exel Đúng Nhất

Trước khi lập được bảng tính lãi vay trên Excel thì khách hàng cần phải xác định được những đối tượng cần thiết để lập bảng, bao gồm:

Số tiền vay (VND)Thời gian vay (năm)Ngày vay (ngày/tháng/năm)Lãi suất hàng năm (%/năm)Khoản thanh toán hàng tháng (VND)Số thanh toán
Tổng tiền lãi
Tổng chi phí vay
Ngày thanh toán
Số dư ban đầu
Thanh toán
Tiền gốc
Lãi suất
Số dư cuối
Các yếu tố cần quan tâm khi lập bảng tính lãi vay bằng Exel

Với file bảng tính lãi suất vay trên exel khách hàng chỉ cần thực hiện các bước nhập liệu ban đầu bao gồm số tiền vay, lãi suất hàng năm, thời hạn vay tính theo năm, ngày bắt đầu vay. Khi đó file exel với các công thức được cài đặt sẵn sẽ thực hiện nhiệm vụ tính và cho ra kết quả.

Ví dụ:

Số tiền vay: 20.000.000 VNDLãi suất hàng năm: 20%/năm
Thời hạn vay: 12 tháng

Ta có kết quả như sau:

Ví dụ cách tính lãi vay bằng file Exel

Cập nhật phí trả nợ trước hạn các ngân hàng mới nhất hiện nay

Những Lợi Ích Của Bảng Excel Tính Lãi Vay Ngân Hàng

Mọi người hiện đang rất ưa chuộng sử dụng bảng tính lãi vay bằng excel vì nó có rất nhiều ưu điểm, là một công cụ hỗ trợ rất hữu ích mà tất cả những người đang vay vốn đều cần dùng đến.

Khách hàng không cần phải tính toán bằng tay phức tạp dẫn đến sai số, chỉ cần nhập các số liệu theo đúng tỷ lệ thì sẽ nhận được các kết quả nhanh chóng.Không cần phải biết quá nhiều cách tính mà chỉ cần nắm vững quy trình hạch toán lãi
Tiết kiệm được nhiều thời gian, nhân lực và chi phí cho việc tính toán. Công cụ được tính tự động theo công thức nhập sẵn cho nên sẽ rất chính xác, trả kết quả nhanh.Bạn cũng có thể chủ động được mọi công việc của mình, số tiền phải chi trả. Nếu như không tính trước khoản lãi suất phải trả thì nếu khoản vay lớn chắc chắn bạn sẽ gặp khó khăn khi đến kỳ hạn trả lãi. Việc ước tính trước sẽ giúp bạn lên kế hoạch công việc chính xác và hiệu quả hơn.Tăng cường độ chuyên nghiệp cho công việc. Đối với những nhân viên đang làm việc liên quan đến vấn đề vay vốn ngân hàng của công ty và cần ước tính số lãi để lên kế hoạch thì đây là một công cụ rất cần thiết. Bạn sẽ thể hiện được tính chuyên nghiệp trong công việc của mình đồng thời việc tính toán cũng được thực hiện chính xác và nhanh chóng hơn.

Kết Luận

Bài viết trên đây chúng tôi đã hướng dẫn cách sử dụng chi tiết và cung cấp file tải bảng tính lãi vay bằng Excel khi vay ngân hàng mới nhất năm 2021. Hy vọng những thông tin này sẽ giúp ích cho công việc của bạn. Xin chào và hẹn gặp lại các bạn ở những bài viết hữu ích tiếp theo.

Khi đi vay ngân hàng theo bất kì hình thức nào, lãi suất vay luôn là vấn đề cần được quan tâm hàng đầu. Nắm được cách tính lãi suất vay bạn sẽ chủ động hơn trong kế hoạch trả nợ, cũng như cân nhắc về số tiền muốn vay và thời gian vay phù hợp nhất với tình hình tài chính hiện tại. Trong bài viết này, mamnongautruc.edu.vn sẽ chia sẻ với các bạn file Excel và cách tính lãi vay ngân hàng chính xác nhất.

1. Các hình thức vay ngân hàng hiện nay

1.1. Vay tín chấp

Là hình thức vay vốn ngân hàng không cần tài sản đảm bảo. Hình thức vay này dựa hoàn toàn trên uy tín của người vay. Hình thức vay này phù hợp với cá nhân với những nhu cầu nhỏ như mua sắm, vui chơi giải trí. Lãi suất cho khoản vay khá cao, thời gian vay tối đa là 60 tháng.

1.2. Vay thấu chi

Đây là hình thức vay cho những khách hàng cá nhân khi có nhu cầu sử dụng vượt số tiền hiện có trong tài khoản thanh toán của cá nhân. Hạn mức được cấp chỉ gấp 5 lần lương. Hồ sơ vay yêu cầu có chứng thực về khoản thu nhập cố định mỗi tháng.

1.3. Vay thế chấp

Là hình thức vay truyền thống của ngân hàng. Theo hình thức vay này người đi vay phải có tài sản đảm bảo. Hạn mức vay khá cao lên đến 80% – 120% giá trị tài sản cầm cố. Lãi suất không quá cao, phù hợp với khoản vay. Thời hạn vay kéo dài lên đến 25 năm theo nhu cầu người vay. Hình thức vay này phù hợp cho cả doanh nghiệp và cá nhân. Lưu ý quan trọng khi vay thế chấp là các khoản phí đi kèm như phí trả chậm hay phí trả trước hạn đôi khi sẽ khá cao.

1.4. Vay trả góp

Là hình thức cho vay mà tiền lãi và gốc mỗi tháng bằng nhau. Tùy thuộc nhu cầu cũng như khả năng trả nợ của mỗi khách hàng mà có thời hạn cũng như hạn mức trả vay khác nhau.

2. Cách sử dụng file Excel tính lãi vay ngân hàng

Trong file sẽ có các bảng tính lãi vay theo phương thức lãi suất tính trên dư nợ giảm dần và tính lãi vay theo phương thức cuối kỳ trả một lần. Các bạn chỉ cần chọn sheet có công thức tính lãi vay theo cách trả nợ của mình. Sau đó các bạn tiến hành nhập số nợ gốc, lãi suất, ngày vay, thời hạn vay là file sẽ tự tính ra số tiền lãi phải trả hàng kỳ cũng như tổng số tiền lãi phải trả của cả khoản vay.

Xem thêm: Thất sơn tâm linh official trailer thất sơn tâm linh, thất sơn tâm linh

11/01/2023

Hướng Dẫn Cách Lập Bảng Excel Tính Lãi Vay Ngân Hàng Bằng File Excel