Hàm XIRR của Excel để tính IRR cho các luồng tiền không định kỳ

Khi bạn phải đối mặt với một quyết định thâm dụng vốn, việc tính toán tỷ lệ hoàn vốn nội bộ là mong muốn bởi vì nó cho phép bạn so sánh lợi nhuận dự kiến ​​cho các khoản đầu tư khác nhau và đưa ra cơ sở định lượng để đưa ra quyết định.

Trong hướng dẫn trước của chúng tôi, chúng tôi đã xem xét cách tính tỷ lệ hoàn vốn nội bộ với Hàm IRR của Excel. Phương pháp đó nhanh chóng và đơn giản, nhưng nó có một hạn chế thiết yếu – chức năng IRR giả định rằng tất cả các dòng tiền xảy ra ở các khoảng thời gian bằng nhau như hàng tháng hoặc hàng năm. Tuy nhiên, trong các tình huống thực tế, dòng tiền và dòng tiền thường xảy ra theo chu kỳ không đều. Rất may, Microsoft Excel có một chức năng khác để tìm IRR trong những trường hợp như vậy và hướng dẫn này sẽ hướng dẫn bạn cách sử dụng nó.

Hàm XIRR trong Excel

Hàm XIRR trong Excel trả về tỷ lệ hoàn vốn nội bộ cho một loạt các luồng tiền có thể hoặc không thể định kỳ.

Hàm được giới thiệu trong Excel 2007 và có sẵn trong tất cả các phiên bản sau của Excel 2010, Excel 2013, Excel 2016, Excel 2019 và Excel cho Office 365.

Cú pháp của hàm XIRR như sau:

XIRR (giá trị, ngày, [guess])

Ở đâu:

• Giá trị (bắt buộc) – một mảng hoặc một phạm vi ô đại diện cho một loạt các luồng vào và ra.
• ngày (bắt buộc) – ngày tương ứng với dòng tiền. Ngày có thể xảy ra theo bất kỳ thứ tự nào, nhưng ngày đầu tư ban đầu phải là đầu tiên trong mảng.
• Phỏng đoán (tùy chọn) – IRR dự kiến ​​được cung cấp dưới dạng phần trăm hoặc số thập phân. Nếu bỏ qua, Excel sẽ sử dụng tỷ lệ mặc định là 0,1 (10%).

Ví dụ: để tính IRR cho chuỗi dòng tiền trong A2: A5 và ngày ở B2: B5, bạn sẽ sử dụng công thức này:

=XIRR(A2:A5, B2:B5)

Tiền boa. Để kết quả hiển thị chính xác, vui lòng đảm bảo Tỷ lệ phần trăm định dạng được đặt cho ô công thức.

6 điều bạn nên biết về chức năng XIRR

Các ghi chú sau đây sẽ giúp bạn hiểu rõ hơn về cơ chế bên trong của hàm XIRR và sử dụng nó trong bảng tính của bạn một cách hiệu quả nhất.

1. XIRR trong Excel được thiết kế để tính tỷ lệ hoàn vốn nội bộ cho các luồng tiền với thời gian không bằng nhau. Đối với các luồng tiền định kỳ có ngày thanh toán chính xác không xác định, bạn có thể sử dụng Chức năng IRR.
2. Phạm vi của các giá trị phải chứa ít nhất một giá trị dương (thu nhập) và một giá trị âm (thanh toán đi).
3. Nếu giá trị đầu tiên là một khoản chi (đầu tư ban đầu), thì nó phải được biểu thị bằng một số âm. Đầu tư ban đầu không được chiết khấu; các khoản thanh toán tiếp theo được đưa trở lại vào ngày của dòng tiền đầu tiên và được chiết khấu dựa trên một năm 365 ngày.
4. Tất cả các ngày được cắt ngắn thành số nguyên, có nghĩa là phần phân số của một ngày đại diện cho thời gian được loại bỏ.
5. Ngày phải là ngày Excel hợp lệ được nhập dưới dạng tham chiếu đến các ô có chứa ngày hoặc kết quả của các công thức, chẳng hạn như Hàm DATE. Nếu ngày là đầu vào ở định dạng văn bản, vấn đề có thể xảy ra.
6. XIRR trong Excel luôn trả về IRR hàng năm ngay cả khi tính toán dòng tiền hàng tháng hoặc hàng tuần.

