Hướng dẫn cho thấy cách sử dụng chức năng PMT trong Excel để tính toán các khoản thanh toán cho khoản vay hoặc đầu tư dựa trên lãi suất, số lượng thanh toán và tổng số tiền cho vay.
Trước khi bạn vay tiền, thật tốt khi biết một khoản vay hoạt động như thế nào. Nhờ các chức năng tài chính của Excel như PMT, PPMT và IPMT, việc tính toán hàng tháng hoặc bất kỳ khoản thanh toán định kỳ nào khác cho khoản vay rất dễ dàng. Trong hướng dẫn này, chúng ta sẽ xem xét kỹ hơn về hàm PMT, thảo luận chi tiết cú pháp của nó và chỉ ra cách xây dựng máy tính PMT của riêng bạn trong Excel.
Hàm PMT trong Excel là gì?
Hàm PMT Excel là một hàm tài chính tính toán khoản thanh toán cho khoản vay dựa trên lãi suất không đổi, số kỳ và số tiền cho vay.
“PMT” là viết tắt của “thanh toán”, do đó tên của hàm.
Ví dụ: nếu bạn đang đăng ký khoản vay mua ô tô hai năm với lãi suất hàng năm là 7% và số tiền vay là 30.000 đô la, một công thức PMT có thể cho bạn biết khoản thanh toán hàng tháng của bạn sẽ là bao nhiêu.
Để chức năng PMT hoạt động chính xác trong trang tính của bạn, vui lòng ghi nhớ những sự thật sau:
- Để phù hợp với mô hình dòng tiền chung, số tiền thanh toán là đầu ra dưới dạng số âm bởi vì đó là một dòng tiền
- Giá trị được trả về bởi hàm PMT bao gồm hiệu trưởng và quan tâm nhưng không bao gồm bất kỳ khoản phí, thuế hoặc thanh toán dự trữ nào có thể liên quan đến khoản vay.
- Công thức PMT trong Excel có thể tính toán khoản thanh toán cho vay cho các tần suất thanh toán khác nhau, chẳng hạn như hàng tuần, hàng tháng, hàng quý, hoặc là hàng năm. Ví dụ này cho thấy làm thế nào để làm điều đó một cách chính xác.
Hàm PMT có sẵn trong Excel cho Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 và Excel 2007.
Hàm PMT Excel – cú pháp và cách sử dụng cơ bản
Hàm PMT có các đối số sau:
PMT (tỷ lệ, nper, pv, [fv], [type])
Ở đâu:
- Tỷ lệ (bắt buộc) – lãi suất không đổi mỗi kỳ. Có thể được cung cấp dưới dạng phần trăm hoặc số thập phân.
Ví dụ: nếu bạn thực hiện hàng năm thanh toán cho khoản vay với lãi suất hàng năm là 10 phần trăm, sử dụng 10% hoặc 0,1 cho lãi suất. Nếu bạn thực hiện hàng tháng thanh toán cho cùng một khoản vay, sau đó sử dụng 10% / 12 hoặc 0,00833 cho tỷ lệ.
- Thần (bắt buộc) – số lần thanh toán cho khoản vay, tức là tổng số kỳ mà khoản vay phải được trả.
Ví dụ: nếu bạn thực hiện hàng năm thanh toán cho khoản vay 5 năm, cung cấp 5 cho nper. Nếu bạn thực hiện hàng tháng thanh toán cho cùng một khoản vay, sau đó nhân số năm với 12 và sử dụng 5 * 12 hoặc 60 cho nper.
- Pv (bắt buộc) – giá trị hiện tại, tức là tổng số tiền mà tất cả các khoản thanh toán trong tương lai có giá trị ngay bây giờ. Trong trường hợp cho vay, nó chỉ đơn giản là số tiền ban đầu đã vay.
- Fv (tùy chọn) – giá trị tương lai hoặc số dư tiền mặt bạn muốn có sau lần thanh toán cuối cùng được thực hiện. Nếu bỏ qua, giá trị tương lai của khoản vay được giả định là 0 (0).
- Kiểu (tùy chọn) – chỉ định khi đến hạn thanh toán:
- 0 hoặc bị bỏ qua – các khoản thanh toán đáo hạn vào cuối mỗi kỳ.
- 1 – các khoản thanh toán đến hạn vào đầu mỗi kỳ.
Ví dụ: nếu bạn vay 100.000 đô la trong 5 năm với lãi suất hàng năm là 7%, công thức sau đây sẽ tính toán Thanh toán hàng năm:
=PMT(7%, 5, 100000)
Để tìm thanh toán hàng tháng cho cùng một khoản vay, sử dụng công thức này:
=PMT(7%/12, 5*12, 100000)
Hoặc, bạn có thể nhập các thành phần đã biết của khoản vay vào các ô riêng biệt và tham chiếu các ô đó trong công thức PMT của mình. Với lãi suất trong B1, không. của năm trong B2 và số tiền cho vay trong B3, công thức đơn giản như sau:
=PMT(B1, B2, B3)
Xin nhớ rằng thanh toán được trả lại dưới dạng số âm bởi vì số tiền này sẽ được ghi nợ (trừ) từ tài khoản ngân hàng của bạn.
Theo mặc định, Excel hiển thị kết quả trong Tiền tệ định dạng, được làm tròn đến 2 vị trí thập phân, được tô sáng màu đỏ và được đặt trong ngoặc đơn, như được hiển thị ở phần bên trái của hình ảnh bên dưới. Hình ảnh bên phải cho thấy kết quả tương tự trong Chung định dạng.
Nếu bạn muốn có khoản thanh toán dưới dạng tích cực con số, đặt dấu trừ trước toàn bộ công thức PMT hoặc pv đối số (số tiền cho vay):
=-PMT(B1, B2, B3)
hoặc là
=PMT(B1, B2, -B3)
Tiền boa. Để tính toán tổng cộng trả cho khoản vay, nhân giá trị PMT trả về với số kỳ (giá trị nper). Trong trường hợp của chúng tôi, chúng tôi sẽ sử dụng phương trình này: 24.389,07 * 5 và thấy rằng tổng số tiền bằng $ 121,945,35.
Cách sử dụng hàm PMT trong Excel – ví dụ về công thức
Dưới đây bạn sẽ tìm thấy một vài ví dụ khác về công thức PMT Excel cho thấy cách tính các khoản thanh toán định kỳ khác nhau cho khoản vay mua ô tô, vay mua nhà, vay thế chấp và tương tự.
Dạng đầy đủ của hàm PMT trong Excel
Đối với hầu hết các phần, bạn có thể bỏ qua hai đối số cuối cùng trong công thức PMT của mình (giống như chúng tôi đã làm trong các ví dụ trên) vì các giá trị mặc định của chúng bao gồm các trường hợp sử dụng điển hình nhất:
- Fv bỏ qua – ngụ ý số dư bằng không sau lần thanh toán cuối cùng.
- Kiểu bỏ qua – các khoản thanh toán đến hạn tại kết thúc của từng thời kỳ.
Nếu điều kiện cho vay của bạn khác với mặc định, thì hãy sử dụng hình thức PMT đầy đủ.
Ví dụ: hãy tính số lượng thanh toán hàng năm dựa trên các ô nhập này:
- B1 – lãi suất hàng năm
- B2 – thời hạn cho vay (tính bằng năm)
- B3 – số tiền vay
- B4 – giá trị tương lai (số dư sau lần thanh toán cuối cùng)
- B5 – loại niên kim:
- 0 (niên kim thường xuyên) – thanh toán được thực hiện vào cuối mỗi năm.
- 1 (niên kim do) – thanh toán được thực hiện vào đầu kỳ, ví dụ: thanh toán tiền thuê hoặc cho thuê.
Cung cấp các tham chiếu này cho công thức PMT Excel của bạn:
=PMT(B1, B2, B3, B4, B5)
Và bạn sẽ có kết quả này:
Tính toán các khoản thanh toán hàng tuần, hàng tháng, hàng quý và nửa năm
Tùy thuộc vào tần suất thanh toán, bạn cần sử dụng các tính toán sau cho tỷ lệ và nper tranh luận:
- Dành cho tỷ lệ, chia lãi suất hàng năm cho số lần thanh toán mỗi năm (được coi là bằng với số kỳ hạn gộp).
- Dành cho nper, nhân số năm với số lần thanh toán mỗi năm.
Bảng dưới đây cung cấp các chi tiết:
| Phương Thức Thanh Toán | Tỷ lệ | Thần |
| Hàng tuần | lãi suất hàng năm / 52 | năm * 52 |
| Hàng tháng | lãi suất hàng năm / 12 | năm * 12 |
| hàng quý | lãi suất hàng năm / 4 | năm * 4 |
| Bán niên | lãi suất hàng năm / 2 | năm * 2 |
Chẳng hạn, để tìm số tiền thanh toán định kỳ cho khoản vay 5.000 đô la với lãi suất 8% hàng năm và thời gian 3 năm, hãy sử dụng một trong các công thức dưới đây.
Hàng tuần thanh toán:
=PMT(8%/52, 3*52, 5000)
Hàng tháng thanh toán:
=PMT(8%/12, 3*12, 5000)
hàng quý thanh toán:
=PMT(8%/4, 3*4, 5000)
Bán niên thanh toán:
=PMT(8%/2, 3*2, 5000)
Trong mọi trường hợp, số dư sau lần thanh toán cuối cùng được giả định là 0 đô la và các khoản thanh toán đáo hạn vào cuối mỗi kỳ.
Ảnh chụp màn hình bên dưới hiển thị kết quả của các công thức này:
Cách tạo máy tính PMT trong Excel
Trước khi bạn tiếp tục và vay tiền, lý do là so sánh các điều kiện cho vay khác nhau để tìm ra các lựa chọn phù hợp với bạn nhất. Đối với điều này, hãy tạo máy tính thanh toán khoản vay Excel của riêng chúng tôi.
- Để bắt đầu, hãy nhập số tiền cho vay, lãi suất và thời hạn cho vay trong các ô riêng biệt (B3, B4, B5, tương ứng).
- Để có thể chọn các giai đoạn khác nhau và chỉ định thời điểm thanh toán đến hạn, tạo danh sách thả xuống với các tùy chọn được xác định trước (B6 và B7) sau đây:
- Thiết lập bảng tra cứu cho Chu kỳ (E2: F6) và Hạn thanh toán (E8: F9) như hiển thị trong ảnh chụp màn hình bên dưới. Điều quan trọng là các nhãn văn bản trong các bảng tra cứu khớp chính xác với các mục của danh sách thả xuống tương ứng.
Trong các ô bên cạnh danh sách thả xuống, hãy nhập như sau VLOOKUP IFERROR các công thức sẽ kéo số từ bảng tra cứu tương ứng với mục được chọn trong danh sách thả xuống.Công thức cho Chu kỳ (C 6):
=IFERROR(VLOOKUP(B6, E2:F6, 2, 0), "")Công thức cho Hạn thanh toán (C7):
=IFERROR(VLOOKUP(B7, E8:F9, 2, 0), "")
- Viết công thức PMT để tính toán thanh toán định kỳ dựa trên các ô của bạn. Trong trường hợp của chúng tôi, công thức như sau:
=IFERROR(-PMT(B4/C6, B5*C6, B3, 0, C7), "")Xin lưu ý những điều sau đây:
- Các fv đối số (0) được mã hóa cứng trong công thức vì chúng tôi luôn muốn số dư bằng không sau lần thanh toán cuối cùng. Trong trường hợp bạn muốn cho phép người dùng của mình nhập bất kỳ giá trị nào trong tương lai, hãy phân bổ một ô nhập riêng cho đối số fv.
- Hàm PMT có trước dấu trừ để hiển thị kết quả dưới dạng số dương.
- Hàm PMT được gói vào IFERROR để ẩn lỗi khi một số giá trị đầu vào không được xác định.
Công thức trên đi vào B9. Và trong ô lân cận (A9), chúng tôi hiển thị nhãn tương ứng với khoảng thời gian đã chọn (B6). Đối với điều này, chỉ cần ghép giá trị trong B6 và văn bản mong muốn:
=B6&" Payment"
- Cuối cùng, bạn có thể ẩn giấu các bảng tra cứu từ chế độ xem, thêm một vài lần chạm định dạng hoàn thiện và máy tính Excel PMT của bạn rất phù hợp:
Hàm PMT Excel không hoạt động
Nếu công thức PMT Excel của bạn không hoạt động hoặc tạo ra kết quả sai, có thể là do các lý do sau:
- Một #NUM! lỗi có thể xảy ra nếu một trong hai tỷ lệ đối số là số âm hoặc nper bằng 0.
- MỘT GIÁ TRỊ! lỗi xảy ra nếu một hoặc nhiều đối số là giá trị văn bản.
- Nếu kết quả của công thức PMT cao hơn hoặc thấp hơn nhiều so với dự kiến, hãy đảm bảo bạn phù hợp với các đơn vị được cung cấp cho tỷ lệ và nper đối số, nghĩa là bạn đã chuyển đổi chính xác lãi suất hàng năm thành lãi suất của thời kỳ và số năm thành tuần, tháng hoặc quý như trong ví dụ này.
Đó là cách bạn tính toán hàm PMT trong Excel. Để xem kỹ hơn các công thức được thảo luận trong hướng dẫn này, bạn có thể tải về Ví dụ về công thức PMT. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!
Bạn cũng có thể quan tâm:
Source link
The post Hàm PMT Excel với các ví dụ về công thức appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.
source https://ketoanmvb.com/ham-pmt-excel-voi-cac-vi-du-ve-cong-thuc.html
Không có nhận xét nào:
Đăng nhận xét