Cách tính NPV, IRR, XNPV, XIRR trong Excel và các lỗi thường gặp
Excel có bốn hàm chính để thẩm định dòng tiền: NPV và IRR cho dòng tiền phát sinh đều theo kỳ, XNPV và XIRR cho dòng tiền gắn với ngày cụ thể. Cách tính NPV, IRR trong Excel không khó, nhưng mỗi hàm có quy ước riêng về thời điểm chiết khấu, và nhập sai một ô có thể làm kết quả lệch vài lần. Bài viết hướng dẫn từng hàm trên cùng một ví dụ, kiểm tra lại bằng phép tính tay và chỉ ra các lỗi hay gặp.
1. Các hàm NPV, IRR, XNPV, XIRR trong Excel dùng khi nào?
Chọn hàm theo hai câu hỏi: cần kết quả bằng tiền hay bằng tỷ lệ, và dòng tiền có cách đều nhau hay không. NPV và XNPV trả về giá trị hiện tại ròng (số tiền); IRR và XIRR trả về tỷ suất hoàn vốn nội bộ (%).
| Hàm | Cú pháp | Kết quả | Dùng khi |
|---|---|---|---|
| NPV | NPV(rate; value1; [value2]; ...) | Giá trị hiện tại của các dòng tiền từ kỳ 1 | Dòng tiền cách đều (năm, quý, tháng) |
| IRR | IRR(values; [guess]) | Suất chiết khấu làm NPV bằng 0, theo kỳ | Dòng tiền cách đều |
| XNPV | XNPV(rate; values; dates) | Giá trị hiện tại ròng về ngày đầu tiên | Dòng tiền gắn với ngày cụ thể |
| XIRR | XIRR(values; dates; [guess]) | Suất chiết khấu năm làm XNPV bằng 0 | Dòng tiền gắn với ngày cụ thể |
| MIRR | MIRR(values; finance_rate; reinvest_rate) | IRR điều chỉnh theo lãi suất tài trợ và tái đầu tư | Dòng tiền cách đều, cần tránh nhiều nghiệm IRR |
Khái niệm và cách đọc hai chỉ tiêu gốc được trình bày trong bài NPV là gì? và IRR là gì?. Bài này chỉ tập trung vào cách Excel tính và những chỗ dễ nhập sai.
2. Chuẩn bị bảng tính
Bảng tính cần một cột dòng tiền xếp đúng thứ tự thời gian, dòng tiền ra mang dấu âm, dòng tiền vào mang dấu dương. Với XNPV và XIRR, thêm một cột ngày tương ứng từng dòng tiền.
Ví dụ xuyên suốt bài: Công ty VQ (doanh nghiệp giả định) xem xét một dây chuyền đóng gói vốn 5,0 tỷ đồng, giải ngân ngày 01/03/2026. Năm đầu là năm lắp đặt nên chưa có dòng tiền; từ năm 2 đến năm 5, dây chuyền tạo ra dòng tiền ròng 1,6; 2,0; 2,0; 1,6 tỷ đồng. Suất chiết khấu là 10%/năm. Bố cục bảng tính như Hình 1: năm ở cột A, ngày ở cột B, dòng tiền ở C2:C7, suất chiết khấu ở ô G2.