Tính toán XIRR trong Excel

Hàm XIRR trong Excel sử dụng phương pháp thử và sai để tìm tỷ lệ thỏa mãn phương trình này:

Ở đâu:

• P – dòng tiền (thanh toán)
• d – ngày
• i – số kỳ
• n – tổng thời gian

Bắt đầu với dự đoán nếu được cung cấp hoặc với 10% mặc định nếu không, Excel sẽ thực hiện các bước lặp để đi đến kết quả với độ chính xác 0,000001%. Nếu sau 100 lần thử, tỷ lệ chính xác không được tìm thấy, #NUM! lỗi được trả lại.

Để kiểm tra tính hợp lệ của phương trình này, chúng ta hãy kiểm tra nó dựa trên kết quả của công thức XIRR. Để đơn giản hóa tính toán của chúng tôi, chúng tôi sẽ sử dụng như sau công thức mảng (xin nhớ rằng bất kỳ công thức mảng nào cũng phải được hoàn thành bằng cách nhấn Ctrl + Shift + Enter):

=SUM(A2:A5/((1+$E$1)^((B2:B5-$B$2)/365)))

Ở đâu:

• A2: A5 là dòng tiền
• B2: B5 là ngày
• E1 là tỷ lệ được trả về bởi XIRR

Như thể hiện trong ảnh chụp màn hình bên dưới, kết quả rất gần với không. QED 🙂

Xem thêm:

Tìm kiếm mục tiêu trong Excel

Cách tùy chỉnh hoặc dừng Tự động sửa lỗi trong Excel

Tìm các giá trị hàng đầu 3, 5, 10 trong cột hoặc hàng

Cách tính phần trăm phương sai trong Excel

Cách tính XIRR trong Excel – ví dụ về công thức

Dưới đây là một vài ví dụ minh họa cách sử dụng phổ biến của hàm XIRR trong Excel.

Công thức XIRR cơ bản trong Excel

Giả sử bạn đã đầu tư 1.000 đô la trong năm 2017 và hy vọng sẽ nhận được một số lợi nhuận trong 6 năm tới. Để tìm tỷ lệ hoàn vốn nội bộ cho khoản đầu tư này, hãy sử dụng công thức này:

=XIRR(A2:A8, B2:B8)

Trong đó A2: A8 là các luồng tiền và B2: B8 là các ngày tương ứng với các luồng tiền:

Để đánh giá lợi nhuận của khoản đầu tư này, hãy so sánh sản lượng XIRR với công ty của bạn chi phí vốn bình quân gia quyền hoặc là lợi tức tối thiểu. Nếu tỷ lệ hoàn vốn cao hơn chi phí vốn, dự án có thể được coi là một khoản đầu tư tốt.

Khi so sánh một số lựa chọn đầu tư, xin nhớ rằng tỷ lệ hoàn vốn dự kiến ​​chỉ là một trong những yếu tố mà bạn nên ước tính trước khi đưa ra quyết định. Để biết thêm thông tin, xin vui lòng xem Tỷ lệ hoàn vốn nội bộ (IRR) là gì?

Hình thức hoàn chỉnh của hàm Excel XIRR

Trong trường hợp bạn biết loại lợi nhuận nào bạn đang mong đợi từ khoản đầu tư này hoặc khoản đầu tư đó, bạn có thể sử dụng kỳ vọng của mình như một dự đoán. Điều này đặc biệt hữu ích khi công thức XIRR chính xác rõ ràng ném #NUM! lỗi.

Đối với đầu vào dữ liệu được hiển thị bên dưới, công thức XIRR mà không cần đoán sẽ trả về lỗi:

