XIRR là gì? Công thức XIRR, cách tính trên Excel và Google Sheets｜Ví dụ lợi nhuận chứng khoán Đài Loan, Mỹ

XIRR là công thức tính “khi tiền có vào có ra, trung bình mỗi năm lời bao nhiêu”. Chỉ cần liệt kê ngày và số tiền của từng lần nạp, rút, rồi gõ =XIRR(số tiền; ngày) trong Excel hoặc Google Sheets, máy tính sẽ tính giúp bạn tỷ suất sinh lời bình quân năm.

Tuy vậy, logic ghi dữ liệu khi dùng XIRR cho chứng khoán Mỹ và chứng khoán Đài Loan không giống nhau, nên mới có bài tổng hợp này. Trước hết là bảng tra nhanh cho người đang vội:

Mỗi hàng trong bảng tra nhanh đều có các bước và ví dụ đầy đủ ở phía dưới; nếu đây là lần đầu dùng, chỉ cần làm theo phần “từng bước một” một lần là biết.

Trước hết, Joe giải thích sơ qua XIRR là gì.

XIRR là gì?

XIRR dùng để tính tỷ suất sinh lời của toàn bộ tài sản. Đây là tên một hàm trong Excel; theo trang hướng dẫn của Microsoft, hàm này trả về tỷ suất hoàn vốn nội bộ của một chuỗi dòng tiền không nhất thiết phải định kỳ. Nói đơn giản: tiền vào lúc nào, ra lúc nào cũng được, có ngày là tính được.

Tính XIRR rất dễ, chỉ cần bốn loại dữ liệu sau:

• tổng tài sản đầu kỳ (tài sản tài chính + tiền mặt)
• tổng tài sản cuối kỳ (tài sản tài chính + tiền mặt)
• các lần rút tiền trong kỳ
• các lần nạp tiền

Cách thao tác có thể xem ở trang hướng dẫn hàm XIRR chính thức của Microsoft, trang hướng dẫn XIRR của Google Sheets, hoặc đọc tiếp các ví dụ của Joe bên dưới.

XIRR, IRR, CAGR và TWR khác nhau thế nào

XIRR là tiện nhất; đặt bốn chỉ số cạnh nhau sẽ thấy rõ hơn:

Ba chỉ số đầu là chỉ số “góc nhìn cá nhân”. Nếu thứ bạn đang đọc là số liệu công bố ra bên ngoài của quỹ đầu tư, công ty tư vấn đầu tư hay quỹ tư nhân, họ không dùng XIRR mà dùng TWR, một chỉ số cố ý tách bỏ hành vi của nhà đầu tư để chỉ đánh giá bản thân chiến lược vận hành danh mục.

XIRR càng cao càng tốt phải không?

Đúng vậy.

Có điều đừng chỉ so với chính mình, hãy nhớ so với thị trường, nếu không con số đó chẳng có ý nghĩa. Mốc tham chiếu dài hạn của thị trường có thể xem bảng đối chiếu trong bài về CAGR, ví dụ S&P 500 của Mỹ dài hạn khoảng 10%, 0050 của Đài Loan khoảng 8–9%.

Ngoài ra cần chú ý, XIRR “quy đổi lợi nhuận ra một năm”. Thời gian nắm giữ càng ngắn, con số càng bị phóng đại:

XIRR của hai trường hợp gần như bằng nhau, nhưng số tiền thực sự kiếm được chênh nhau 8 lần. Khi giữ chưa đến một năm, nhớ xem cả “tổng cộng đã lời bao nhiêu”, đừng chỉ nhìn XIRR.

Vì sao Joe dùng XIRR

Vì cần ghi chép ít, lại có thể ghi ngay trên Excel hoặc Google Sheets, vừa nhanh vừa tiện.

Thời điểm Joe nạp, rút tiền rất không cố định, mà XIRR chỉ cần có ngày là đủ, so với các công thức khác thì linh hoạt và tiện hơn.

Công thức XIRR: viết hàm thế nào, phía sau đang tính gì

