Trước khi ký hợp đồng vay mua xe, vay mua nhà hay mua điện thoại trả góp, bạn thường muốn biết trước một con số: mỗi tháng mình phải trả bao nhiêu tiền. Tiếp theo là những câu hỏi khó hơn: tổng cộng mình trả bao nhiêu tiền lãi, sau 2 năm còn nợ bao nhiêu, vay 3 năm hay 5 năm thì đỡ áp lực hơn.
Excel giúp bạn tính tiền trả góp và trả lời tất cả các câu hỏi đó chỉ với vài công thức. Hàm chính là hàm PMT, đi kèm các hàm IPMT, PPMT để tách tiền lãi và tiền gốc, CUMIPMT để cộng tiền lãi theo năm, và RATE, NPER, PV, FV để giải bài toán ngược.
Bài viết này hướng dẫn bạn từng bước: cú pháp hàm PMT, ví dụ vay mua xe và mua nhà, cách lập lịch trả nợ đủ 60 kỳ, cách so sánh 3 kiểu tính trả góp hay gặp ở Việt Nam và những lỗi khiến kết quả sai. Mọi số liệu trong bài là số liệu giả định để minh họa cách tính, không phải lãi suất của ngân hàng hay công ty tài chính cụ thể nào.
Hàm PMT tính được gì?
PMT là viết tắt của chữ Payment, nghĩa là khoản thanh toán. Hàm PMT tính số tiền bạn phải trả đều mỗi kỳ (gồm cả gốc và lãi) để trả hết một khoản vay, với điều kiện lãi suất cố định và số tiền trả mỗi kỳ bằng nhau.
Cách trả này còn gọi là trả góp đều hay niên kim đều. Tiền lãi mỗi kỳ vẫn tính trên dư nợ còn lại, nên kỳ đầu bạn trả nhiều lãi, ít gốc, còn những kỳ cuối bạn trả ít lãi, nhiều gốc. Tổng tiền trả mỗi kỳ thì giữ nguyên từ đầu tới cuối.
Hàm PMT có cú pháp như thế nào?
=PMT(rate, nper, pv, [fv], [type])
Hàm PMT có 3 đối số bắt buộc và 2 đối số không bắt buộc (nằm trong ngoặc vuông):
Đối số | Ý nghĩa | Ví dụ |
|---|---|---|
| Lãi suất của một kỳ trả nợ | Lãi 9%/năm, trả hằng tháng thì rate = 9%/12 = 0,75% |
| Tổng số kỳ trả nợ | Vay 5 năm, trả hằng tháng thì nper = 5*12 = 60 |
| Số tiền vay ở thời điểm hiện tại | 500.000.000 |
| Số tiền còn lại sau kỳ cuối. Bỏ trống nghĩa là bằng 0, tức trả hết nợ | Thường bỏ trống |
| Thời điểm trả tiền trong kỳ: 0 hoặc bỏ trống là cuối kỳ, 1 là đầu kỳ | Thường bỏ trống |
Nếu máy tính của bạn dùng dấu phẩy làm dấu thập phân (định dạng vùng Việt Nam), Excel thường yêu cầu dấu chấm phẩy để ngăn cách các đối số, ví dụ =PMT(C3/12;C4*12;C2). Bài viết này dùng dấu phẩy ngăn cách đối số, theo thiết lập vùng mặc định của Excel bản tiếng Anh.
Bạn cần nhớ 3 quy tắc trước khi dùng PMT
Phần lớn kết quả sai khi dùng PMT đến từ 3 điểm sau. Bạn nắm chắc 3 điểm này thì các hàm IPMT, PPMT, RATE, NPER cũng dùng đúng ngay.
Lãi suất phải khớp với kỳ trả. Ngân hàng thường công bố lãi suất theo năm. Nếu bạn trả hằng tháng, bạn chia lãi suất năm cho 12:
rate = 9%/12. Nếu bạn quên chia, Excel hiểu là 9% mỗi tháng và kết quả sai rất xa.Số kỳ phải đếm theo cùng đơn vị. Vay 5 năm, trả hằng tháng thì số kỳ là
5*12 = 60, không phải 5. Vay 20 năm thì số kỳ là 240.Tiền vào là số dương, tiền ra là số âm. Excel tính theo dòng tiền của chính bạn: tiền vay nhận về là số dương, tiền trả cho ngân hàng là số âm. Vì vậy PMT trả về số âm. Bạn muốn hiện số dương thì đặt dấu trừ trước hàm:
=-PMT(...).
Ví dụ 1: Bạn vay 500 triệu mua xe, trả trong 5 năm
Giả sử bạn mua xe ô tô và vay ngân hàng 500.000.000 đồng, lãi suất cố định 9%/năm, trả đều hằng tháng trong 5 năm. Bạn nhập 3 thông tin này vào các ô C2, C3, C4 rồi viết công thức ở ô C7:
=PMT(C3/12,C4*12,C2)