=XIRR(A2:A7, B2:B7)

Tỷ lệ hoàn vốn dự kiến ​​(-20%) được đưa vào phỏng đoán đối số giúp Excel đi đến kết quả:

=XIRR(A2:A7, B2:B7, -20%)

Cách tính XIRR cho dòng tiền hàng tháng

Để bắt đầu, hãy nhớ điều này – bất kể dòng tiền nào bạn đang tính toán, hàm XIRR của Excel tạo ra một tỷ suất lợi nhuận hàng năm.

Để chắc chắn về điều này, hãy tìm IRR cho cùng một dòng tiền (A2: A8) xảy ra hàng tháng và hàng năm (ngày nằm trong B2: B8):

=XIRR(A2:A8, B2:B8)

Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, IRR tăng từ 7,68% trong trường hợp dòng tiền hàng năm lên khoảng 145% cho dòng tiền hàng tháng! Sự khác biệt dường như quá cao để được chứng minh chỉ bằng giá trị thời gian của yếu tố tiền bạc:

Để tìm một gần đúng XIRR hàng tháng, bạn có thể sử dụng phép tính dưới đây, trong đó E1 là kết quả của công thức XIRR thông thường:

=(1+E1)^(1/12)-1

Hoặc bạn có thể nhúng XIRR trực tiếp vào phương trình:

=(1+XIRR(A2:A8,B2:B8))^(1/12)-1

Để kiểm tra bổ sung, hãy sử dụng Chức năng IRR trên cùng một dòng tiền. Xin lưu ý rằng IRR cũng sẽ tính toán một tỷ lệ gần đúng bởi vì nó giả sử tất cả các khoảng thời gian là bằng nhau:

=IRR(A2:A8)

Theo kết quả của những tính toán này, chúng tôi nhận được XIRR hàng tháng là 7,77%, rất gần với 7,68% được tạo ra bởi công thức IRR:

Kết luận: nếu bạn đang tìm kiếm IRR hàng năm cho các luồng tiền hàng tháng, hãy sử dụng chức năng XIRR ở dạng thuần túy; để có được IRR hàng tháng, hãy áp dụng điều chỉnh được mô tả ở trên.

Mẫu Excel XIRR

Để nhanh chóng nhận được tỷ lệ hoàn vốn nội bộ cho các dự án khác nhau, bạn có thể tạo một máy tính XIRR linh hoạt cho Excel. Đây là cách thực hiện:

1. Nhập dòng tiền và ngày vào hai cột riêng lẻ (A và B trong ví dụ này).
2. Tạo hai phạm vi xác định động, được đặt tên Tiền mặt và ngày. Về mặt kỹ thuật, đó sẽ được đặt tên theo công thức:Tiền mặt=OFFSET(Sheet1!$A$2,0,0,COUNT(Sheet1!$A:$A),1)Ngày:=OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B:$B),1)

   Ở đâu Tờ1 là tên của bảng tính của bạn, A2 là dòng tiền đầu tiên và B2 là ngày đầu tiên.

3. Cung cấp các tên được xác định động mà bạn đã tạo cho công thức XIRR:

=XIRR(Cash_flows, Dates)

Làm xong! Bây giờ bạn có thể thêm hoặc xóa bao nhiêu dòng tiền bạn muốn và công thức XIRR động của bạn sẽ tính toán lại cho phù hợp:

Tải xuống mẫu XIRR Excel

XIRR so với IRR trong Excel

Sự khác biệt chính giữa các hàm Excel XIRR và IRR là:

• IRR giả định rằng tất cả các giai đoạn trong một loạt các dòng tiền đều bằng nhau. Bạn sử dụng chức năng này để tìm tỷ lệ hoàn vốn nội bộ cho các dòng tiền định kỳ như hàng tháng, hàng quý hoặc hàng năm.
• XIRR cho phép bạn chỉ định một ngày cho từng dòng tiền riêng lẻ. Vì vậy, sử dụng chức năng này để tính IRR cho các luồng tiền không nhất thiết phải định kỳ.