Trong Excel và Google Sheets, hàm được viết theo cùng một thứ tự, số tiền trước, ngày sau (tên hàm ở cả hai đều là XIRR):

• Excel: =XIRR(values; dates; [guess])
• Google Sheets: =XIRR(khoản_chi_trả_của_dòng_tiền; ngày_tương_ứng_với_dòng_tiền; [lãi_suất_dự_đoán])

Tên đối số của Excel lấy theo trang hướng dẫn của Microsoft (giữ tiếng Anh), tên đối số của Google Sheets lấy theo trang hướng dẫn tiếng Việt của Google. Ô thứ ba là “giá trị ước đoán”, có thể bỏ trống; bỏ trống thì cả hai phần mềm đều mặc định 10%. Ghi chép thông thường không cần đến, chỉ khi tính không ra mới cần (xem bảng đối chiếu lỗi bên dưới). Các công thức trong bài dùng dấu chấm phẩy (;) để ngăn cách đối số, giống trong hình; nếu bảng tính của bạn dùng dấu phẩy thì cứ đổi thành dấu phẩy.

Thứ phía sau đang tính là: tìm một lãi suất năm r, chiết khấu từng khoản tiền về ngày đầu tiên theo “số ngày đã qua kể từ khoản đầu tiên ÷ 365”, sao cho cộng tất cả lại vừa đúng bằng 0.

Tổng của [mỗi khoản tiền ÷ (1 + r)^(số ngày tính từ khoản đầu tiên ÷ 365)] = 0

Giá trị r này chính là XIRR. Lấy ví dụ A: nạp 1.000, 364 ngày sau thành 1.800, thay vào là −1.000 + 1.800 ÷ (1 + r)^(364 ÷ 365) = 0, giải ra r khoảng 80,29%. Khi chỉ có hai khoản thì còn khai căn trực tiếp được, dòng tiền nhiều lên thì không giải trực tiếp được nữa; Excel dùng cách thử tính lặp đi lặp lại để tiến dần tới đáp án, nên trên thực tế cứ giao cho hàm hoặc máy tính trực tuyến.

Tính XIRR bằng Excel hoặc Google Sheets: từng bước một

Bước 1: ngày đặt ở cột A, số tiền đặt ở cột B

Khi nhập dữ liệu có ba điều cần chú ý:

• Hàng dữ liệu đầu tiên đặt khoản sớm nhất. Thứ tự phía sau có thể lộn xộn, nhưng không được có ngày nào sớm hơn hàng đầu tiên, nếu không sẽ báo lỗi #NUM!.
• Ngày phải là “ngày” thật, không được là văn bản. Sau khi nhập, nếu ô tự động căn phải thì thường là ngày; căn trái thì phần lớn đã bị coi là văn bản, có thể nhập lại bằng =DATE(2022;11;15).
• Số tiền chỉ gõ số và dấu trừ, đừng gõ kiểu có chữ như “1.800 đồng”.

Bước 2: gõ công thức vào một ô trống

Ở C2 (hoặc bất kỳ ô trống nào) gõ =XIRR(B2:B3; A2:A3). Chọn số tiền trước, ngày sau, hai vùng phải có cùng số hàng.

Sau này mỗi lần ghi thêm một khoản, kéo dài cả hai vùng cùng lúc, ví dụ ba khoản thì đổi thành =XIRR(B2:B4; A2:A4).

Bước 3: nhấn Enter, rồi đổi sang phần trăm

Kết quả vừa tính ra sẽ là số thập phân kiểu 0,8029, phải đổi sang phần trăm mới dễ đọc:

• Excel: ở tab Home (tên trong Excel bản tiếng Anh), bấm nút “%”. Mặc định không có chữ số thập phân; khi thấy 80% thì bấm nút Increase Decimal (tăng số chữ số thập phân) hai lần, sẽ thành 80,29%.
• Google Sheets: bấm nút “%” trên thanh công cụ (định dạng phần trăm), sẽ hiện ngay 80,29%.

Số dương hay số âm? Quy tắc dấu của XIRR