Excel trả về -10.379.178 đồng (giá trị đầy đủ là -10.379.177,61). Dấu âm cho biết đây là tiền bạn trả ra. Khi đặt dấu trừ trước PMT ở ô C8, bạn có số dương 10.379.178 để dễ đọc và dễ cộng trừ tiếp.
Từ số tiền trả mỗi tháng, bạn tính tiếp được 2 con số quan trọng:
Tổng tiền trả bằng tiền trả mỗi tháng nhân số kỳ:
=C8*C6ra 622.750.657 đồng.Tổng lãi phải trả bằng tổng tiền trả trừ số tiền vay:
=C9-C2ra 122.750.657 đồng.
Như vậy, với giả định trên, khoản vay 500 triệu khiến bạn trả thêm khoảng 122,75 triệu tiền lãi sau 5 năm. Nếu bạn cần số tiền tròn đồng như trên hợp đồng, bạn bọc thêm hàm ROUND: =ROUND(-PMT(C3/12,C4*12,C2),0) ra 10.379.178.
Trả đầu kỳ thì sao? Một số hợp đồng thuê tài sản hoặc trả góp yêu cầu trả tiền ngay đầu mỗi kỳ. Khi đó bạn điền thêm đối số type bằng 1: =PMT(C3/12,C4*12,C2,0,1). Với cùng khoản vay, số tiền mỗi tháng giảm còn 10.301.913 đồng, vì mỗi khoản trả đến sớm hơn một tháng nên bạn chịu lãi ít hơn.
Ví dụ 2: PMT so sánh nhiều khoản vay cùng lúc
Khi bạn đang cân nhắc nhiều phương án, bạn đặt mỗi khoản vay trên một dòng và viết công thức một lần rồi kéo xuống. Bảng dưới đây so sánh 4 khoản vay giả định: vay mua xe, vay mua nhà 20 năm, vay tiêu dùng 3 năm và mua laptop trả góp 0%.

