Cách tính niên kim trong Excel với PMT, PV, FV

Bạn muốn biết mỗi tháng nhận được bao nhiêu, cần có bao nhiêu tiền gốc, hay khoản tiết kiệm đều đặn sẽ thành bao nhiêu sau nhiều năm? Excel có sẵn các hàm tài chính để trả lời đúng những câu hỏi này. Bài viết giúp bạn chọn đúng hàm theo mục tiêu, nhập đúng lãi suất và số kỳ khi tính theo tháng, hiểu vì sao kết quả ra số âm và tránh các lỗi phổ biến. Bài có một ví dụ bằng số cụ thể cho hàm PMT.

Lưu ý ngay từ đầu: các hàm này giả định lãi suất và khoản thanh toán cố định. Kết quả là con số tính toán, không phải số tiền lương hưu hay quyền lợi bảo hiểm bạn sẽ thực nhận.

Niên kim là gì và Excel giúp được những gì

Hiểu đơn giản, niên kim là một chuỗi các khoản tiền bằng nhau phát sinh đều đặn theo từng kỳ, ví dụ mỗi tháng. Đó có thể là khoản bạn nhận về hằng tháng từ một số tiền gốc, hoặc khoản bạn gửi đều đặn để tích lũy.

Excel chia bài toán này thành năm câu hỏi, mỗi câu có một hàm riêng:

• Mỗi kỳ nhận hoặc trả bao nhiêu? Dùng PMT.
• Cần bao nhiêu tiền ở thời điểm hiện tại? Dùng PV.
• Sau một thời gian sẽ tích lũy được bao nhiêu? Dùng FV.
• Cần bao nhiêu kỳ để đạt mục tiêu? Dùng NPER.
• Lãi suất mỗi kỳ là bao nhiêu? Dùng RATE.

Phạm vi của bài: lãi suất cố định và khoản thanh toán cố định trong suốt thời gian tính. Đây cũng là điều kiện mà tài liệu của Microsoft dùng khi mô tả các hàm này.

Chọn hàm nào cho mục tiêu nào

Bảng dưới đây giúp bạn chọn nhanh hàm phù hợp với câu hỏi của mình.

Hàm	Trả lời câu hỏi	Cần nhập chính	Kết quả là
PMT	Mỗi kỳ nhận hoặc trả bao nhiêu?	Lãi suất kỳ, số kỳ, số tiền hiện tại	Khoản thanh toán mỗi kỳ
PV	Cần bao nhiêu tiền gốc hiện tại?	Lãi suất kỳ, số kỳ, khoản thanh toán mỗi kỳ	Giá trị hiện tại
FV	Gửi đều sẽ thành bao nhiêu?	Lãi suất kỳ, số kỳ, khoản gửi mỗi kỳ	Giá trị cuối kỳ
NPER	Cần bao nhiêu kỳ?	Lãi suất kỳ, khoản thanh toán mỗi kỳ, số tiền hiện tại	Số kỳ
RATE	Lãi suất mỗi kỳ là bao nhiêu?	Số kỳ, khoản thanh toán mỗi kỳ, số tiền hiện tại	Lãi suất mỗi kỳ

Mẹo chọn nhanh: hãy xác định điều bạn chưa biết. Biết lãi suất, số kỳ và số tiền gốc mà chưa biết khoản mỗi kỳ thì dùng PMT. Biết khoản mỗi kỳ mà muốn tìm số tiền gốc thì dùng PV. Muốn biết số tiền sau một thời gian gửi đều thì dùng FV. Muốn biết thời gian thì dùng NPER, muốn biết lãi suất thì dùng RATE.

Cách nhập lãi suất và số kỳ khi trả theo tháng

Đây là chỗ người dùng hay nhập sai nhất. Các hàm tài chính của Excel tính theo kỳ, nên lãi suất và số kỳ phải cùng một đơn vị.

• Lãi suất mỗi kỳ bằng lãi suất năm chia 12 khi lãi tính gộp theo tháng. Ví dụ ô lãi suất năm là A2 thì nhập A2/12.
• Số kỳ bằng số năm nhân 12. Ví dụ số năm nằm ở ô A3 thì nhập A3*12.
• Nếu thanh toán theo năm thì giữ nguyên lãi suất năm và số năm.

Trước khi chia cho 12, hãy kiểm tra hợp đồng hoặc sản phẩm của bạn có tính lãi gộp theo tháng hay không. Cách chia này là ví dụ cho trường hợp lãi tính gộp theo tháng. Nếu cách tính lãi khác, hãy quy đổi cho đúng trước khi nhập.

Dùng PMT để tính khoản nhận hoặc trả mỗi kỳ