Công thức trong bài viết theo kiểu máy đặt định dạng vùng Việt Nam: dấu phẩy thập phân, và Excel khi đó thường dùng dấu chấm phẩy để ngăn các đối số. Nếu Excel dùng định dạng tiếng Anh (Mỹ), thay dấu chấm phẩy bằng dấu phẩy, ví dụ =NPV(G2,C3:C7)+C2.
3. Cách tính NPV trong Excel bằng hàm NPV
Công thức đúng cho ví dụ là =NPV(G2;C3:C7)+C2, cho kết quả 0,184 tỷ đồng. Khoản đầu tư năm 0 ở ô C2 phải cộng bên ngoài hàm.
3.1. Cú pháp và cách hàm NPV chiết khấu
Hàm NPV của Excel chiết khấu giá trị đầu tiên một kỳ, giá trị thứ hai hai kỳ, và cứ thế. Microsoft mô tả rằng khoản đầu tư trong hàm NPV "bắt đầu một kỳ trước ngày của dòng tiền value1" (nguyên văn: The NPV investment begins one period before the date of the value1 cash flow). Viết thành công thức, với là giá trị thứ trong danh sách và là suất chiết khấu mỗi kỳ:
Tên hàm là NPV nhưng thực chất nó tính giá trị hiện tại (PV) của dòng tiền từ kỳ 1 trở đi. Muốn có NPV theo nghĩa tài chính doanh nghiệp, tức , phải cộng thêm (không chiết khấu) ngoài hàm. Microsoft cũng hướng dẫn: nếu dòng tiền đầu tiên xảy ra vào đầu kỳ thứ nhất, cộng nó vào kết quả NPV thay vì đưa vào danh sách giá trị.
Hàm NPV còn giả định các giá trị cách đều nhau và xảy ra vào cuối mỗi kỳ. Suất chiết khấu phải cùng kỳ với dòng tiền: dòng tiền theo quý thì dùng lãi suất quý.
3.2. Ví dụ từng bước
Bảng dưới tính lại kết quả của Excel bằng tay, để thấy hàm đang làm gì.
| Năm | Dòng tiền (tỷ đồng) | Hệ số chiết khấu | Giá trị hiện tại (tỷ đồng) |
|---|---|---|---|
| 0 | −5,0 | 1,0000 | −5,000 |
| 1 | 0,0 | 0,9091 | 0,000 |
| 2 | 1,6 | 0,8264 | 1,322 |
| 3 | 2,0 | 0,7513 | 1,503 |
| 4 | 2,0 | 0,6830 | 1,366 |
| 5 | 1,6 | 0,6209 | 0,993 |
| Tổng | 2,2 | 0,184 |
=NPV(G2;C3:C7) trả về 5,184, là tổng giá trị hiện tại của năm 1 đến năm 5. Cộng ô C2 (−5,0) được NPV của dự án khoảng 0,184 tỷ đồng, khớp với tổng cột cuối. Theo các giả định đã nêu, dự án đạt lợi suất cao hơn 10%/năm và tạo thêm khoảng 184 triệu đồng giá trị tính theo tiền ngày 01/03/2026.
Lưu ý: Nếu viết =NPV(G2;C2:C7), Excel coi −5,0 là dòng tiền cuối năm 1 và chiết khấu toàn bộ dự án thêm một năm. Kết quả là 0,168 tỷ đồng, đúng bằng 0,184 / 1,1, thấp hơn khoảng 9%. Lỗi này không báo lỗi gì nên rất khó phát hiện nếu không kiểm tra lại.Suất chiết khấu 10% trong ví dụ là giả định. Trong thực tế, con số này thường lấy theo chi phí vốn của dự án, được trình bày trong bài WACC là gì?
4. Cách tính IRR trong Excel bằng hàm IRR
Công thức là =IRR(C2:C7), cho kết quả 11,18%. Khác với hàm NPV, hàm IRR nhận cả năm 0 trong vùng giá trị, vì IRR là suất chiết khấu làm tổng giá trị hiện tại của mọi dòng tiền (kể cả năm 0) bằng 0.
Hàm IRR không giải phương trình trực tiếp mà thử dần: xuất phát từ giá trị dự đoán (guess; bỏ trống thì Excel dùng 10%), điều chỉnh đến khi kết quả chính xác trong phạm vi 0,00001%, và báo #NUM! nếu 20 lần thử chưa ra. Vì đọc thứ tự ô như thứ tự thời gian, vùng giá trị phải xếp từ năm 0 trở đi và chứa cả số âm lẫn số dương.
Có thể kiểm tra kết quả bằng cách đưa IRR ngược vào công thức NPV: =NPV(IRR(C2:C7);C3:C7)+C2 cho kết quả xấp xỉ 0 (khoảng khi chạy thử). IRR 11,18% cao hơn suất chiết khấu 10%, nhất quán với NPV dương ở mục 3.
IRR trả về theo đúng kỳ của dòng tiền. Dòng tiền theo năm cho IRR theo năm; dòng tiền theo tháng cho IRR theo tháng (xem mục 7.4).
5. Hàm XNPV và XIRR: khi dòng tiền không đều
XNPV và XIRR dùng khi mỗi dòng tiền gắn với một ngày cụ thể, ví dụ giải ngân theo tiến độ, tiền thu theo hợp đồng hay lịch mua bán chứng chỉ quỹ của một nhà đầu tư. Cú pháp cho ví dụ là =XNPV(G2;C2:C7;B2:B7) và =XIRR(C2:C7;B2:B7).
5.1. Công thức và quy ước 365 ngày
Với là dòng tiền thứ , là ngày tương ứng và là ngày đầu tiên, XNPV được tính theo:
XIRR là giá trị làm biểu thức trên bằng 0. Từ công thức có ba hệ quả:
- XNPV chiết khấu mọi dòng tiền về ngày của dòng tiền đầu tiên, nên khác hàm NPV, năm 0 được đưa vào trong hàm và không bị chiết khấu.
- Số ngày luôn chia cho 365, kể cả năm nhuận. Suất chiết khấu nhập vào XNPV là lãi suất năm hiệu dụng (đã tính lãi kép), không phải lãi suất tháng hay quý.
- Ngày được làm tròn xuống thành số nguyên. Theo Microsoft, các ngày sau có thể xếp theo thứ tự bất kỳ nhưng không được sớm hơn ngày đầu tiên, nếu không hàm trả lỗi #NUM!.
Với lịch ngày 01/03 hằng năm như Hình 1, XNPV là 0,183 tỷ đồng và XIRR là 11,17%, chênh rất ít so với NPV 0,184 và IRR 11,18%. Chênh lệch đến từ năm nhuận: từ 01/03/2027 đến 01/03/2028 có 366 ngày (qua ngày 29/02/2028), nên dòng tiền năm 2 trở đi bị chiết khấu thêm 1/365 năm.
5.2. Ví dụ: tiền về trễ 4 tháng
Giả sử dòng tiền năm 2 đến năm 5 của Công ty VQ thực tế về vào ngày 30/06 thay vì 01/03 (2028 đến 2031), tức trễ khoảng 4 tháng mỗi lần. Hàm NPV không phân biệt được hai lịch này vì chỉ đếm thứ tự ô. XNPV thì có:
| Lịch dòng tiền | XNPV ở 10% (tỷ đồng) | XIRR |
|---|---|---|
| Theo kế hoạch, ngày 01/03 hằng năm | 0,183 | 11,17% |
| Tiền về trễ, ngày 30/06 hằng năm | 0,022 | 10,13% |
Chỉ chậm 4 tháng, NPV của dự án giảm từ 0,183 xuống 0,022 tỷ đồng, tức giảm gần 90%, và XIRR chỉ còn cao hơn suất chiết khấu khoảng 0,1 điểm phần trăm. Ví dụ cho thấy với dự án có NPV mỏng, thời điểm nhận tiền quan trọng không kém số tiền. Cơ sở của phép chiết khấu theo thời gian được giải thích trong bài Giá trị thời gian của tiền là gì?
5.3. Chiết khấu về một ngày khác ngày đầu tiên
Vì XNPV chiết khấu về ngày đầu tiên trong danh sách, muốn tính giá trị tại một ngày khác (ví dụ ngày lập báo cáo thẩm định 01/01/2026, trước ngày giải ngân), thêm một dòng đầu có giá trị 0 và ngày 01/01/2026. Với lịch tiền về trễ, kết quả đổi từ 0,0219 thành 0,0216 tỷ đồng, đúng bằng 0,0219 chiết khấu thêm 59 ngày. Hàm NPV không làm được việc này trực tiếp vì luôn chiết khấu theo kỳ nguyên.
6. Hàm MIRR
MIRR (Modified Internal Rate of Return) là biến thể của IRR, dùng lãi suất tài trợ cho dòng tiền ra và lãi suất tái đầu tư cho dòng tiền vào. Với Công ty VQ, =MIRR(C2:C7;G2;G2) (cả hai lãi suất 10%) cho 10,80%; nếu lãi suất tái đầu tư chỉ 8%, =MIRR(C2:C7;10%;8%) cho 10,15%.
MIRR luôn có một kết quả duy nhất, nên hữu ích khi dòng tiền đổi dấu nhiều lần (mục 7.3). Theo Microsoft, MIRR trả lỗi #DIV/0! nếu vùng giá trị không có cả số âm và số dương. Công thức MIRR và cách diễn giải được trình bày trong mục MIRR của bài IRR là gì?
7. Lỗi thường gặp khi tính NPV, IRR trong Excel
Phần lớn lỗi không làm Excel báo lỗi mà cho ra một con số sai trông hợp lý. Hình 2 so sánh NPV của cùng dự án Công ty VQ theo các cách nhập khác nhau.
7.1. Đưa năm 0 vào trong hàm NPV
Đây là lỗi phổ biến nhất, đã nêu ở mục 3.2: =NPV(G2;C2:C7) cho 0,168 thay vì 0,184 tỷ đồng. Với dự án có NPV âm, lỗi này làm NPV bớt âm; với dự án có NPV dương, làm NPV nhỏ đi. Cách tránh: luôn để ô năm 0 ngoài hàm, hoặc dùng XNPV với cột ngày.
7.2. Để trống ô thay vì nhập số 0
Microsoft ghi rõ hàm NPV và IRR bỏ qua ô trống, giá trị logic và chữ trong vùng tham chiếu. Nghĩa là ô trống không được hiểu là số 0: Excel bỏ ô đó đi và kéo mọi dòng tiền phía sau lên sớm một kỳ. Nếu ô C3 (năm 1, dòng tiền bằng 0) để trống, =NPV(G2;C3:C7)+C2 cho 0,703 tỷ đồng thay vì 0,184, và =IRR(C2:C7) cho 16,30% thay vì 11,18%.