Công thức ở ô F2 là =-PMT(D2/12,E2*12,C2), ô G2 là =F2*E2*12-C2. Bảng này cho thấy vài điều đáng chú ý:
Khoản vay mua nhà 2 tỷ trong 20 năm với lãi 10%/năm có tiền trả mỗi tháng 19.300.433 đồng, nhưng tổng lãi lên tới 2.632.103.896 đồng, nhiều hơn cả số tiền vay.
Thời hạn vay càng dài thì tiền trả mỗi tháng càng nhẹ, nhưng tổng tiền lãi càng lớn. Bạn đổi số năm ở cột E để thấy ngay sự khác biệt.
Hàm PMT vẫn chạy đúng khi lãi suất bằng 0%: kết quả chỉ là số tiền vay chia đều cho số kỳ, tức 20.000.000 / 12 = 1.666.667 đồng.
Bạn lập lịch trả nợ từng tháng bằng IPMT và PPMT
PMT chỉ cho bạn tổng số tiền mỗi kỳ. Để biết trong số tiền đó có bao nhiêu là lãi, bao nhiêu là gốc, bạn dùng thêm 2 hàm:
Hàm | Cú pháp | Kết quả |
|---|---|---|
IPMT |
| Phần lãi phải trả ở kỳ thứ per |
PPMT |
| Phần gốc phải trả ở kỳ thứ per |
Hai hàm này có thêm đối số per là số thứ tự của kỳ cần tính, từ 1 tới nper. Ở mọi kỳ, IPMT cộng PPMT luôn bằng PMT. Chữ I là Interest (lãi), chữ P đầu tiên của PPMT là Principal (gốc).
Bạn lập lịch trả nợ theo các bước sau, với số tiền vay ở ô I2, lãi suất năm ở ô I3 và số kỳ ở ô I4:
Cột A ghi số kỳ từ 1 tới 60.
Ô B2 lấy số tiền vay ban đầu:
=$I$2. Từ ô B3 trở xuống, dư nợ đầu kỳ bằng dư nợ cuối kỳ trước:=F2.Ô C2 tính tiền trả mỗi kỳ:
=-PMT($I$3/12,$I$4,$I$2).Ô D2 tính tiền lãi của kỳ:
=-IPMT($I$3/12,A2,$I$4,$I$2).Ô E2 tính tiền gốc của kỳ:
=-PPMT($I$3/12,A2,$I$4,$I$2).Ô F2 tính dư nợ cuối kỳ:
=B2-E2. Sau đó bạn chọn vùng A2:F2 và kéo xuống tới kỳ 60.
Các ô I2, I3, I4 cần khóa bằng dấu $ để khi kéo công thức xuống, Excel không làm trượt địa chỉ. Bạn bấm phím F4 ngay sau khi chọn ô để thêm dấu $ nhanh. Bạn có thể xem thêm các phím tắt khác trong bài Phím tắt Excel thông dụng.

Lịch trả nợ cho thấy rõ cách khoản vay vận hành. Ở kỳ 1, tiền lãi là 3.750.000 đồng, đúng bằng 500.000.000 x 0,75%. Bạn chỉ trả được 6.629.178 đồng tiền gốc. Tới kỳ 60, tiền lãi chỉ còn 77.264 đồng và gần như toàn bộ số tiền trả là gốc.
Bạn nên kiểm tra lịch trả nợ bằng 2 phép thử đơn giản:
Tổng cột Trả gốc
=SUM(E2:E61)phải đúng bằng số tiền vay 500.000.000 đồng.Dư nợ cuối kỳ ở dòng kỳ 60 phải bằng 0. Nếu ra một số khác 0, công thức đang sai lãi suất kỳ hoặc sai số kỳ.
Tổng cột Trả lãi là 122.750.657 đồng, khớp với tổng lãi đã tính ở ví dụ 1. Các con số trong ảnh đã làm tròn về đồng cho dễ đọc, còn Excel vẫn giữ đủ phần thập phân khi tính.
CUMIPMT cho biết tổng lãi trong từng năm
Đôi khi bạn không cần xem 60 dòng, bạn chỉ muốn biết mỗi năm mình trả bao nhiêu tiền lãi. Hàm CUMIPMT cộng tiền lãi của một dãy kỳ liên tiếp, còn hàm CUMPRINC cộng tiền gốc:
=CUMIPMT(rate, nper, pv, start_period, end_period, type)
Hai hàm này có 6 đối số và tất cả đều bắt buộc, kể cả type. Nếu bạn bỏ trống type, Excel không cho nhập công thức. Bạn điền 0 khi trả cuối kỳ. Với năm thứ nhất, kỳ bắt đầu là 1 và kỳ kết thúc là 12. Với năm thứ hai, kỳ bắt đầu là 13 và kỳ kết thúc là 24.

