Trong khi làm việc với các khoản vay đã vay, chúng ta phải tính toán số tiền lãi hoặc vốn mà chúng ta phải trả cho khoản vay đó. Chúng tôi có thể dễ dàng tính lãi cho khoản vay trong Excel bằng cách sử dụng tích hợp sẵnchức năng tài chínhđặt tênPMT,IPMT,PPMT, VàCUMIPMT. Trong bài viết này, tôi sẽ chỉ cho bạn cách sử dụng các hàm này để tính tiền lãi trong một khoảng thời gian nhất định, tiền lãi trong một năm nhất định vàlãi suất.
Mục lục trốn
Tải sách bài tập thực hành
5 phương pháp thích hợp để tính lãi cho một khoản vay trong Excel
1. Tính khoản hoàn trả khoản vay cố định hàng tháng hoặc hàng năm
2. Tìm hiểu khoản thanh toán lãi cho khoản vay cho tháng hoặc năm cụ thể
3. Tính Thanh toán vốn cho Tháng hoặc Năm cụ thể trong Excel
4. Xác định lãi vay tích lũy cho tháng hoặc năm cụ thể trong Excel
5. Tính lãi kép cho khoản vay bằng hàm FV
Những điều cần ghi nhớ
Phần kết luận
Những bài viết liên quan
Tải sách bài tập thực hành
Tải xuống cuốn sách thực hành này để thực hiện nhiệm vụ trong khi bạn đang đọc bài viết này.
Tính Lãi.xlsx
5 phương pháp thích hợp để tính lãi cho một khoản vay trong Excel
Giả sử một tình huống mà chúng ta có khoản vay lên tới 5000 đô la. Lãi suất hàng năm cho khoản vay là 4% mỗi năm. Khoản vay đã được thực hiện trong 5 năm. Chúng ta cần tính tiền lãi từ dữ liệu đã cho này. Trong phần này, chúng ta sẽ thảo luận về năm phương pháp khác nhau để tính lãi cho khoản vay trong excel.
1. Tính khoản hoàn trả khoản vay cố định hàng tháng hoặc hàng năm
Bạn có thể dùngChức năng PMTđể tính lãi suất cố định cho một khoản vay trong Excel trong một khoảng thời gian nhất định.
Giới thiệu về chức năng PMT
Mục tiêu chức năng:
Tính toán khoản hoàn trả cho một khoản vay dựa trên khoản thanh toán không đổi và lãi suất không đổi.
Cú pháp:
=PMT(tỷ lệ, mỗi, nper, pv, [fv], [loại])
Giải thích đối số:
| Lý lẽ | Bắt buộc/Tùy chọn | Giải trình |
|---|---|---|
| Tỷ lệ | Yêu cầu | Lãi suất mỗi kỳ. |
| Ví dụ | Yêu cầu | Tổng số lần thanh toán cho khoản vay. |
| pv | Yêu cầu | Giá trị hiện tại, hoặc tổng số tiền mà một loạt các khoản thanh toán trong tương lai có giá trị hiện tại. Còn được gọi là hiệu trưởng. |
| Fv | Không bắt buộc | Giá trị tương lai hoặc số dư tiền mặt bạn muốn đạt được sau lần thanh toán cuối cùng được thực hiện. Nếu chúng ta không chèn giá trị cho fv, nó sẽ được coi là 0 (ví dụ: giá trị tương lai của khoản vay là 0). |
| Kiểu | Không bắt buộc | Số 0 hoặc 1. Nó cho biết thời điểm đến hạn thanh toán. Nếu loại bị bỏ qua, nó được coi là 0. |
Tham số trả về:
Thanh toán cho một khoản vay dựa trên các khoản thanh toán không đổi và lãi suất không đổi.
Bước 1:
- Bước đầu tiên của chúng tôi là chọn ô mà chúng tôi muốn có tổng số tiền hoàn trả trong một khoảng thời gian nhất định. Chúng tôi sẽ chọn ôQ10để tính tổng số tiền trả nợ trong một tháng.
- Bây giờ chúng ta sẽ viết raPMTcông thức trong ô đó.
=PMT(C4/12, C7, C8)
Phân tích công thức:
- C4 =Tỷ lệ (Đối số đầu tiên)= Lãi suất hàng năm =4%
Vì chúng tôi đang tính toán khoản thanh toán cố định trong một tháng, chúng tôi đã chia nó cho số tháng trong một năm,12.
- C7 =Npr(Đối số thứ hai)= Tổng số lần thanh toán =60
Chúng tôi có 5 năm để trả lại khoản vay. 5 năm có tổng cộng là (5X12) =60 tháng
- C8 =Pv(Đối số thứ ba)= Tổng số tiền vay hoặc Tiền gốc =5.000 USD
Bước 2:
- Khi nhấp vàoĐI VÀO, chúng tôi sẽ nhận được số tiền cố định phải trả hàng tháng hoặc số tiền trả nợ cố định hàng tháng. Số tiền này là như nhau cho mỗi tháng. Nó bao gồm phần tiền vốn hay tiền gốc và cả số tiền lãi chúng ta phải trả trong tháng đầu tiên.
Bước 3:
- Chúng tôi cũng có thể tính toán trả nợ cố định chomỗi năm. Để tính số tiền trả nợ phải trả hàng năm, chúng ta phải sử dụng công thức sau.
=PMT(F4, F6, F8)
- Như chúng ta có thể thấy, chúng ta không cần phải chialãi suất hàng nămbằng 12. Chúng tôi đang tính toán số tiền trong một năm. VàNprhoặc tổng số lần thanh toán bây giờ là 5 như chúng ta có5 nămđể trả hết khoản vay. Hình ảnh dưới đây cho thấy số tiền hoàn trả cố định hàng năm.
Đọc thêm:Cách tính lãi suất cho khoản vay trong Excel (2 tiêu chí)
2. Tìm hiểu khoản thanh toán lãi cho khoản vay cho tháng hoặc năm cụ thể
Mặc dù số tiền trả hàng tháng hoặc hàng năm trong thời hạn vay sẽ như nhau, nhưng tỷ lệ tiền lãi và vốn mà bạn trả mỗi kỳ thay đổi theo thời hạn. Khi bắt đầu khoản vay, bạn phải trả phần lớn tiền lãi và một ít vốn, nhưng đến cuối kỳ hạn, bạn phải trả một ít tiền lãi và chủ yếu là vốn.
Đối với mỗi kỳ hạn của khoản vay, bạn có thể tính số tiền lãi bằng cách sử dụngchức năng IPMT.
Giới thiệu về chức năng IPMT
Mục tiêu chức năng:
tính toántrả lãitrong một khoảng thời gian nhất định như một tháng hoặc năm cụ thể.
Cú pháp:
=IPMT(tỷ lệ, per, nper, pv, [fv], [type])
Giải thích đối số:
| Lý lẽ | Bắt buộc/Tùy chọn | Giải trình |
|---|---|---|
| Tỷ lệ | Yêu cầu | Lãi suất mỗi kỳ. |
| Mỗi | Yêu cầu | Khoảng thời gian mà bạn muốn tìm lãi suất. Nó phải nằm trong khoảng từ 1 đến Nper |
| Ví dụ | Yêu cầu | Tổng số kỳ thanh toán trong một niên kim. |
| pv | Yêu cầu | Giá trị hiện tại, hoặc tổng số tiền mà một loạt các khoản thanh toán trong tương lai có giá trị hiện tại. Còn được gọi là hiệu trưởng. |
| Fv | Không bắt buộc | Giá trị tương lai hoặc số dư tiền mặt bạn muốn đạt được sau lần thanh toán cuối cùng được thực hiện. Nếu chúng ta không chèn giá trị cho fv, nó sẽ được coi là 0 (ví dụ: giá trị tương lai của khoản vay là 0). |
| Kiểu | Không bắt buộc | Số 0 hoặc 1. Nó cho biết thời điểm đến hạn thanh toán. Nếu loại bị bỏ qua, nó được coi là 0. |
Tham số trả về:
Thanh toán lãi trong một khoảng thời gian nhất định cho một khoản đầu tư dựa trên các khoản thanh toán định kỳ, cố định và lãi suất không đổi.
Bước 1:
- Bước đầu tiên của chúng ta là chọn một ô và viết công thức củaIPMT. Chúng tôi sẽ chọn ôQ10và viết ra công thức sau.
=IPMT(C4/12, 1, C7, C8)
Phân tích công thức:
- C4= Tỷ lệ (Đối số đầu tiên) = Lãi suất hàng năm =4%
Khi chúng tôi đang tính thanh toán lãi cho một tháng, chúng tôi đã chia nó cho số tháng trong một năm,12.
- 1= Pr(Đối số thứ hai) = Khoảng thời gian mà bạn muốn tìm lãi =1
Chúng tôi đang tính số tiền lãi chotháng đầu tiên. Kể từ đâytrước=1
- C7=Nper(Đối số thứ ba) = Tổng số lần thanh toán = 60
- C8=Pv(Luận điểm thứ tư)= Tổng số tiền cho vay hoặc Tiền gốc = $5.000
Bước 2:
- Khi nhấp vàoĐI VÀO, ta sẽ được số tiền lãi phải trả trong tháng đầu tiên.
Bước 3:
- Chúng tôi cũng có thể tính toán các khoản thanh toán lãi chomột năm cụ thể. Để tính số tiền lãi cho năm vừa qua, chúng ta phải sử dụng công thức sau.
=IPMT(F4, 5, F6, F8)
- Như chúng ta có thể thấy, chúng ta không cần phải chialãi suất hàng nămbằng 12. Chúng tôi đang tính toán số tiền trong một năm. Vàtrướchoặc khoảng thời gian mà chúng tôi muốn tìm tiền lãi bây giờ là5vì chúng tôi đang tính toán số tiền lãi cho lần cuối cùng hoặc5quần quènăm.Tổng thời gian (F6)cũng là 5 vì tổng thời gian của chúng ta là 5 năm. Hình ảnh dưới đây cho thấy số tiền lãi cố định hàng năm phải trả trong lần cuối hoặc5quần quènăm.
- Chúng tôi cũng có thể tính toán các khoản thanh toán lãi hàng tuần, hàng quý và nửa năm bằng cách sử dụngIPMT.
Đọc thêm:Cách tính lãi trong Excel với các khoản thanh toán (3 ví dụ)
3. Tính Thanh toán vốn cho Tháng hoặc Năm cụ thể trong Excel
Chúng tôi cũng có thể tính toán khoản thanh toán vốn cho một tháng hoặc năm cụ thể bằng cách sử dụngchức năng PPMTcủa Excel.
Giới thiệu về chức năng PPMT
Mục tiêu chức năng:
tính toánthanh toán vốntrong một khoảng thời gian nhất định như một tháng hoặc năm cụ thể.
Cú pháp:
=PPMT(tỷ lệ, mỗi, nper, pv, [fv], [loại])
Giải thích đối số:
| Lý lẽ | Bắt buộc/Tùy chọn | Giải trình |
|---|---|---|
| Tỷ lệ | Yêu cầu | Lãi suất mỗi kỳ. |
| Mỗi | Yêu cầu | Khoảng thời gian mà bạn muốn tìm lãi suất phải nằm trong khoảng từ 1 đến Nper |
| Ví dụ | Yêu cầu | Tổng số kỳ thanh toán trong một niên kim. |
| pv | Yêu cầu | Giá trị hiện tại, hoặc tổng số tiền mà một loạt các khoản thanh toán trong tương lai có giá trị hiện tại. Còn được gọi là hiệu trưởng. |
| Fv | Không bắt buộc | Giá trị tương lai hoặc số dư tiền mặt bạn muốn đạt được sau lần thanh toán cuối cùng được thực hiện. Nếu chúng ta không chèn giá trị cho fv, nó sẽ được coi là 0 (ví dụ: giá trị tương lai của khoản vay là 0). |
| Kiểu | Không bắt buộc | Số 0 hoặc 1. Nó cho biết thời điểm đến hạn thanh toán. Nếu loại bị bỏ qua, nó được coi là 0. |
Tham số trả về:
Việc thanh toán tiền gốc trong một khoảng thời gian nhất định cho một khoản đầu tư dựa trên các khoản thanh toán định kỳ, không đổi và lãi suất không đổi.
Bước 1:
- Chúng tôi sẽ chọn ôQ10và viết ra công thức sau đây củaPPMT.
=PPMT(C4/12, 1, C7, C8)
Phân tích công thức:
- C4 =Tỷ lệ (Đối số đầu tiên)= Lãi suất hàng năm =4%
Khi chúng tôi đang tính thanh toán lãi cho một tháng, chúng tôi đã chia nó cho số tháng trong một năm,12.
- 1 =Pr(Đối số thứ hai)= Khoảng thời gian mà bạn muốn tìm lãi =1
Chúng tôi đang tính số tiền lãi chotháng đầu tiên. Do đó Pr =1
- C7 =Nper(Đối số thứ ba)= Tổng số lần thanh toán =60
- C8 =Pv(Luận điểm thứ tư)= Tổng số tiền vay hoặc Tiền gốc =5.000 USD
Bước 2:
- Khi nhấp vàoĐI VÀO, ta sẽ được số vốn phải trả trong tháng đầu tiên.
Bước 3:
- Chúng tôi cũng có thể tính toán các khoản thanh toán vốn chomột năm cụ thể. Để tính số vốn cho năm vừa qua, chúng ta phải sử dụng công thức sau.
=PPMT(F4, 5, F6, F8)
- Như chúng ta có thể thấy, chúng ta không cần phải chialãi suất hàng nămbằng 12. Chúng tôi đang tính toán số tiền trong một năm. Vàtrướchoặc khoảng thời gian mà chúng tôi muốn tìm tiền lãi bây giờ là5vì chúng tôi đang tính toán số vốn cho lần cuối cùng hoặc5quần quènăm.Tổng thời gian (F6)cũng là 5 vì tổng thời gian của chúng ta là 5 năm. Hình ảnh dưới đây cho thấy số tiền vốn hàng năm phải trả trong lần cuối cùng hoặc5quần quènăm.
Lưu ý: Tổng số tiền trả lãi và trả vốn sẽ bằng với số tiền trả cố định mà chúng ta đã tính ở phương pháp đầu tiên.
- Thanh toán tiền lãi cho tháng đầu tiên = $16,67 [Sử dụng chức năng IPMT]
Thanh toán vốn cho tháng đầu tiên = $75,42 [Sử dụng chức năng PPMT]
Tổng số tiền thanh toán cho tháng đầu tiên = 16,67+75,42 = 92,09 = Tổng số tiền phải trả cho mỗi tháng mà chúng tôi đã tính toán bằng cách sử dụngChức năng PMT trong phương pháp 1
Vì vậy, tổng số tiền phải trả sẽ bằng nhau cho mỗi khoảng thời gian giống hệt nhau. Nhưng số tiền lãi và số tiền vốn sẽ thay đổi theo từng thời kỳ.
- Chúng ta cũng có thể tính toán các khoản thanh toán vốn hàng tuần, hàng quý và nửa năm bằng cách sử dụngPPMT.
Đọc thêm:Tạo Công cụ tính lãi thanh toán trễ trong Excel và tải xuống miễn phí
bài đọc tương tự
- Cách tạo Công cụ tính tiền lãi GST trong Excel (với các bước hữu ích)
- Cách tạo Máy tính thanh toán HELOC bằng tiền gốc và tiền lãi trong Excel
- Máy tính lãi vay hàng ngày trong Excel (Tải xuống miễn phí)
- Cách tính lãi vay mua nhà trên Excel (2 cách dễ dàng)
- Cách tạo Công cụ tính lãi định kiến trong Excel
4. Xác định lãi vay tích lũy cho tháng hoặc năm cụ thể trong Excel
Bạn có thể dùngchức năng CUMIPMTđể tính lãi tích lũy cho một khoản vay trong Excel trong một khoảng thời gian cụ thể như một tháng hoặc một năm cụ thể.
Giới thiệu về Chức năng CUMIPMT
Mục tiêu chức năng:
Tiền lãi tích lũy được trả cho khoản vay từ start_period đến end_period.
Cú pháp:
=CUMIPMT(tỷ lệ, nper, pv, start_period, end_period, [type])
Giải thích đối số:
| Lý lẽ | Bắt buộc/Tùy chọn | Giải trình |
|---|---|---|
| Tỷ lệ | Yêu cầu | Lãi suất mỗi kỳ. |
| Ví dụ | Yêu cầu | Tổng số kỳ thanh toán trong một niên kim. |
| pv | Yêu cầu | Giá trị hiện tại, hoặc số tiền gộp mà một loạt các khoản thanh toán trong tương lai có giá trị ngay bây giờ. |
| Start_period | Yêu cầu | Giai đoạn đầu tiên trong tính toán. Các kỳ thanh toán được đánh số bắt đầu bằng 1. |
| End_period | Yêu cầu | Giai đoạn cuối cùng trong tính toán. |
| Kiểu | Yêu cầu | Số 0 hoặc 1. Nó cho biết thời điểm đến hạn thanh toán. Nếu loại bị bỏ qua, nó được coi là 0. |
Tham số trả về:
Tiền lãi tích lũy được trả cho một khoản vay giữa một khoảng thời gian cụ thể.
Bước 1:
- Đầu tiên, chúng ta sẽ chọn ôQ10và viết ra công thức dưới đây để tính tiền lãi cộng dồn cho tháng đầu tiên.
=CUMIPMT(C4/12, C7, C8, 1, 1, 0)
Phân tích công thức:
- C4 =Tỷ lệ (Đối số đầu tiên)= Lãi suất hàng năm =4%
Khi chúng tôi đang tính tiền lãi tích lũy trong một tháng, chúng tôi đã chia nó cho số tháng trong một năm,12.
- C7 =Nper (Đối số thứ hai)= Tổng số lần thanh toán =60
Chúng tôi có 5 năm để trả lại khoản vay. 5 năm có tổng cộng là (5X12) =60 tháng
- C8 =Pv(Đối số thứ ba)= Tổng số tiền vay hoặc Tiền gốc =5.000 USD
- 1 =Start_period(Đối số thứ tư)VàEnd_period(Đối số thứ năm)= Chúng tôi đang tính tiền lãi tích lũy cho tháng đầu tiên. Do đó, khoảng thời gian bắt đầu và kết thúc của chúng ta là1.
- 0 =Loại(Đối số thứ sáu)= Thanh toán vào cuối kỳ.
Bước 2:
Sau đó, chúng ta sẽ nhấnĐI VÀOvà chúng tôi sẽ nhận được số tiền lãi tích lũy cho tháng đầu tiên.
Bước 3:
- Chúng ta cũng có thể tính lãi tích lũy chomột năm cụ thể. Để tính số tiền lãi lũy kế phải trả trongcuối cùng hoặc 5quần quènăm, chúng ta phải sử dụng công thức sau.
=CUMIPMT(F4/12, F7, F8, 49,60, 0)
- Các đối số gần giống với các đối số được sử dụng để tính lãi lũy kế cho tháng đầu tiên trừ kỳ đầu và kỳ cuối. Cácstarted_periodlà49khi năm cuối cùng hoặc năm thứ năm bắt đầu sau 4quần quènăm hay (4X12) = 48 tháng và kết thúc sau (5X12) = 60 tháng. NênEnd_periodlà60. Hình ảnh dưới đây cho thấy số tiền hoàn trả cố định hàng năm.
Đọc thêm:Cách Sử dụng Công thức Lãi suất Tích lũy trong Excel (3 Cách Dễ dàng)
5. Tính lãi kép cho khoản vay bằng hàm FV
Bạn cũng có thể tính lãi gộp cho khoản vay trong Excel bằng cách sử dụngChức năng FV.
Giới thiệu về Chức năng FV
Mục tiêu chức năng:
Tính toán giá trị tương lai của một khoản đầu tư dựa trên lãi suất cố định. Bạn có thể sử dụng FV với các khoản thanh toán định kỳ, liên tục hoặc thanh toán một lần.
Cú pháp:
FV(tỷ lệ,nper,pmt,[pv],[type])
Giải thích đối số:
| Lý lẽ | Bắt buộc/Tùy chọn | Giải trình |
|---|---|---|
| Tỷ lệ | Yêu cầu | Lãi suất mỗi kỳ. |
| Ví dụ | Yêu cầu | Tổng số kỳ thanh toán trong một niên kim. |
| Pmt | Yêu cầu | Các khoản thanh toán sẽ được thực hiện mỗi kỳ. Nó là cố định hoặc không đổi trong suốt thời hạn của khoản vay hoặc thế chấp. Thông thường, pmt chỉ chứa tiền gốc và tiền lãi nhưng không có phí hoặc thuế. Nếu pmt bị bỏ qua, bạn phải bao gồm đối số pv. |
| pv | Không bắt buộc | Giá trị hiện tại, hoặc tổng số tiền mà một loạt các khoản thanh toán trong tương lai có giá trị hiện tại. Còn được gọi là hiệu trưởng. |
| Kiểu | Yêu cầu | Số 0 hoặc 1. Nó cho biết thời điểm đến hạn thanh toán. Nếu loại bị bỏ qua, nó được coi là 0. |
Tham số trả về:
Giá trị tương lai.
Bước 1:
- Đầu tiên, chúng ta sẽ chọn ôQ10và viết ra công thức dưới đây để tính lãi kép cho tháng đầu tiên.
=FV(C4/12, C7, 0, -C8)
Phân tích công thức:
- C4 =Tỷ lệ (Đối số đầu tiên)= Lãi suất hàng năm =4%
Khi chúng tôi đang tính toán trên cơ sở hàng tháng, chúng tôi đã chia nó cho số tháng trong một năm,12.
- C7 =Npr(Đối số thứ hai)= Tổng số lần thanh toán =60
Chúng tôi có 5 năm để trả lại khoản vay. 5 năm có tổng cộng là (5X12) =60 tháng
- 0 =Pmt(Đối số thứ ba)= Khoản thanh toán được thực hiện mỗi kỳ.
- -C8 =Pv(Luận điểm thứ tư)= Giá trị hiện tại.
Bước 2:
- Sau đó, khi nhấp vàoĐI VÀO, chúng ta sẽ nhận được tiền lãi gộp trong kỳ.
Đọc thêm:Cách tính tiền lãi tích lũy cho khoản tiền gửi có kỳ hạn trong Excel (3 phương pháp)
Những điều cần ghi nhớ
- Loại đối số trong các hàm này thường làkhông bắt buộc. Số 0 hoặc 1 cho biết thời điểm đến hạn thanh toán. Nếu loại bị bỏ qua, nó được coi là 0.
- Đặt đối số Loại thành 0 nếu các khoản thanh toán đến hạn vào cuối kỳ. Đặt đối số Loại thành 1 nếu các khoản thanh toán đến hạn vào đầu kỳ.
Phần kết luận
Trong bài viết này, chúng ta đã học cách tính lãi cho một khoản vay trong Excel. Chúng tôi đã học cách tính tổng số tiền trả cố định cho mỗi kỳ, trả lãi và vốn cho một khoảng thời gian cụ thể, trả lãi gộp và tích lũy cho một tháng hoặc năm cụ thể bằng cách sử dụng các hàm nhưPMT, IPMT, PPMT, CUMIPMT và FVcác hàm trong Excel. Tôi hy vọng từ giờ trở đi, bạn sẽ thấy việc tính lãi cho các khoản vay của mình trong Excel rất dễ dàng. Nếu bạn có bất kỳ câu hỏi hoặc đề xuất nào về bài viết này, vui lòng để lại nhận xét bên dưới. Có một ngày tuyệt vời!!!
Những bài viết liên quan
- Cách tạo Công cụ tính lãi suất SIP trong Excel (với các bước đơn giản)
- Cách tạo Công cụ tính lãi suất TDS trong Excel
- Tính lãi trong quá trình xây dựng trong Excel
- Máy tính khoản vay mua ô tô trong Bảng tính Excel – Tải xuống mẫu miễn phí
- Cách tính lãi thẻ tín dụng trong Excel (3 bước đơn giản)
- Công cụ tính lãi suất ngân hàng trong Bảng tính Excel – Tải xuống mẫu miễn phí