XIRR nhìn tài khoản từ phía “túi tiền của bạn”: tiền từ túi bỏ vào tài khoản là số âm, tiền từ tài khoản quay về túi là số dương.

Lỗi hay gặp nhất là gõ cả khoản nạp thành số dương. Khi tất cả đều là số dương, XIRR không tìm ra đáp án và trả về ngay #NUM!:

Tính không ra? Bảng đối chiếu lỗi XIRR thường gặp

Bốn trường hợp #NUM! đầu và #VALUE! do ngày không hợp lệ đều là các trường hợp được liệt kê trong trang hướng dẫn chính thức của Microsoft; ngày dạng văn bản và số tiền có kèm chữ thì Microsoft không ghi rõ sẽ ra lỗi nào, chỉ nhắc ngày dạng văn bản có thể gây trục trặc, nên an toàn nhất là luôn đổi thành ngày thật và số thuần. Khi gặp lỗi, cứ dò từ hàng đầu tiên của bảng xuống, thường hai hàng đầu đã tìm ra nguyên nhân.

Ví dụ tính XIRR với tài khoản chứng khoán Mỹ

Dùng XIRR để tính lợi nhuận đầu tư chứng khoán Mỹ vừa đơn giản vừa tiện, vì theo cách vận hành ở Mỹ, tài khoản chứng khoán và tài khoản tiền mặt là một.

Ví dụ, khi Joe dùng Firstrade hay Charles Schwab, chỉ cần nhìn tổng tài sản là đã cộng sẵn sức mua bằng tiền mặt và giá trị thị trường của cổ phiếu.

Cấu trúc này khiến việc dùng XIRR rất thuận tiện.

Ví dụ XIRR A: nạp 1.000 vào tài khoản, mua cổ phiếu toàn bộ, một năm sau bán hết

Các bước

• 2022-11-15 nạp 1.000 vào tài khoản, dùng toàn bộ để mua cổ phiếu
  • Giả sử ngày hôm đó cả 1.000 đều đã mua cổ phiếu, không còn tiền mặt dư
• 2023-11-14 bán toàn bộ cổ phiếu, giá trị thị trường là 1.800
• Hàm XIRR sẽ có dạng: =XIRR(B2:B3; A2:A3)
  • Số tiền là −1.000 và 1.800
  • Ngày là hai ngày nêu trên

Bảng tính trông giống như hình ở bước 2 phía trên, kết quả là 80,29%.

Ví dụ XIRR B đến D: tiền mặt chưa mua hết, cổ tức nằm lại trong tài khoản, tái đầu tư cổ tức, đều không cần ghi riêng

Ba ví dụ tiếp theo đều là: ngày 2022-11-15 nạp 1.000 vào tài khoản, dùng 700 mua cổ phiếu, để 300 tiền gốc nằm yên, đến 2023-11-14 bán hết. Để phân biệt với số liệu của ví dụ A, ở đây ta giả sử giá trị thị trường cuối cùng giảm từ 1.800 xuống 1.500.

Cả ba câu đều phán đoán giống nhau: khoản tiền này có rời khỏi tài khoản không? Không thì không cần ghi. Vì vậy cả ba đều chỉ ghi hai khoản: 2022-11-15 −1.000 và 2023-11-14 +1.500, XIRR đều là 50,17%. So với ví dụ A, kết quả thành 50,17% chỉ vì giá trị thị trường đã thành 1.500.

Trừ khi giá trị thị trường lúc tất toán cuối cùng thay đổi, bạn hoàn toàn không cần ghi thêm bất cứ điều gì; đây là điểm rất tiện khi dùng XIRR với chứng khoán Mỹ.

Ví dụ XIRR E: nạp 1.000, dùng 700 mua cổ phiếu, để 300 tiền gốc nằm yên, giữa kỳ nhận cổ tức tiền mặt 50 theo chương trình tái đầu tư cổ tức, giữa chừng rút 300 đi mua Starbucks, một năm sau bán hết

Trường hợp này khác hẳn, vì đã rút 300 đi mua Starbucks, tức là có dòng tiền rời khỏi tài khoản.

Các bước