Kết quả cho thấy năm đầu tiên bạn trả 41.635.120 đồng tiền lãi, còn năm thứ 5 chỉ trả 5.865.142 đồng. Tổng 5 năm là 122.750.657 đồng, khớp với lịch trả nợ chi tiết. Nếu muốn tính tổng lãi của cả khoản vay chỉ bằng một công thức, bạn dùng =-CUMIPMT($I$3/12,$I$4,$I$2,1,60,0).
Ba cách tính trả góp cho ra ba kết quả khác nhau
Khi tính tiền trả góp, bạn cần biết hàm PMT chỉ đúng với cách trả đều mỗi kỳ. Trên thực tế, khoản vay của bạn có thể tính theo cách khác, và khi đó số tiền mỗi tháng sẽ không khớp với PMT. Ở Việt Nam, bạn thường gặp 3 cách sau:
Cách tính | Tiền gốc mỗi kỳ | Tiền lãi mỗi kỳ | Tổng tiền trả mỗi kỳ |
|---|---|---|---|
1. Trả đều (niên kim, hàm PMT) | Tăng dần | Dư nợ còn lại x lãi suất kỳ | Bằng nhau mọi kỳ |
2. Gốc đều, lãi theo dư nợ giảm dần | Bằng nhau: số tiền vay / số kỳ | Dư nợ còn lại x lãi suất kỳ | Giảm dần theo từng kỳ |
3. Lãi phẳng (lãi tính trên số tiền vay ban đầu) | Bằng nhau: số tiền vay / số kỳ | Số tiền vay ban đầu x lãi suất kỳ | Bằng nhau mọi kỳ |
Nhiều khoản vay mua nhà, mua xe tại ngân hàng tính theo cách 2: tiền gốc chia đều, tiền lãi tính trên dư nợ còn lại. Một số chương trình trả góp tiêu dùng tính theo cách 3. Vì vậy, bạn cần đọc kỹ hợp đồng hoặc hỏi lại ngân hàng xem khoản vay của mình tính theo cách nào trước khi dùng công thức.
Bạn lập bảng gốc đều bằng các phép tính cơ bản
Cách gốc đều không cần hàm tài chính nào. Với cùng khoản vay 500 triệu, 9%/năm, 60 kỳ, bạn lập bảng như sau:
Trả gốc mỗi kỳ:
=$I$2/$I$4, ra 8.333.333 đồng.Trả lãi mỗi kỳ:
=B2*$I$3/12, tức dư nợ đầu kỳ nhân lãi suất tháng.Tổng trả mỗi kỳ:
=C2+D2. Dư nợ cuối kỳ:=B2-C2.

Với cách gốc đều, tháng đầu tiên bạn trả 12.083.333 đồng, cao hơn cách trả đều. Số tiền giảm dần mỗi tháng và tháng cuối chỉ còn 8.395.833 đồng. Tổng lãi là 114.375.000 đồng, thấp hơn cách trả đều vì bạn trả gốc nhanh hơn ở giai đoạn đầu.
Lãi phẳng làm tổng lãi tăng gần gấp đôi
Với cách lãi phẳng, tiền lãi mỗi tháng luôn tính trên 500 triệu ban đầu: 500.000.000 x 9% / 12 = 3.750.000 đồng, giữ nguyên suốt 60 tháng dù bạn đã trả bớt gốc. Tổng lãi vì vậy là 3.750.000 x 60 = 225.000.000 đồng. Bảng dưới đặt 3 cách cạnh nhau:

Cùng con số "9%/năm", cách lãi phẳng khiến bạn trả nhiều hơn cách trả đều khoảng 102 triệu tiền lãi. Hàng cuối của bảng dùng hàm RATE để quy lãi phẳng về lãi tính trên dư nợ giảm dần: =RATE(60,-E2,500000000)*12 ra khoảng 15,71%/năm. Đây là cách so sánh công bằng giữa các khoản vay có cách tính khác nhau.
Hàm RATE giúp bạn tìm lãi thực của khoản trả góp lãi phẳng
Hàm RATE làm ngược lại với PMT: bạn biết số tiền trả mỗi kỳ, hàm tìm ra lãi suất mỗi kỳ.
=RATE(nper, pmt, pv, [fv], [type], [guess])
Giả sử bạn mua điện thoại giá 30 triệu, trả trước 30%, phần còn lại 21 triệu trả góp trong 12 tháng với lãi phẳng 1,5%/tháng. Mỗi tháng bạn trả 21.000.000 / 12 + 21.000.000 x 1,5% = 2.065.000 đồng, tổng lãi là 3.780.000 đồng.

Công thức =RATE(C5,-C7,C4) cho thấy khoản trả góp này tương đương lãi khoảng 2,64%/tháng nếu tính trên dư nợ giảm dần, tức khoảng 31,72%/năm khi nhân 12. Để so sánh, nếu cùng 21 triệu được tính lãi 1,5%/tháng trên dư nợ giảm dần thì =-PMT(C6,C5,C4) ra 1.925.280 đồng mỗi tháng, tổng lãi chỉ 2.103.358 đồng.
Bạn lưu ý đặt dấu trừ trước tiền trả (-C7) trong hàm RATE. Tiền trả và tiền vay phải ngược dấu nhau, nếu cùng dấu thì RATE báo lỗi #NUM!. Con số 31,72% là lãi suất năm danh nghĩa (lãi tháng nhân 12), dùng để so sánh với lãi suất năm mà các ngân hàng công bố.
PV, NPER, RATE, FV giải các bài toán ngược
Khi lập kế hoạch tài chính cá nhân, bạn hay đặt câu hỏi theo chiều ngược lại: thu nhập cho phép trả tối đa 15 triệu mỗi tháng thì vay được bao nhiêu? Muốn trả nhanh hơn thì mất bao nhiêu tháng? Bốn hàm sau dùng chung bộ đối số với PMT và trả lời các câu hỏi này:
Hàm | Bạn biết | Hàm tìm ra |
|---|---|---|
PV | Lãi suất, số kỳ, tiền trả mỗi kỳ | Số tiền vay tối đa |
NPER | Lãi suất, tiền trả mỗi kỳ, số tiền vay | Số kỳ cần để trả hết nợ |
RATE | Số kỳ, tiền trả mỗi kỳ, số tiền vay | Lãi suất mỗi kỳ |
FV | Lãi suất, số kỳ đã trả, tiền trả mỗi kỳ, số tiền vay | Dư nợ còn lại sau số kỳ đó |