Cú pháp của hàm: PMT(rate, nper, pv, [fv], [type]). Hàm này giả định lãi suất và khoản thanh toán không đổi qua các kỳ.

Ví dụ lấy theo hướng dẫn của Spreadsheet Planet: số tiền 100.000 USD, lãi suất 5%/năm, thời gian 20 năm, thanh toán vào cuối mỗi tháng. Với điều kiện này, ví dụ cho kết quả khoảng 659,96 USD mỗi tháng. Đây là con số của ví dụ theo đúng các điều kiện trên, không phải mức bạn sẽ thực nhận.

Các bước nhập trong bảng tính:

1. Ô A2: nhập lãi suất năm là 5%.
2. Ô A3: nhập số năm là 20.
3. Ô A4: nhập số tiền hiện tại là 100000.
4. Ô A5: nhập công thức =PMT(A2/12, A3*12, -A4).

Dấu trừ trước A4 giúp kết quả hiện số dương. Lý do được giải thích ở phần số âm bên dưới. Phần type để trống nên Excel hiểu là thanh toán cuối kỳ.

Dùng PV để tìm số tiền gốc cần có

Hàm PV trả lời câu hỏi: muốn nhận đều một khoản mỗi tháng thì cần có bao nhiêu tiền ở thời điểm hiện tại? Cú pháp: PV(rate, nper, pmt, [fv], [type]).

Cách nhập khi trả theo tháng:

1. Lãi suất kỳ: lãi suất năm chia 12.
2. Số kỳ: số năm nhân 12.
3. Khoản mỗi kỳ: số tiền bạn muốn nhận mỗi tháng.

Cách đối chiếu hữu ích: lấy kết quả của PMT ở mục trước làm khoản mỗi kỳ, giữ nguyên lãi suất và số kỳ. PV phải cho ra số tiền gốc ban đầu, chỉ khác về dấu theo quy ước dòng tiền. Nếu hai kết quả không khớp, hãy kiểm tra lại đơn vị kỳ và type.

Dùng FV để biết số tiền tích lũy

Hàm FV phù hợp với người gửi đều đặn mỗi tháng và muốn biết số tiền ở cuối kỳ. Cú pháp: FV(rate, nper, pmt, [pv], [type]).

Cách nhập:

1. Lãi suất kỳ: lãi suất năm chia 12 khi lãi tính gộp theo tháng.
2. Số kỳ: số năm nhân 12.
3. Khoản gửi mỗi kỳ: nhập với dấu âm, vì đó là tiền bạn chi ra. Kết quả sẽ là số dương.

Ví dụ công thức: =FV(A2/12, A3*12, -A6) với A6 là khoản gửi mỗi tháng. Nếu gửi vào đầu mỗi kỳ, hãy thêm đối số type bằng 1, vì khi đó mỗi khoản gửi bắt đầu sinh lãi sớm hơn một kỳ.

Dùng NPER và RATE để tìm thời gian và lãi suất

NPER: cần bao nhiêu kỳ

NPER cho biết số kỳ cần thiết khi lãi suất và khoản thanh toán mỗi kỳ cố định. Bạn nhập lãi suất kỳ, khoản thanh toán mỗi kỳ và số tiền hiện tại. Kết quả là số kỳ, nên với trả theo tháng hãy chia cho 12 nếu muốn ra số năm.

RATE: lãi suất ngầm định

Theo tài liệu của Microsoft, RATE trả về lãi suất mỗi kỳ của một niên kim. Cú pháp: RATE(nper, pmt, pv, [fv], [type], [guess]), trong đó nper, pmt và pv là các đối số bắt buộc. Khoản thanh toán mỗi kỳ không được thay đổi trong suốt vòng đời niên kim.

Vì kết quả là lãi suất theo kỳ, với trả theo tháng bạn nhân 12 để ra lãi suất năm. Ví dụ: =RATE(A3*12, A5, -A4)*12, trong đó A3 là số năm, A5 là khoản mỗi tháng và A4 là số tiền gốc. Lưu ý pmt và pv phải ngược dấu nhau theo quy ước dòng tiền, và đơn vị của số kỳ phải khớp với kỳ thanh toán.

Thanh toán đầu kỳ và cuối kỳ khác nhau thế nào

Đối số type cho Excel biết khoản thanh toán diễn ra vào lúc nào trong kỳ. Giá trị 0 hoặc bỏ trống nghĩa là cuối kỳ, giá trị 1 nghĩa là đầu kỳ.