• 2022-11-15 nạp 1.000 vào tài khoản
  • 700 mua cổ phiếu, còn 300 tiền mặt
• 2023-04-21 rút 300 đi mua Starbucks
  • Vì đã rút 300 tiền mặt ra khỏi tài khoản, dòng tiền ở đây ghi là dương 300
  • Số cổ phiếu còn lại tiếp tục giữ, đến cuối bán theo giá thị trường
• 2023-08-01 nhận cổ tức tiền mặt 50, tự động tái đầu tư
  • Giống ví dụ D, không rút ra khỏi tài khoản, không cần ghi
• 2023-11-14 bán toàn bộ cổ phiếu, giá trị thị trường là 1.500
• Hàm XIRR sẽ có dạng: =XIRR(B2:B4; A2:A4)
  • Số tiền là −1.000, 300 và 1.500
  • Ngày là ba ngày nêu trên (ngày nhận cổ tức không cần liệt kê)
• XIRR tăng lên vì sau khi rút 300 tiền mặt, giá trị cuối kỳ vẫn giữ ở 1.500
  • Nghĩa là hiệu quả tốt hơn, nên XIRR đương nhiên cao hơn

Ví dụ tính XIRR với chứng khoán Đài Loan

Khác biệt lớn nhất giữa chứng khoán Đài Loan và chứng khoán Mỹ nằm ở cấu trúc tài khoản: ở các công ty chứng khoán Đài Loan, tài khoản chứng khoán chỉ chứa cổ phiếu, còn tiền nằm ở một tài khoản ngân hàng riêng gọi là “tài khoản thanh toán” (交割戶). Khi mua cổ phiếu, công ty chứng khoán trừ tiền từ tài khoản thanh toán; tiền bán cổ phiếu quay về tài khoản thanh toán, cổ tức tiền mặt thông thường cũng được chuyển vào tài khoản thanh toán.

Vì vậy khi dùng XIRR cho chứng khoán Đài Loan, bước đầu tiên là quyết định “tài khoản” tính đến đâu. Có hai cách làm phổ biến:

• Tài khoản thanh toán chỉ dùng để đầu tư: coi “giá trị thị trường cổ phiếu + tiền trong tài khoản thanh toán” là một tài khoản, khi đó giống hệt các ví dụ chứng khoán Mỹ ở trên, chỉ ghi khi chuyển tiền từ bên ngoài vào tài khoản thanh toán, hoặc chuyển tiền từ tài khoản thanh toán ra để tiêu.
• Tài khoản thanh toán đồng thời là tài khoản nhận lương hoặc tài khoản sinh hoạt: tiền ra vào lẫn lộn với nhau, thì đổi sang chỉ xét riêng cổ phiếu. Mỗi lần mua ghi số âm (số tiền mua đã gồm phí giao dịch); mỗi lần bán (ghi số tiền thực nhận sau khi trừ phí giao dịch và thuế giao dịch chứng khoán) và mỗi lần cổ tức tiền mặt về đều ghi số dương; cuối kỳ ghi giá trị thị trường của cổ phiếu chưa bán là số dương.

Ví dụ XIRR F: tài khoản thanh toán ở Đài Loan dùng chung với tiền sinh hoạt, giữa kỳ nhận một lần cổ tức tiền mặt

Các bước

• 2023-01-10 mua một quỹ ETF, tổng cộng 120.000 (đã gồm phí giao dịch)
  • Dòng tiền ghi −120.000
• 2023-07-20 cổ tức tiền mặt 2.000 về tài khoản thanh toán, sau đó được tiêu cùng tiền sinh hoạt
  • Tiền đã rời khỏi tài khoản “cổ phiếu”, ghi +2.000
• 2024-01-10 cổ phiếu vẫn chưa bán, giá trị thị trường ngày hôm đó là 140.000
  • Giá trị thị trường cuối kỳ ghi +140.000
• Hàm XIRR sẽ có dạng: =XIRR(B2:B4; A2:A4)
  • Số tiền là −120.000, 2.000 và 140.000
  • Ngày là ba ngày nêu trên

