Nếu là các bạn một kế toán tài chính viên hay như là một nhân viên ngân hàng, hoặc đơn giản và dễ dàng là nhiều người đang xem xét đầu tư vào một hạng mục nào đó thì tính lãi suất vay là các bước cần thiết cùng thường gặp. Tiếng đây, việc tính lãi suất vay theo chu kì của một khoản vay mượn hoặc chi tiêu nào đó đã trở nên đơn giản và dễ dàng hơn nhờ vận dụng hàm RATE để tính lãi vay trong Excel. Núm chắc cách thực hiện hàm RATE sẽ giúp bạn đo lường và tính toán nhanh và đúng chuẩn hơn đó. Hãy cùng khám phá về cách áp dụng hàm RATE để tính lãi suất trong bài học Excel này thuộc Gitiho nhé!


VỪA BẬN VỪA LƯỜI VẪN GIỎI EXCEL NHỜ KHÓA HỌC NÀY


*
EXG01: tuyệt đỉnh công phu Excel - trở nên bậc thầy Excel

G-LEARNING

34224 học viên


4.8 (110 tiến công giá)

499,000đ

799,000đ


*
Nhập môn Excel cùng Gitiho

G-LEARNING

11065 học viên


4.68 (22 tấn công giá)

Miễn phí

499,000đ


*
EXG05 - năng lực sử dụng phương pháp và hàm A-Z vào Excel

G-LEARNING

688 học viên


4.8 (5 tiến công giá)

499,000đ

799,000đ


Hướng dẫn sử dụng hàm RATE để tính lãi suất trong Excel

Cấu trúc hàm RATE

Hàm RATE gồm cấu trục như sau:

=RATE(nper, pmt, pv, , )

Trong đó:

nper (Bắt buộc): Kì hạn của khoản vay mượn hoặc đầu tư (tổng số kì đưa ra trả)pmt (Bắt buộc): Số tiền nên chi trả hoặc nhận ra về từng kì trong suốt kì hạnpv (Bắt buộc):Số tiền đã vay hoặc đầu tư chi tiêu (giá trị hiện tại tại)fv (Tùy chọn): Số dư chi phí mặt ước muốn sau lần chi trả cuối cùng. Nếu như không lựa chọn đối số này thì nó sẽ mặc đình bằng 0type (Tùy chọn): biểu thị thời điểm giao dịch bằng 2 số 0 hoặc 1: nếu như trả đầu kì thì nhằm 1, cuối kì thì nhằm 0 (hoặc bỏ qua)

Lưu ý:

Pmt bao gồm cả tiền nơi bắt đầu và lãi từng kỳ
Nếu pmt được bỏ lỡ thì nên đưa vào fv
Đối sốpmtbao gồm cả tiền gốc và lãi thanh toán mỗi kỳ. Nếupmtđược bỏ qua, bạn phải chuyển vào đối sốfv.Nếufvđược bỏ qua, thì nó được giả định là 0

Ví dụ cách thực hiện hàm Rate nhằm tính lãi vay trong Excel

Cho bảng tài liệu về trả góp của bạn tại 1 ẩm thực ăn uống điện máy. Hãy tính lãi vay tháng và lãi suất năm năm nhưng mà từng khách hàng phải chịu khi mua trả dần tại siêu thị nhà hàng này.

Bạn đang xem: Công thức tính lãi vay trong excel

*

Lưu ý: vì pmt (Ứng với cột E3) là số tiền phải chi ra nên phải để dấu âm (-) đằng trước.

Cách tính lãi suất vay tháng:Vì tính lãi suất theo tháng yêu cầu phải quy thay đổi tổng số kì bỏ ra trả ra theo tháng. Mà trong ví dụ, dữ liệu đề bài xích cho là theo năm, nên bắt buộc lấy dữ liệu ở cột Số năm bắt buộc trả (F3) nhân cùng với 12 tháng (*12)Tại ô G3, ta nhập bí quyết sau:

=RATE(F3*12,-E3, D3)

Kết quả trả về là 2%. Như vậy lãi suất vay theo mon mà người tiêu dùng Nguyễn Văn bạo phổi phải chịu là 2%.Sao chép phương pháp để tính đến các người sử dụng còn lại.

*
Cách tính lãi suất vay năm:Với lãi suất vay năm, ta chỉ việc lấy lãi suất vay tháng nhân cùng với 12.Tại ô H3, nhập công thức:

=G3*12

Ấn phím Enter. Tác dụng trả về là 21,4%. Như vậy, lãi suất vay theo năm mà người tiêu dùng Nguyễn Văn khỏe mạnh phải chịu là 21,4%. Coppy công thức nhằm tính cho các người sử dụng còn lại.

*

Kết luận

Chỉ với một hàm RATE đơn giản và dễ dàng mà đã giúp chúng ta tiết kiệm được không hề ít thời gian trong việc tính lãi suất vay trong Excel. Bạn hãy ghi nhớ bí quyết của hàm tính lãi suất vay trong Excel thuộc Gitiho luôn nhé: =RATE(nper, pmt, pv, , )

Mong rằng nội dung bài viết trên vẫn giúp các bạn hiểu rõ và áp dụng được cách thực hiện hàm RATE nhằm tính lãi suất trong Excel. Chúng ta cũng có thể tải về tài liệu mẫu mã ở phía dưới để dễ ợt theo dõi và thực hành thực tế hơn.