Vay được bao nhiêu:
=PV(10%/12,20*12,-15000000)cho biết nếu trả tối đa 15 triệu mỗi tháng trong 20 năm với lãi 10%/năm, bạn vay được khoảng 1.554.369.280 đồng.Mất bao lâu:
=NPER(9%/12,-15000000,500000000)ra 38,5 kỳ. Bạn làm tròn lên bằng=ROUNDUP(NPER(9%/12,-15000000,500000000),0)để có 39 tháng.Lãi suất bao nhiêu:
=RATE(60,-10379178,500000000)*12ra 9,00%/năm, khớp với giả định ở ví dụ 1.Còn nợ bao nhiêu:
=-FV(9%/12,24,PMT(9%/12,60,500000000),500000000)ra 326.391.977 đồng sau 24 kỳ, đúng bằng dư nợ cuối kỳ 24 trong lịch trả nợ.
Con số dư nợ ở dòng cuối hữu ích khi bạn tính chuyện tất toán trước hạn. Bạn vẫn cần hỏi ngân hàng về phí trả nợ trước hạn (nếu có) theo hợp đồng, vì phí này không nằm trong công thức.
Hàm PMT hay ra kết quả sai ở những chỗ nào?
Bạn thấy | Nguyên nhân | Cách sửa |
|---|---|---|
Tiền trả mỗi tháng ra 45.257.097 thay vì 10.379.178 | Công thức dùng lãi suất năm 9% làm lãi suất tháng | Chia lãi suất năm cho 12: |
Tiền trả mỗi tháng ra 102.261.208 | Đối số nper nhận số năm (5) thay vì số tháng | Nhân số năm với 12: |
Kết quả ra hàng tỷ đồng, ví dụ 4.500.000.000 | Ô lãi suất ghi 9 thay vì 9%, Excel hiểu là 900% | Nhập 9% hoặc 0,09 |
PMT, IPMT, PPMT ra số âm | Excel coi tiền trả ra là số âm, đây không phải lỗi | Thêm dấu trừ trước hàm: |
RATE báo #NUM!, NPER ra số âm | Tiền trả và tiền vay cùng dấu dương | Nhập tiền trả là số âm, tiền vay là số dương |
Dư nợ cuối kỳ 60 khác 0 | Lãi suất kỳ hoặc số kỳ trong IPMT, PPMT khác với PMT | Dùng cùng các ô đã khóa $ cho mọi cột |
Số tiền khác lịch trả nợ của ngân hàng | Ngân hàng tính theo cách gốc đều, lãi phẳng hoặc tính lãi theo số ngày thực tế | Đọc hợp đồng, chọn đúng cách tính và đối chiếu với ngân hàng |
Bạn còn thắc mắc gì về hàm PMT?
Hàm PMT có tính lãi theo dư nợ giảm dần không?
Có. Tiền lãi mỗi kỳ trong PMT luôn bằng dư nợ còn lại nhân lãi suất kỳ, như lịch trả nợ ở trên đã chứng minh. Điểm khác với cách gốc đều nằm ở chỗ PMT giữ tổng tiền trả mỗi kỳ bằng nhau, còn cách gốc đều giữ tiền gốc mỗi kỳ bằng nhau.
Ngân hàng tính gốc đều thì bạn dùng hàm gì?
Bạn không dùng PMT trong trường hợp này. Bạn lập bảng gốc đều với tiền gốc =Số tiền vay/Số kỳ và tiền lãi =Dư nợ đầu kỳ*Lãi suất năm/12 như phần so sánh ở trên.
Lãi suất thay đổi sau thời gian ưu đãi thì PMT còn đúng không?
PMT giả định lãi suất cố định cho toàn bộ thời hạn. Khi lãi suất đổi, bạn tính dư nợ còn lại tại thời điểm đổi bằng FV hoặc lịch trả nợ, rồi dùng PMT lần nữa với dư nợ đó, lãi suất mới và số kỳ còn lại.
Trả góp 0% thì có cần dùng PMT không?
Bạn có thể dùng PMT với rate bằng 0, kết quả bằng số tiền vay chia số kỳ. Bạn vẫn nên hỏi rõ các khoản phí đi kèm như phí hồ sơ hay phí bảo hiểm, vì các khoản này làm chi phí thực tế lớn hơn 0%.
Google Sheets có hàm PMT không?
Có. Google Sheets có hàm PMT, IPMT, PPMT, RATE, NPER với cách dùng tương tự Excel, nên bạn áp dụng được các công thức trong bài.
Bạn muốn dùng thành thạo các hàm Excel cho công việc?
Nhóm hàm tài chính như PMT, IPMT, PPMT không chỉ giúp bạn tính khoản vay cá nhân. Người làm kế toán, tín dụng hay bán hàng trả góp dùng chúng hằng ngày để lập lịch thanh toán cho khách hàng. Bạn có thể luyện thêm với các đề trong bài Bài tập Excel kế toán phỏng vấn, và xem thứ tự học các nhóm hàm trong bài Lộ trình học Excel từ cơ bản đến nâng cao.
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.