Cách tính khoản phải trả, tiền gốc, tiền lãi bằng hàm PMT, PPMT, IPMT trong Excel

Đỗ Thúy Quỳnh
Đỗ Thúy Quỳnh
Dec 25 2020

Nếu bạn đang có một tập hợp gồm vô vàn bảng danh sách cảc khoản vay cần tính khoản phải trả, tiền gốc, tiền lãi theo rất nhiều thời hạn cùng lãi suất khác nhau, mà chỉ sử dụng thao tác thủ công sẽ rất dễ khiến bạn bị nhầm lẫn, xuất hiện sai sót. Trong bài này Gitiho sẽ giới thiệu cho bạn giải pháp nhanh và dễ nhất để bạn giải quyết vấn đề này. Không chỉ áp dụng cho các khoản vay, còn dùng được cho các khoản đầu tư lẫn tiền gửi ngân hàng,...Để biết cách tính như nào ta cùng xem ví dụ minh họa chi tiết bên dưới nhé.

Cách tính khoản phải trả, tiền gốc, tiền lãi bằng hàm PMT, PPMT, IPMT trong Excel

Hàm PMT, IPMT, PPMT là gì?

PMT, IPMT, PPMT là một trong số những hàm tài chính thường được sử dụng nhất và có vai trò quan trọng trong tính toán liên quan tài chính.

Chúng ta có một ví dụ điển hình cho tác dụng của hàm PMT là tính số tiền vay phải trả dựa trên các khoản thanh toán cố định và lãi suất không đổi. Trong bài này Gitiho sẽ hướng dẫn chi tiết cho bạn qua các ví dụ dễ hiểu và dễ làm theo.

Trở thành bậc thầy Excel trong 16 giờ cùng Tuyệt Đỉnh Excel

Ví dụ về dùng hàm PMT

Cùng xem xét một khoản vay với lãi suất hàng năm là 6%, thời hạn 20 năm. Giá trị hiện tại là 150.000 đô la (số tiền đã vay) và giá trị tương lai là 0 (nghĩa là khi đã trả hết khoản vay).

1. Dùng hàm PMT tính khoản phải trả hàng năm.

Ta điền công thức sau vào ô trống cần trả kết quả.

=PMT(B2,C2,D2,E2)

hàm PMT, PPMT, IPMT trong Excel

Chú ý: Nếu như đối số thứ 5 trong công thức bị bỏ qua thì có nghĩa là các khoản thanh toán đến hạn vào cuối kỳ. Chúng ta cần trả khoản vay là 150 nghìn đô la (giá trị dương) và thanh toán hàng năm một khoản là 13,077.68 đô la (giá trị âm).

2. Dùng hàm PMT tính khoản phải trả hàng quý.

Ta vẫn điền công thức tương tự vào ô trống cần trả kết quả.

=PMT(B2,C2,D2,E2)

Tuy nhiên điểm khác biệt là ta đã thay đổi lãi suất xuống còn 1,50% và Nper là 80. Giống như trong hình dưới đây.

hàm PMT, PPMT, IPMT trong Excel

Chú ý:

  • Để tính khoản phải trả hàng quý, ta cần chia lãi suất năm thành 4 phần tức là 6% / 4 = 1,5% là lãi suất hàng quý.
  • Tổng số kỳ Nper là 20*4=80.

3. Dùng hàm PMT tính khoản phải trả hàng tháng.

Tương tự như trên ta vẫn dùng công thức =PMT(B2,C2,D2,E2). Tuy nhiên cần thay đổi thông số của lãi suất hàng năm và Nper. Như hình minh họa dưới đây.

hàm PMT, PPMT, IPMT trong Excel

Chú ý:

  • Để tính khoản phải trả hàng tháng, ta sẽ sử dụng lãi suất là 6% / 12 = 0,5%.
  • Tổng số kỳ là 20*12=240 để điền vào Nper.

Do đó ta có thể thấy được vai trò của hàm PMT cực kỳ hữu ích và giúp ta tiết kiệm được rất nhiều thời gian.

4. Dùng hàm PMT tính khoản tiền gửi hàng năm.

Với hàm PMT, ta hoàn toàn có thể giải quyết vấn đề sau một cách dễ dàng: Có một khoản đầu tư có lãi suất hàng năm là 8% và giá trị hiện tại là 0. Ta nên gửi bao nhiêu tiền vào mỗi cuối năm để có 1,448.66 đô la trong tài khoản trong 10 năm?

Ta xem hình minh họa dưới đây.

hàm PMT, PPMT, IPMT trong Excel

Giải thích: Trong thời gian 10 năm, bạn phải trả 10*100=1000 đô la và bạn sẽ nhận được 1,448.66 đô la sau 10 năm. Lãi suất càng cao, tiền của bạn lại càng tăng nhanh và nhiều.