Khi chạy thử trên Excel 2024 cho Windows (phiên bản 16.0), XNPV với ô trống trong vùng giá trị trả lỗi #NUM! thay vì im lặng bỏ qua. Trong cùng lần chạy thử, XIRR với ô trống không báo lỗi và cho 11,17%, như thể ô đó bằng 0. Như vậy ba hàm xử lý ô trống theo ba cách khác nhau. Cách tránh chung: nhập số 0 cho mọi kỳ không có dòng tiền.
7.3. Dòng tiền đổi dấu nhiều lần: IRR phụ thuộc giá trị dự đoán
Khi dòng tiền đổi dấu hơn một lần, phương trình NPV bằng 0 có thể có nhiều nghiệm, và hàm IRR chỉ trả về nghiệm mà vòng lặp tìm được trước. Ví dụ dòng tiền −10; 25; −15,4 tỷ đồng (năm 0 đến năm 2) có hai IRR là 10% và 40%. Khi chạy thử trên Excel 2024 cho Windows (phiên bản 16.0), =IRR(...) với giá trị dự đoán mặc định trả về 10%, còn =IRR(...;25%) trả về 40%. Ở suất chiết khấu 5% hay 50%, NPV của dòng tiền này đều âm (−0,16 và −0,18 tỷ đồng), còn ở 20% thì dương (+0,14 tỷ đồng). Vì vậy so một con số IRR do Excel trả về với chi phí vốn không đủ để kết luận.
Cách xử lý: vẽ NPV theo nhiều mức suất chiết khấu để thấy hết các nghiệm, rồi quyết định theo NPV ở đúng chi phí vốn, hoặc dùng MIRR. Bảng NPV theo nhiều suất chiết khấu có thể lập nhanh bằng chức năng Data Table (bảng dữ liệu, nằm trong nhóm What-If Analysis của thẻ Data).
Cách lập bảng này được hướng dẫn trong bài Cách làm bảng độ nhạy bằng Data Table trong Excel
7.4. Dòng tiền theo tháng nhưng đọc IRR như lãi suất năm
IRR và NPV hiểu "kỳ" là khoảng cách giữa hai ô, không biết đó là tháng hay năm. Ví dụ bỏ 100 triệu đồng, nhận 9 triệu đồng mỗi tháng trong 12 tháng: =IRR(...) cho 1,20%, là lãi suất tháng. Quy đổi sang năm phải theo lãi kép:
Nhân 12 cho 14,45%, thấp hơn khoảng 1 điểm phần trăm. Tương tự, khi dùng hàm NPV với dòng tiền tháng, suất chiết khấu phải đổi về lãi suất tháng: , không phải 10%/12. XIRR với cột ngày trả thẳng lãi suất năm, nên tránh được bước quy đổi này.
7.5. Lỗi #NUM! và #VALUE! với XNPV, XIRR
Theo Microsoft, XNPV và XIRR trả lỗi #NUM! khi có ngày sớm hơn ngày đầu tiên hoặc khi số dòng tiền khác số ngày, và trả lỗi #VALUE! khi có ngày không hợp lệ. Nguyên nhân hay gặp là lệch định dạng ngày: dán "30/06/2028" vào máy dùng định dạng tháng/ngày, Excel không nhận ra ngày và giữ nguyên dạng văn bản; còn "01/03/2026" có thể bị hiểu thành ngày 3 tháng 1, không báo lỗi nhưng làm sai kết quả. Kiểm tra bằng cách đổi ô sang định dạng số: ô vẫn giữ nguyên chữ là ngày dạng văn bản; ô hiện số sê-ri thì cần so thêm với số đúng (01/03/2026 là 46082, còn 3 tháng 1 năm 2026 là 46025), hoặc dùng =MONTH(B2) để xem tháng có đúng không. XIRR lặp tối đa 100 lần; nếu vẫn báo #NUM! dù dữ liệu đúng, thử nhập giá trị dự đoán khác.
8. Câu hỏi thường gặp
Nên dùng NPV hay XNPV?
Nếu dòng tiền là dự phóng theo năm và giả định cuối kỳ là chấp nhận được, hàm NPV (cộng năm 0 ngoài hàm) là đủ. Nếu biết ngày nhận, trả tiền cụ thể, hoặc cần chiết khấu về một ngày định giá cụ thể, XNPV phản ánh thời điểm chính xác hơn.
Hàm IRR báo #NUM! thì làm thế nào?
Trước hết kiểm tra vùng giá trị có cả số âm và số dương chưa. Nếu có, thử nhập giá trị dự đoán khác, ví dụ =IRR(C2:C7;-10%) hoặc 30%. Nếu vẫn lỗi, dòng tiền có thể không có IRR nào; khi đó dùng NPV hoặc MIRR.
Kết luận
Hàm NPV của Excel chiết khấu từ kỳ 1, nên khoản đầu tư năm 0 phải cộng ngoài hàm; IRR thì nhận cả năm 0 trong vùng giá trị. XNPV và XIRR chiết khấu theo số ngày thực tế trên năm 365 ngày, về ngày của dòng tiền đầu tiên. Trước khi dùng kết quả, nên kiểm tra ô trống, kỳ của dòng tiền và số nghiệm IRR, vì các lỗi này không làm Excel báo lỗi.
Bài viết mang tính giáo dục, không phải khuyến nghị đầu tư.
Nguồn tham khảo
- Microsoft Support, NPV function: https://support.microsoft.com/en-us/office/npv-function-8672cb67-2576-4d07-b67b-ac28acf2a568
- Microsoft Support, IRR function: https://support.microsoft.com/en-us/office/irr-function-64925eaa-9988-495b-b290-3ad0c163c1bc
- Microsoft Support, XNPV function: https://support.microsoft.com/en-us/office/xnpv-function-1b42bbf6-370f-4532-a0eb-d67c16b664b7
- Microsoft Support, XIRR function: https://support.microsoft.com/en-us/office/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d
- Microsoft Support, MIRR function: https://support.microsoft.com/en-us/office/mirr-function-b020f038-7492-4fb4-93c1-35c345b53524
- Microsoft Support, Làm việc với dòng tiền mặt: Tính NPV và IRR trong Excel: https://support.microsoft.com/vi-VN/Excel/go-with-the-cash-flow-calculate-npv-and-irr-in-excel
Đọc tiếp: NPV là gì? Công thức, ví dụ và cách dùng