Ba dòng tiền tính ra XIRR khoảng 18,47%. Nếu quên ghi khoản cổ tức 2.000, chỉ tính hai khoản −120.000 và 140.000, XIRR sẽ thành khoảng 16,67%, tức là tỷ suất sinh lời bị đánh giá thấp gần 2 điểm phần trăm. Khi tài khoản thanh toán dùng lẫn với tiền sinh hoạt thì nhất định phải ghi cổ tức tiền mặt; còn cổ tức bằng cổ phiếu thì không cần ghi riêng, vì nó phản ánh thẳng vào giá trị thị trường cổ phiếu cuối kỳ.

Một bảng tổng hợp: những việc nào phải ghi thành dòng tiền

Trong bảng, “Mỹ” là tài khoản có tiền mặt và cổ phiếu chung một chỗ; “Đài Loan (chung)” là tài khoản thanh toán dùng chung cho sinh hoạt, chỉ xét phần cổ phiếu.

Thật ra quy tắc chỉ có một: chỉ ghi một khoản khi “tiền ra vào tài khoản mà bạn định nghĩa”, cuối kỳ chốt lại bằng tổng giá trị thị trường. Các ví dụ từ A đến F đều là áp dụng quy tắc này; ai chỉ dùng tài khoản thanh toán để đầu tư thì cứ ghi theo cột chứng khoán Mỹ là được.

Không muốn tính tay? Dùng máy tính XIRR

Nếu không muốn tự mở Excel để dùng hàm XIRR, Joe có làm một công cụ trực tuyến: máy tính XIRR. Giao diện hiện chỉ có tiếng Trung phồn thể, nhưng ngày tháng và con số vẫn nhập bình thường. Dán ngày và số tiền của từng dòng tiền vào (tiền nạp vào ghi số âm, giá trị thị trường cuối kỳ ghi số dương), công cụ sẽ tính ngay tỷ suất sinh lời bình quân năm của bạn, không cần nhớ công thức hay sắp xếp định dạng dữ liệu. Các bài tính chứng khoán Mỹ và Đài Loan ở trên đều có thể dùng công cụ này để đối chiếu đáp án.

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

XIRR là gì?

XIRR là hàm của Excel và Google Sheets, dùng để tính “khi tiền ra vào vào những thời điểm không cố định, trung bình mỗi năm lời bao nhiêu”. Nó là một dạng tỷ suất hoàn vốn nội bộ (IRR), khác ở chỗ không cần các kỳ cách đều nhau: mỗi lần nạp, rút tiền chỉ cần có ngày và số tiền là tính được, rất hợp để tính tỷ suất sinh lời bình quân năm thực tế của tài khoản của chính bạn.

Công thức XIRR viết thế nào?

Cách viết là =XIRR(vùng số tiền; vùng ngày). Ví dụ ngày đặt ở A2:A3, số tiền đặt ở B2:B3 thì gõ =XIRR(B2:B3; A2:A3). Ô thứ ba có thể điền giá trị ước đoán, không điền thì mặc định là 10%.

Nguyên lý phía sau là tìm một lãi suất năm sao cho mỗi khoản tiền, khi chiết khấu về ngày đầu tiên theo “số ngày tính từ khoản đầu tiên ÷ 365”, cộng lại bằng 0; lãi suất đó chính là XIRR.

XIRR và IRR khác nhau ở đâu?

IRR giả định các dòng tiền cách nhau đều đặn (ví dụ mỗi năm một lần), chỉ xét đó là kỳ thứ mấy; XIRR thì quy đổi theo ngày thực tế, một năm tính 365 ngày, nên tiền ra vào lúc nào cũng được. Thời điểm nạp, rút tiền của tài khoản cá nhân thường không cố định, dùng XIRR sẽ chính xác hơn.

Số tiền trong XIRR nhập số dương hay số âm?

Tiền từ túi bạn bỏ vào tài khoản (đầu tư, nạp tiền) ghi số âm; tiền từ tài khoản quay về túi bạn (rút tiền, tiền bán về) và tổng giá trị tài khoản cuối kỳ ghi số dương. Cổ tức, tiền lãi nằm lại trong tài khoản thì không cần ghi.