Tiêu chí	Cuối kỳ (type = 0 hoặc bỏ trống)	Đầu kỳ (type = 1)
Thời điểm thanh toán	Vào cuối mỗi kỳ	Vào đầu mỗi kỳ
Cách nhập	Để trống hoặc nhập 0	Nhập 1
Ảnh hưởng đến kết quả	Kết quả theo thời điểm cuối kỳ	Kết quả thay đổi vì mỗi khoản đến sớm hơn một kỳ
Khi nào chọn	Hợp đồng ghi thanh toán cuối kỳ	Hợp đồng ghi thanh toán đầu kỳ

Cách chọn đúng là dựa vào điều khoản thực tế của khoản tiền bạn đang tính, không chọn theo thói quen. Ví dụ ở phần PMT ở trên dùng thanh toán cuối tháng.

Vì sao kết quả ra số âm và cách xử lý

Excel dùng quy ước dòng tiền: tiền bạn chi ra là số âm, tiền bạn nhận vào là số dương. Vì vậy nếu bạn nhập số tiền hiện tại là số dương (coi như tiền bạn nhận), hàm PMT sẽ trả về khoản phải trả mỗi kỳ dưới dạng số âm.

Hai cách xử lý:

• Nhập đúng dấu theo vai trò của dòng tiền, ví dụ nhập số tiền gốc với dấu âm khi đó là tiền bạn bỏ ra: =PMT(A2/12, A3*12, -A4).
• Hoặc đặt dấu trừ trước cả hàm: =-PMT(A2/12, A3*12, A4).

Số âm không phải là lỗi. Đó chỉ là cách Excel cho biết dòng tiền đi ra. Điều quan trọng là bạn nhất quán về dấu trong cả bảng tính.

Những lỗi thường gặp và giới hạn của cách tính này

Trước khi tin vào một kết quả, hãy rà nhanh danh sách sau:

• Đơn vị kỳ khớp nhau: lãi suất theo tháng đi cùng số kỳ theo tháng, lãi suất theo năm đi cùng số năm.
• Dấu đúng: tiền chi là âm, tiền thu là dương, và không để hai đối số cùng dấu khi hàm cần ngược dấu.
• Đúng type: đầu kỳ hay cuối kỳ phải khớp với điều khoản thực tế.
• Đúng cách tính lãi gộp trước khi chia lãi suất năm cho 12.
• Với RATE: nếu Excel báo lỗi #NUM!, nghĩa là các lần lặp không hội tụ sau 20 lần. Hãy kiểm tra dấu của pmt và pv, đơn vị kỳ, hoặc thử nhập một giá trị đoán (guess) khác.

Giới hạn cần biết:

• Các hàm này giả định lãi suất và khoản thanh toán cố định, nên không áp dụng nguyên cho trường hợp lãi suất hay khoản chi thay đổi theo thời gian.
• Kết quả có thể chưa gồm thuế, phí hay khoản dự phòng, nên không nên xem là số tiền thực nhận hay thực trả.
• Kết quả không đồng nghĩa với mức lương hưu, quyền lợi bảo hiểm hay số tiền bảo đảm từ một sản phẩm cụ thể. Muốn biết quyền lợi thực tế, hãy xem điều khoản của sản phẩm hoặc hỏi đơn vị cung cấp.

Câu hỏi thường gặp

Hàm nào tính khoản nhận mỗi tháng?

Dùng PMT. Bạn nhập lãi suất kỳ, số kỳ và số tiền hiện tại, Excel trả về khoản thanh toán mỗi kỳ.

Cần bao nhiêu tiền gốc để nhận đều hằng tháng?

Dùng PV. Nhập lãi suất kỳ, số kỳ và khoản bạn muốn nhận mỗi tháng. Bạn có thể đối chiếu bằng cách đưa kết quả PMT trở lại vào PV.

Lãi suất năm nhập thế nào khi trả theo tháng?

Khi lãi tính gộp theo tháng, lấy lãi suất năm chia 12 và số năm nhân 12. Nếu cách tính lãi khác, hãy quy đổi cho đúng trước khi nhập.

Đầu kỳ và cuối kỳ khác nhau ra sao?

Type bằng 0 hoặc bỏ trống là thanh toán cuối kỳ, type bằng 1 là thanh toán đầu kỳ. Chọn theo điều khoản thực tế, vì hai cách cho kết quả khác nhau.

Vì sao Excel báo số âm?

Do quy ước dòng tiền: tiền chi là âm, tiền thu là dương. Hãy nhập dấu cho đúng vai trò của từng khoản hoặc đặt dấu trừ trước hàm để ra số dương.

Dùng được khi lãi suất thay đổi không?

Các hàm trong bài giả định lãi suất và khoản thanh toán cố định, nên không áp dụng nguyên cho trường hợp thay đổi. Với trường hợp đó, bạn cần chia nhỏ theo từng giai đoạn hoặc dùng cách tính khác.

Tham gia cộng đồng của chúng tôi