Nói chung, nếu bạn biết ngày chính xác của các khoản thanh toán, nên sử dụng XIRR vì nó cung cấp độ chính xác tính toán tốt hơn.

Ví dụ: hãy so sánh kết quả của IRR và XIRR cho cùng một dòng tiền:

Nếu tất cả các khoản thanh toán xảy ra tại khoảng thời gian đều đặn, các hàm trả về kết quả rất gần:

Nếu thời điểm của dòng tiền là không đồng đều, sự khác biệt giữa các kết quả là khá đáng kể:

XIRR và XNPV trong Excel

XIRR liên quan chặt chẽ đến chức năng XNPV vì kết quả của XIRR là tỷ lệ chiết khấu dẫn đến giá trị hiện tại ròng bằng không. Nói cách khác, XIRR là XNPV = 0. Ví dụ sau đây cho thấy mối quan hệ giữa XIRR và XNPV trong Excel.

Giả sử bạn đang xem xét một số cơ hội đầu tư và muốn kiểm tra cả giá trị hiện tại ròng và tỷ lệ hoàn vốn nội bộ của khoản đầu tư này.

Với các dòng tiền trong A2: A5, ngày ở B2: B5 và tỷ lệ chiết khấu trong E1, như sau Công thức XNPV sẽ cung cấp cho bạn giá trị hiện tại ròng của dòng tiền trong tương lai:

=XNPV(E1, A2:A5, B2:B5)

NPV dương cho thấy dự án có lợi nhuận:

Bây giờ, hãy tìm tỷ lệ chiết khấu nào sẽ làm cho giá trị hiện tại ròng bằng không. Đối với điều này, chúng tôi sử dụng chức năng XIRR:

=XIRR(A2:A5, B2:B5)

Để kiểm tra xem tốc độ do XIRR tạo ra có thực sự dẫn đến NPV không, hãy đặt nó vào tỷ lệ đối số của công thức XNPV của bạn:

=XNPV(E4, A2:A5, B2:B5)

Hoặc nhúng toàn bộ chức năng XIRR:

=XNPV(XIRR(A2:A5, B2:B5), A2:A5, B2:B5)

Đúng, XNPV làm tròn đến 2 chữ số thập phân không bằng 0:

Để hiển thị giá trị NPV chính xác, chọn hiển thị nhiều vị trí thập phân hơn hoặc áp dụng Định dạng khoa học đến ô XNPV. Điều đó sẽ tạo ra một kết quả tương tự như thế này:

Nếu bạn không quen thuộc với ký hiệu khoa học, hãy thực hiện phép tính sau để chuyển đổi nó thành số thập phân:

1.11E-05 = 1.11*10^-5 = 0.0000111

Hàm XIRR của Excel không hoạt động

Nếu bạn gặp phải sự cố với hàm XNPV trong Excel, dưới đây là những điểm chính cần kiểm tra.

#NUM! lỗi

Lỗi #NUM có thể xảy ra vì các lý do sau:

• Các giá trị và ngày phạm vi có độ dài khác nhau (số lượng cột hoặc hàng khác nhau).
• Các giá trị mảng không chứa ít nhất một giá trị dương và một giá trị âm.
• Bất kỳ ngày nào sau đó đều sớm hơn ngày đầu tiên.
• Một kết quả không được tìm thấy sau 100 lần lặp. Trong trường hợp này, hãy thử một dự đoán khác.

#GIÁ TRỊ! lỗi

Lỗi #VALUE có thể do các nguyên nhân sau:

• Bất kỳ được cung cấp giá trị không phải là số.
• Một số ngày được cung cấp không thể được xác định là ngày Excel hợp lệ.

Đó là cách bạn tính XIRR trong Excel. Để xem kỹ hơn các công thức được thảo luận trong hướng dẫn này, bạn có thể tải xuống sổ làm việc mẫu của chúng tôi để Hàm XIRR của Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!