Vì sao XIRR báo lỗi #NUM!?

Hay gặp nhất là các số tiền đều là số dương (hoặc đều là số âm); XIRR cần ít nhất một số âm và một số dương. Các nguyên nhân khác: có ngày sớm hơn ngày ở hàng đầu tiên, số ô số tiền và số ô ngày không bằng nhau, hoặc thử tính 100 lần vẫn không tìm ra kết quả (có thể điền một giá trị ước đoán gần đúng vào ô thứ ba).

Nếu lỗi hiện ra là #VALUE!, phần lớn là do ngày không hợp lệ, ví dụ gõ một ngày không tồn tại; ngày bị lưu thành văn bản cũng có thể gây lỗi, Microsoft khuyên nhập bằng DATE(năm; tháng; ngày).

Cổ tức tiền mặt có cần ghi vào XIRR không?

Tùy cổ tức có rời khỏi tài khoản mà bạn định nghĩa hay không. Với tài khoản chứng khoán Mỹ, cổ tức nằm lại trong tài khoản hoặc được tái đầu tư thì đều không cần ghi, vì giá trị cuối kỳ đã tính vào rồi; với tài khoản thanh toán chứng khoán ở Đài Loan, nếu dùng chung với tiền sinh hoạt và chỉ xét riêng cổ phiếu, thì mỗi lần cổ tức tiền mặt về phải ghi một số dương, sót sẽ làm tỷ suất sinh lời bị đánh giá thấp (ví dụ F trong bài: 18,47% thành 16,67%). Cổ tức bằng cổ phiếu thì không cần ghi riêng, nó đã phản ánh trong giá trị thị trường cuối kỳ.

Chọn XIRR hay CAGR?

Khi chỉ có một khoản đầu tư, giữa chừng không nạp thêm cũng không rút ra, hai cách tính ra gần như giống nhau, dùng CAGR là đủ (giải thích về CAGR). Khi giữa chừng có đầu tư định kỳ, nạp thêm hoặc rút tiền, CAGR sẽ bị sai lệch, phải dùng XIRR.

Vì sao mới để vài tháng mà XIRR cao đến giật mình?

Vì XIRR quy đổi lợi nhuận ra “một năm”. 1.000 để 2 tháng (ví dụ từ 1/1 đến 1/3, tổng cộng 59 ngày) thành 1.100, chỉ lời 10%, nhưng XIRR khoảng 80,33%, gần như bằng kết quả để 1 năm lời 80% (khoảng 80,29%). Khi giữ chưa đến một năm, nhớ xem cùng lúc thực tế đã lời bao nhiêu.

XIRR bao nhiêu là tốt?

Phải so với thị trường mới có ý nghĩa, không thể chỉ so với chính mình. Theo số ước lượng dài hạn đã tính cổ tức, S&P 500 của Mỹ khoảng 10%, 0050 của Đài Loan khoảng 8–9% (xem bảng đối chiếu trong bài về CAGR). XIRR cũng có thể là số âm, ví dụ 1.000 sau một năm chỉ còn 600, XIRR khoảng −40%.

Trang tổng hợp loạt bài “Báo cáo tài sản Đài Loan của Joe”

Joe tổng hợp những gì mình thấy và học được về tài sản thành một loạt tư liệu, gọi là loạt bài “Bản đồ tài sản Đài Loan của Joe”.

Không phải để đưa ra đáp án, mà để mọi người biết mình đang đứng ở đâu; mong những tư liệu này giúp bạn hiểu rõ bản thân hơn.

Loạt bài “Báo cáo tài sản Đài Loan của Joe” có trang mục lục tại đây (tiếng Trung).

Đây là ghi chép nghiên cứu cá nhân, không phải lời khuyên đầu tư.

Từ khóa: XIRR, XIRR là gì, công thức XIRR, hàm XIRR, XIRR trong Excel, XIRR trong Google Sheets, tỷ suất sinh lời bình quân năm