=PMT(B2/12,B3*12,-B1) trả về số tiền phải trả mỗi tháng cho khoản vay B1 với lãi suất năm ở B2, trả dần trong B3 năm. Lãi suất được chia cho 12 và số năm được nhân với 12 vì các khoản trả diễn ra hằng tháng.
| A | B | |
|---|---|---|
| 1 | Loan amount | $250,000 |
| 2 | Annual rate | 6.5% |
| 3 | Years | 30 |
| 4 | ||
| 5 | Monthly payment | $1,580.17 |
| 6 | Total paid | $568,861.22 |
| 7 | Total interest | $318,861.22 |
Khoản vay 250.000 với lãi suất 6,5% trong 30 năm tốn 1.580,17 mỗi tháng, và tổng tiền lãi trong cả kỳ hạn là 318.861,22, nhiều hơn chính khoản vay. Đổi B3 thành 15: số tiền trả mỗi tháng tăng lên 2.177,77 nhưng tổng tiền lãi giảm xuống dưới một nửa.
Cú pháp hàm PMT
=PMT(rate, nper, pv, [fv], [type])
rate: lãi suất mỗi kỳ. Với khoản trả hằng tháng theo lãi suất năm, dùngrate/12.nper: số kỳ trả. 30 năm trả hằng tháng là30*12, tức 360 kỳ.pv: giá trị hiện tại, tức số tiền vay.fv(không bắt buộc): số tiền còn lại vào cuối kỳ. Bằng 0 với khoản vay trả hết, đây cũng là giá trị mặc định; bằng mục tiêu tiết kiệm với kế hoạch tiết kiệm.type(không bắt buộc): 0 (mặc định) khi trả vào cuối mỗi kỳ, như phần lớn các khoản vay; 1 khi trả vào đầu mỗi kỳ, như tiền thuê nhà hay thuê tài sản.
PMT giả định lãi suất cố định và các kỳ trả bằng nhau. Hàm không tính thuế, bảo hiểm hay các khoản phí mà bên cho vay cộng vào tiền trả góp mua nhà.
Vì sao PMT trả về số âm
Các hàm tài chính của Excel (PMT, PV, FV, NPV, IRR) dùng dấu của số để chỉ chiều của dòng tiền. Tiền bạn nhận là số dương, tiền bạn chi ra là số âm. Khoản vay là tiền bạn nhận, nên =PMT(B2/12,B3*12,B1) trả về -1.580,17: một khoản tiền đi ra.
Đó là lý do công thức trên trang này có -B1: số tiền vay đi vào dưới dạng số âm, nên số tiền trả đi ra là số dương. =-PMT(B2/12,B3*12,B1) cũng cho kết quả tương tự. Hãy chọn một cách và dùng nhất quán, vì chính quy tắc dấu này quyết định fv: mục tiêu tiết kiệm mà bạn sẽ nhận được nhập dưới dạng giá trị tương lai âm, với giá trị hiện tại bằng 0.
So sánh các kỳ hạn vay
Đặt các kỳ hạn vào một cột, cố định số tiền vay và lãi suất bằng tham chiếu tuyệt đối, rồi kéo công thức xuống để so sánh tiền trả mỗi tháng và tổng chi phí cạnh nhau.
| A | B | C | |
|---|---|---|---|
| 1 | Amount | $28,000 | |
| 2 | Rate | 7.9% | |
| 3 | Years | Monthly | Total interest |
| 4 | 3 | $876.13 | $3,540.58 |
| 5 | 4 | $682.25 | $4,747.92 |
| 6 | 5 | $566.40 | $5,984.00 |
| 7 | 6 | $489.56 | $7,248.66 |
Kéo dài khoản vay từ 3 lên 6 năm làm tiền trả mỗi tháng giảm từ 876,13 xuống 489,56, trong khi tiền lãi tăng từ 3.540,58 lên 7.248,66. Muốn hiểu thêm về dấu $, xem tham chiếu tuyệt đối.
Mỗi tháng cần tiết kiệm bao nhiêu
Khi pv bằng 0 và có giá trị tương lai, PMT trả lời câu hỏi ngược lại: mỗi tháng cần để dành bao nhiêu để đạt một mục tiêu. Mục tiêu là tiền bạn sẽ nhận, nên nó được nhập dưới dạng số âm để số tiền gửi là số dương.
| A | B | |
|---|---|---|
| 1 | Goal | $20,000 |
| 2 | Annual rate | 4% |
| 3 | Years | 5 |
| 4 | ||
| 5 | Monthly deposit | $301.66 |
Gửi 301,66 mỗi tháng trong 60 tháng với lãi suất 4% sẽ đạt 20.000; tổng tiền gửi khoảng 18.100, phần còn lại do tiền lãi bù vào. Đổi B2 thành 0% thì số tiền gửi đúng bằng 20.000 chia cho 60.
Tiền lãi và tiền gốc: IPMT và PPMT
Mỗi kỳ trả gồm một phần tiền lãi và một phần tiền gốc. IPMT trả về phần tiền lãi của một kỳ, PPMT trả về phần tiền gốc; cộng lại thì bằng PMT. Cả hai nhận số thứ tự kỳ trả làm đối số thứ hai, nên khi kéo xuống bạn có ngay một bảng trả nợ.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Payment | Interest | Principal | Balance |
| 2 | 1 | $1,580.17 | $1,354.17 | $226.00 | $249,774.00 |
| 3 | 2 | $1,580.17 | $1,352.94 | $227.23 | $249,546.77 |
| 4 | 3 | $1,580.17 | $1,351.71 | $228.46 | $249,318.31 |
| 5 | 4 | $1,580.17 | $1,350.47 | $229.70 | $249,088.61 |
| 6 | 5 | $1,580.17 | $1,349.23 | $230.94 | $248,857.67 |
Ở tháng 1, trong 1.580,17 tiền trả có 1.354,17 là tiền lãi và chỉ 226,00 làm giảm dư nợ. Phần tiền gốc tăng lên một chút mỗi tháng. Trong một workbook thật, hãy đặt lãi suất, kỳ hạn và số tiền vào các ô rồi tham chiếu chúng bằng $, như ở các trang tính phía trên.
Thử ngay: tiền trả góp mua xe
| A | B | |
|---|---|---|
| 1 | Loan | $32,000 |
| 2 | Annual rate | 6.9% |
| 3 | Years | 5 |
| 4 | ||
| 5 | Monthly payment |
Đến lượt bạn: Ở B5, tính số tiền phải trả mỗi tháng cho khoản vay ở B1 với lãi suất năm ở B2 trong số năm ở B3. Hiện kết quả là số dương.
Gợi ý: lãi suất và số kỳ trả đều phải tính theo tháng.
Lỗi thường gặp: dùng lãi suất năm cho khoản trả hằng tháng
Lãi suất và nper phải cùng một đơn vị kỳ. Giữ nguyên lãi suất năm trong khi đếm theo tháng nghĩa là tính lãi 6,5% mỗi tháng, và số tiền trả cao gấp khoảng mười lần:
| A | B | |
|---|---|---|
| 1 | Loan amount | $250,000 |
| 2 | Annual rate | 6.5% |
| 3 | Years | 30 |
| 4 | ||
| 5 | Wrong | $16,250.00 |
| 6 | Right | $1,580.17 |
Công thức sai đòi 16.250,00 mỗi tháng, chính là tiền lãi 6,5% mỗi tháng. Một dạng khác của lỗi này là gõ lãi suất thành 6.5 thay vì 6.5% hoặc 0.065: như vậy là 650% một năm. Nếu số tiền trả trông vô lý, hãy kiểm tra đơn vị của lãi suất trước tiên. Với khoản trả hằng năm, dùng nguyên lãi suất năm và số năm.
Câu hỏi thường gặp
Làm sao để tính tiền trả góp hằng tháng của khoản vay trong Excel?
Dùng =PMT(rate/12, years*12, -amount), ví dụ =PMT(6.5%/12,30*12,-250000) cho khoản vay 250.000 trong 30 năm với lãi suất 6,5%. Kết quả là khoảng 1.580,17 mỗi tháng.
Vì sao PMT trả về số âm trong Excel?
Các hàm tài chính của Excel dùng dấu của số để chỉ chiều của dòng tiền: tiền bạn nhận là số dương, tiền bạn trả là số âm. Khoản vay là tiền đến tay bạn, nên khoản trả góp đi ra là số âm. Đặt dấu trừ trước số tiền vay (-B1) hoặc trước PMT để hiện kết quả là số dương.
Làm sao để tính tổng tiền lãi của khoản vay trong Excel?
Nhân số tiền trả mỗi kỳ với số kỳ trả rồi trừ đi khoản vay: =PMT(B2/12,B3*12,-B1)*B3*12-B1. Muốn biết tiền lãi trả trong năm đầu, dùng =-CUMIPMT(B2/12,B3*12,B1,1,12,0).
PMT, IPMT và PPMT khác nhau thế nào?
PMT là toàn bộ số tiền trả mỗi kỳ. IPMT là phần tiền lãi trong một kỳ, PPMT là phần tiền gốc, và cộng hai phần lại thì bằng PMT. Những kỳ trả đầu tiên chủ yếu là tiền lãi.
Làm sao để tính mỗi tháng cần tiết kiệm bao nhiêu trong Excel?
Đưa cho PMT một giá trị tương lai thay vì giá trị hiện tại: =PMT(4%/12,5*12,0,-20000) là số tiền gửi mỗi tháng để có 20.000 sau 5 năm với lãi suất 4% một năm.