Chắc hẳn chúng ta không kỳ lạ gì với đều lời mời vay tiêu dùng của ngân hàng. Nhưng chúng ta lại không có đủ kiến thức tài chính để tự tay tính toán được số lãi buộc phải trả trên 1 hợp đồng vay mượn là bao nhiêu, liệu lời mời của ngân hàng có đích thực 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ợ giảm dần trên Excel để nắm rõ vấn đề này nhé.

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

Ví dụ các bạn xét 1 hợp đồng bao gồm các nhân tố sau: Số tiền dự định vay mượn là 100 triệu đồng thời gian vay trong 4 năm gốc và lãi trả dần dần theo tháng lãi vay phải trả là 16%/năm cách thức tính lãi theo dư nợ bớt dần
Hãy mở 1 file Excel lên và trình diễn những ngôn từ này nào:
*
Cách tính lãi vay vay tiêu dùng trên excel 4Các thông số kỹ thuật bạn đề nghị nhập riêng biệt vào từng ô, để khi phải thay đổi thông số nào bọn họ sẽ thay đổi trên riêng ô đó.Định dạng những dữ liệu như sau: A1:C1 chọn Styles là Heading1 cái 1 gồm Row height= 24 (40 pixels) chiếc 2 đến cái 6 có Row height= 15.6 (26 pixels) Cột A tất cả Column Width = 22 (205 pixels) Cột B có Column Width = 14 (133 pixels) Cột C gồm 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 vay chi tiêu và sử dụng trên excel 5Các ô B2:B6, C3, C5 chọn Cell Styles là Input để xác định đây đang là nơi để nhập dữ liệu
*
Cách tính lãi suất vay chi tiêu và sử dụng trên excel 6Như vậy bọn chúng ta đã tùy chỉnh 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 tất cả sự thay đổi của bất kỳ 1 thông số nào thì bạn cũng có thể thay đổi trực tiếp vào những vùng ô trường đoản cú B2:C6.

Xem thêm: Mẫu sổ sách kế toán bằng excel theo thông tư 200 miễn phí năm

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 đó họ mới rất có thể tính đúng được.

Hệ số quy đổi:

một năm = 12 mon 1 Quý = 3 tháng 1 năm = 4 QuýTa tất cả bảng quy đổi như sau:
*
Cách tính lãi suất 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 lại 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 văn bản cột này)

Phương pháp quy đổi

Quy về đơn vị trên B4 (làm căn cứ quy đổi)
*
Cách tính lãi suất vay chi tiêu và sử dụng trên excel 2Các ô E3:G3 cùng E5:G5 định dạng Cells Styles là CalculationTa có: E3=B4 (hệ số quy đổi thiết bị 1) E5=B4 (hệ số quy đổi sản phẩm công nghệ 1) F3=VLOOKUP(E3&C3,$K$2:$L$10,2,0) Đối tượng kiếm tì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à 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 với Hệ số G3=B3*F3 là tổng thể kỳ được quy đổi F5=VLOOKUP(E5&C5,$K$2:$L$10,2,0) tựa như như F3 G5=B5/F5 là lãi suất được quy đổi(để nắm rõ hơn vì sao là phép phân chia thì vui miệng xem lại bảng thông số quy đổi)Sau khi đã thống tốt 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 mượn 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á bán trên 1 số ít yếu tố sau: Kỳ: là từng lần tiến hành trả gốc + 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ố tiền vay cội còn lại ở đầu từng 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ố chi phí lãi bắt buộc trả trên kỳ đó = Nợ đầu kỳ * lãi suất vay đang quy đổi Tổng phải trả: =Gốc + Lãi Nợ cuối kỳ: Số tiền còn sót 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 chúng ta chọn) A12:F12 – Cell Styles – Heading 3 A12:F12: Căn lề phải
Cách tính những chỉ tiêu như sau:

Kỳ

tại A13 nhập số 1, vì chưng kỳ luôn bắt đầu từ là 1 A14 đặt công thức: ví như A13+1 lớn hơn tổng số kỳ tại ô G3 thì mang lại giá trị rỗng, nếu như không to hơn G3 thì tính theo A13+1. Chú ý cố định vị trí ô G3 A15 trở đi: Filldown phương pháp 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 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 gốc ban đầu

B13=$B$2

Tại kỳ 2: ô B14

Nợ đầu kỳ 2 = Nợ thời điểm 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 tồn tại giá trị ở các cột còn lại. Do đó buộc phải xét thêm trường thích 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 phương pháp từ B14 xuống

Gốc

Tại kỳ 1: ô C13

Tổng khoản vay (B2) phân tách cho tổng thể kỳ vay (G3)

C13=$B$2/$G$3

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

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 vay sẽ quy đổi, chú ý cố định địa điểm tham chiếu ô G5

D13=B13*$G$5

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òa hợp này trước lúc tính. Do đó:

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

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

Tổng đề nghị trả

=Gốc + Lãi

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

Từ E14 trở đi: Filldown cách làm 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ách làm từ F13 xuống
Kết quả cuối cùng ta gồm như sau:
*
Cách tính lãi vay vay tiêu dùng trên excel
Chú ý: Filldown cho tới số kỳ to hơn giá trị ở ô G3Để tính tổng số chi phí gốc, lãi, bọn họ có thể đặt phương pháp tính tổng trên mẫu 11, tương ứng với những cột

Hàm tài thiết yếu và biện pháp sử dụng các hàm tài thiết yếu trong Excel, Full