5. Dùng hàm PMT tính khoản tiền rút hàng tháng.

Cùng xem xét một vấn đề rất phổ biến như sau: ta có một khoản tiền có lãi suất hàng năm là 6% và giá trị hiện tại của nó là 83,748.46 đô la (có thể sử dụng). Ta có thể rút được bao nhiêu tiền vào cuối mỗi tháng trong 20 năm tới?

Ta xem hình minh họa bên dưới đây.

hàm PMT, PPMT, IPMT trong Excel

Giải thích: 

  • Ta sẽ trả một lần là 83,748.46 đô la.
  • Nhận được 240*600=144.000 đô la trong tương lai.
  • Đây là một ví dụ cho thấy tiền có thể sinh sôi theo thời gian.

Ví dụ về dùng hàm PPMT và IPMT

Cùng xem xét một khoản vay với lãi suất hàng năm là 5% có thời hạn 2 năm và giá trị hiện tại là 20 nghìn đô la (tức là số tiền đã vay).

1. Dùng hàm PMT để tính số tiền phải trả hàng tháng.

hàm PMT, PPMT, IPMT trong Excel

Chú ý: Để tính số tiền phải trả hàng tháng ta cần chỉnh lãi suất thành 5% / 12 = 0,42% và tổng số kỳ là 2*12=24 ghi vào Nper.

2. Dùng hàm PPMT để tính tiền gốc .

Ta xem hình dưới đây. Điền công thức vào ô trống cần trả kết quả.

=PPMT(B2,5C2,D2,E2)

hàm PMT, PPMT, IPMT trong Excel

Giải thích: Hàm PPMT ở trên tính cho ta số tiền gốc phải trả trong lần thanh toán thứ 5.

3. Dùng hàm IPMT để tính tiền lãi.

Hàm IPMT trong Excel sẽ giúp ta tính được phần lãi phải trả. Trong công thức đối số thứ 2 xác định số lần trả.

=IPMT(B2,5,C2,D2,E2)

hàm PMT, PPMT, IPMT trong Excel
 

Giải thích: Hàm IPMT trong công thức trên giúp ta tính phần lãi của lần thanh toán thứ 5.

4. Tạo lịch trả dần cho khoản vay.

Cần phải mất 24 tháng để trả hết khoản vay này. Ta có thể tạo một lịch chi trả dần cho khoản vay như hình bên dưới để thấy rõ phần gốc tăng và phần lãi giảm theo từng lần thanh toán.

hàm PMT, PPMT, IPMT trong Excel

Chú ý: phần gốc lẫn phần lãi sẽ luôn cộng dồn vào số tiền phải trả.

Như vậy chúng ta đã biết được tác dụng và cách sử dụng của các hàm tài chính PMT, PPMT và IPMT rồi.

Để trở thành cao thủ Excel hãy xem thêm.

Kết luận

Trên đây là cách tính khoản phải trả tiền gốc, tiền lãi bằng hàm PMT, PPMT, IPMT trong Excel. Để học thêm vào các hàm chuyên sâu trong Excel bạn hãy đăng ký ngay Khóa học Thành thạo HÀM trong Excel cho công việc của chúng mình nhé. Đây là khóa học giúp bạn thành thạo 75+ Hàm và Công thức trong Excel để tăng hiệu quả sử dụng Excel cho công việc hằng ngày, tiết kiệm tới 35% thời gian làm việc. Khóa học này sẽ cung cấp cho bạn sự hiểu biết sâu sắc về các công thức và hàm nâng cao của Excel giúp biến Excel từ một chương trình bảng tính cơ bản thành một công cụ phân tích năng động và mạnh mẽ. Đăng ký Khóa học Thành thạo HÀM trong Excel ngay hôm nay để thành thạo các hàm trong Excel bạn nhé.


 

 

 

 

 

0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

Bài viết liên quan

Hướng dẫn về một số hàm DAX căn bản trong Power Pivot (Phần 2)

Hướng dẫn về một số hàm DAX căn bản trong Power Pivot (Phần 2)

Hướng dẫn cách phân biệt Data Table với Lookup Table trong Power Pivot

Hướng dẫn cách phân biệt Data Table với Lookup Table trong Power Pivot

Hướng dẫn cách mở thẻ Power Pivot trên thanh Menu của Excel

Hướng dẫn cách mở thẻ Power Pivot trên thanh Menu của Excel

Hướng dẫn cách phân biệt giữa Power Pivot và Pivot Table

Hướng dẫn cách phân biệt giữa Power Pivot và Pivot Table

Hướng dẫn cách kết nối các bảng trong Power Pivot

Hướng dẫn cách kết nối các bảng trong Power Pivot

Hướng dẫn cách đổi tên bảng và tên cột trong Power Pivot

Hướng dẫn cách đổi tên bảng và tên cột trong Power Pivot

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội