Cách tính khoản thanh toán khoản vay hàng tháng bằng Excel (Thế chấp, Khoản vay xe và hơn thế nữa) |

Nếu bạn không phải là một chuyên gia trong việc sử dụng Microsoft Excel, thì việc tính toán khoản thanh toán tiền vay hàng tháng bằng cách sử dụng chương trình có thể có vẻ đáng sợ. Tin tốt là nó tương đối đơn giản để làm.

Thực tế, Excel có chức năng tự động được tích hợp vào chương trình tính toán các khoản thanh toán hàng tháng cho bạn. Tất cả những gì bạn cần làm là nhập các chi tiết cụ thể của khoản vay và bạn có thể tính toán các khoản thanh toán hàng tháng cho các khoản thế chấp, cho vay mua xe, vay sinh viên, v.v.

Dưới đây chúng tôi hướng dẫn bạn cách tính toán khoản thanh toán hàng tháng bằng Excel. Nếu bạn muốn, InvestingAnswers cũng có một số máy tính có thể tính toán các khoản thanh toán của bạn.

Tính toán các khoản thanh toán hàng tháng bằng Excel (Tất cả các phiên bản)

Để tính khoản thanh toán hàng tháng cho khoản vay bằng Excel, bạn sẽ sử dụng công cụ tích hợp được gọi là "PMT" hoặc chức năng "Thanh toán".

Chức năng PMT hoạt động giống nhau trên tất cả các phiên bản của Excel, do đó các hướng dẫn bên dưới sẽ hoạt động không có vấn đề gì nếu bạn đang chạy phiên bản cũ hoặc mới của chương trình.

Chức năng PMT yêu cầu ba điểm dữ liệu để tính toán một khoản thanh toán tiền vay hàng tháng - lãi suất, số tiền trả nợ và số tiền vay.

• RATE Bắt buộc: Lãi suất của khoản vay
• NPER (số kỳ) Bắt buộc: Số tiền thanh toán khoản vay
• PV (giá trị hiện tại) Bắt buộc: Số tiền được mượn

Có hai điểm dữ liệu tùy chọn khác bạn có thể sử dụng cho phép tính cụ thể của mình, nếu cần:

• FV (giá trị tương lai) Tùy chọn: Số dư cuối cùng sau khi tất cả các khoản thanh toán được thực hiện (thường là $ 0)
• LOẠI Tùy chọn: Sử dụng "0" hoặc "1" để xác định xem khoản thanh toán có được tính theo thời gian bắt đầu hoặc kết thúc hay không của tháng (giả sử thanh toán khoản vay được thực hiện hàng tháng).

Để sử dụng hàm PMT, bạn sẽ chọn ô và nhập "= PMT (" không có dấu ngoặc kép. Sau đó, nó sẽ bắt đầu yêu cầu bạn nhập vào sau các điểm dữ liệu bổ sung:

RATE : Sau khi nhập dấu ngoặc đơn mở, Excel sẽ yêu cầu mức lãi suất hoặc lãi suất cho vay đầu tiên, tại đây bạn sẽ nhập lãi suất theo tỷ lệ phần trăm cho từng kỳ. bạn muốn tính toán một mortga hàng tháng thanh toán địa lý bằng lãi suất 5%, bạn có thể nhập "5% / 12" hoặc "0,05 / 12". "/ 12" chia lãi suất hàng năm thành số tiền hàng tháng. (Thận trọng: Nếu bạn chỉ nhập "5/12" thay vào đó, Excel sẽ giải thích điều này dưới dạng tỷ lệ hàng năm 500% được trả hàng tháng. Chỉ nhập "5" sẽ dẫn đến lãi suất 500% mỗi tháng.)

NPER : Khi bạn nhập lãi suất, hãy nhập dấu phẩy để di chuyển đến điểm dữ liệu tiếp theo, NPER hoặc số dấu chấm. Đây đơn giản là số tiền bạn sẽ trả cho khoản vay. Ví dụ: khoản vay thế chấp 30 năm được thanh toán hàng tháng sẽ có tổng số 360 thanh toán, do đó bạn có thể nhập "360" hoặc "30 * 12". Nếu bạn muốn tính khoản vay 5 năm được trả hàng tháng, bạn sẽ nhập "60" hoặc "5 * 12" cho số kỳ.

PV : Đối với điểm dữ liệu PV (giá trị hiện tại), bạn sẽ nhập số tiền vay. Ví dụ: nếu bạn đã vay 150.000 đô la, bạn sẽ nhập "150000" vào phần này.

FV : Sử dụng điểm dữ liệu FV (Giá trị tương lai) là tùy chọn. Nó thường đi vào sử dụng nếu bạn đang tính toán một mục tiêu tiết kiệm thay vì trả một số dư cho vay. Vì phần lớn các khoản vay được dựa trên việc hoàn trả khoản vay hoàn toàn, điều này sẽ tự động được mặc định là 0 đô la cho hàm PMT. Nếu bạn thay vì tiết kiệm tiền cho mục tiêu, bạn có thể nhập "0" cho PV và số dư bạn muốn sau lần thanh toán cuối cùng cho giá trị trong tương lai.

TYPE : Cuối cùng, bằng cách sử dụng điểm dữ liệu TYPE, bạn có thể chỉ định xem các khoản thanh toán khoản vay có xảy ra ở đầu mỗi giai đoạn hay ở cuối hay không. Thời gian thanh toán có tác động đến số tiền lãi được tích luỹ trong tháng.

Lưu ý : Trong khi bạn có thể nhập riêng từng điểm dữ liệu, thì tốt nhất là nên có một ô dành riêng cho mỗi hình như chúng tôi làm trong các ví dụ bên dưới. Bằng cách đó, bạn có thể điều chỉnh dữ liệu của mình trong các ô dễ dàng để xem cách thay đổi thanh toán hàng tháng.

Ví dụ: Tính thanh toán thế chấp hàng tháng trong Excel

Giả sử bạn đang mua một khoản thế chấp và muốn biết thanh toán hàng tháng của bạn sẽ là bao nhiêu. Để tính toán, tất cả những gì bạn cần là ba điểm dữ liệu:

• Lãi suất
• Thời lượng cho vay
• Số tiền được vay

Chúng tôi đã chèn các điểm dữ liệu ví dụ dưới đây. Ở đây, chúng tôi tính toán khoản thanh toán hàng tháng cho khoản vay thế chấp 30 năm là 250.000 đô la với lãi suất là 5,0%:

Để tính toán thanh toán của chúng tôi, chúng tôi nhập dữ liệu này vào trường thích hợp trong hàm PMT. Lưu ý rằng vì thế chấp của chúng tôi dựa trên các khoản thanh toán hàng tháng, chúng tôi sẽ chia lãi suất cho 12 (để cho chúng tôi lãi suất hàng tháng) và nhân số tiền thanh toán cho 12 (để cung cấp cho chúng tôi tổng số tiền thanh toán):

Như bạn có thể thấy bên dưới, khoản thanh toán gốc và lãi hàng tháng cho khoản vay thế chấp này là $ 1,342.05 . Điều này được thể hiện trong Excel dưới dạng một số âm vì nó đại diện cho số tiền hàng tháng được chi tiêu. Nếu bạn muốn hiển thị nó như một con số tích cực, bạn có thể nhập một dấu âm trước số tiền được vay.

[Bạn cũng có thể bấm vào đây để sử dụng máy tính thế chấp của InvestingAnswers để xem mức lãi suất, điều khoản và số tiền vay ảnh hưởng đến khoản thanh toán của bạn.]

Ví dụ: Tính toán Thanh toán bằng Xe hàng tháng trong Excel

Tính toán thanh toán hàng tháng bằng xe hơi tương tự như tính toán khoản thanh toán thế chấp hàng tháng. Để bắt đầu, bạn sẽ cần lãi suất, thời gian vay và số tiền vay.

Ví dụ này, giả sử khoản vay xe hơi là 32.000 đô la trong năm năm với lãi suất 3,9%:

Như đã thảo luận ở trên, bạn sẽ nhập dữ liệu này vào hàm PMT để tính toán khoản thanh toán hàng tháng của bạn. Vì khoản vay này được trả hàng tháng, lãi suất sẽ cần chia cho 12 trong khi thời gian cho vay sẽ được nhân với 12:

Một lần nữa, số tiền thanh toán hàng tháng sẽ hiển thị dưới dạng âm bởi vì đó là luồng tiền tiền mỗi tháng. Trong trường hợp này, thanh toán hàng tháng cho xe hơi đến $ 587.89:

[Bạn cũng có thể bấm vào đây để sử dụng máy tính cho vay tự động của InvestingAnswers để xem mức lãi suất, điều khoản và số tiền vay ảnh hưởng đến khoản thanh toán của bạn như thế nào.]

chắc chắn tôi đã nhập công thức chính xác trong Excel?

Cách dễ nhất để biết bạn có công thức đúng hay không là làm theo các ví dụ trên. Bạn sẽ nhận được cùng một con số chính xác được hiển thị. Bạn cũng có thể kiểm tra công việc của mình với máy tính thế chấp hoặc máy tính tự động.

Tại sao thanh toán hàng tháng của tôi hiển thị là âm?

Vì thanh toán được coi là dòng tiền, Excel hiển thị thanh toán hàng tháng dưới dạng số âm. Nếu bạn cảm thấy thoải mái hơn khi nhìn thấy một con số tích cực, bạn có thể điều chỉnh công thức bằng cách đặt dấu âm trước số tiền đã mượn.

Làm thế nào để thay đổi các giá trị trong hàm PMT thay đổi thanh toán hàng tháng? sẽ điều chỉnh thanh toán hàng tháng. Lãi suất cao hơn sẽ làm tăng số tiền trả nợ hàng tháng trong khi lãi suất thấp hơn sẽ giảm. Các điều khoản vay dài hơn giảm các khoản thanh toán hàng tháng trong khi các điều khoản cho vay ngắn hơn tăng lên. Số tiền cao hơn được vay sẽ làm tăng các khoản thanh toán hàng tháng trong khi số tiền thấp hơn sẽ giảm chúng.

Excel tiếp tục cho tôi một lỗi trong công thức PMT. Tôi phải làm gì?

Lý do phổ biến nhất cho thông báo lỗi là không đặt dấu đóng ngoặc khi nhập hàm PMT. Bạn cũng có thể gặp phải số tiền không chính xác nếu bạn quên điều chỉnh lãi suất hoặc thời hạn cho vay để tính đến tần suất thanh toán. Ví dụ, nếu bạn thực hiện thanh toán hàng tháng, bạn nên chia lãi suất cho 12. Nếu thời hạn vay của bạn là nhiều năm, nhưng bạn sẽ thực hiện thanh toán hàng tháng, bạn sẽ muốn nhân số tiền vay dài thêm 12.