Hướng dẫn cho thấy cách sử dụng XIRR trong Excel để tính tỷ lệ hoàn vốn nội bộ (IRR) cho các luồng tiền với thời gian không đều và cách tạo máy tính XIRR của riêng bạn.

Khi bạn phải đương đầu 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à 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à mang ra nền tảng định lượng để mang 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 – tính năng IRR giả định rằng toàn bộ 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 tiễn, 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 tính 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 Excel XIRR 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 toàn bộ 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à trước hết trong mảng.
  • Phỏng đoán (tùy chọn) – IRR dự kiến ​​được phân phối 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ị chuẩn 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 chúng ta nên biết về tính 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 nhanh 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. So với các luồng tiền định kỳ có ngày thanh toán chuẩn xác không xác định, bạn có thể sử dụng Tính năng IRR.
  2. Phạm vi của các giá trị phải chứa tối thiểu một giá trị dương (thu nhập) và một giá trị âm (thanh toán đi).
  3. Nếu giá trị trước hết 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 hoa hồng; các khoản thanh toán tiếp theo được mang trở lại vào ngày của dòng tiền trước hết và được hoa hồng dựa trên một năm 365 ngày.
  4. Toàn bộ 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, ví dụ 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

Khởi đầu với phán đoán nếu được phân phối 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 độ chuẩn xác 0,000001%. Nếu sau 100 lần thử, tỷ lệ chuẩn xác không được tìm thấy, #NUM! lỗi được trả lại.

Để xác minh tính hợp lệ của phương trình này, tất cả chúng ta hãy xác minh 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 thiện 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ư trổ tài trong ảnh chụp màn hình bên dưới, kết quả rất gần với không. QED ?

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 thông dụng 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à kỳ vọng sẽ thu đượ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 số đó 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ận xét lợi nhuận của khoản đầu tư này, hãy so sánh sản lượng XIRR với trung tâm tư vấn du học của bạn ngân sách vốn trung bình 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 ngân sách vốn, dự án có thể được xem như 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à chúng ta nên ước tính trước khi mang 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 phán đoán. Điều này đặc biệt hữu ích khi công thức XIRR chuẩn xác rõ ràng ném #NUM! lỗi.

So 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 mang 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

Để khởi đầ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 nịch 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 hình như quá cao để được minh chứng 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

Để xác minh bổ sung, hãy sử dụng Tính 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ử toàn bộ 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 thu đượ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:

Tổng kết: 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 tính năng XIRR ở dạng thuần túy; để có được IRR hàng tháng, hãy vận dụng điều chỉnh được mô tả ở trên.

Mẫu Excel XIRR

Để nhanh chóng thu đượ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 rẽ (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 trước hết và B2 là ngày trước hết.

    Để biết hướng dẫn cụ thể từng bước, vui lòng xem Cách tạo một phạm vi có tên động trong Excel.

  3. Phân phối 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! Hiện thời 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 thích hợp:

Tải xuống mẫu XIRR Excel

XIRR so với IRR trong Excel

Sự khác biệt trung tâm các hàm Excel XIRR và IRR là:

  • IRR giả định rằng toàn bộ các thời kỳ trong một loạt các dòng tiền đều bằng nhau. Bạn sử dụng tính 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 rẽ. Vì vậy, sử dụng tính 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 chuẩn xác của các khoản thanh toán, nên sử dụng XIRR vì nó phân phối độ chuẩn 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 toàn bộ các khoản thanh toán xảy ra tại khoảng thời gian điều độ, 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 tính năng XNPV vì kết quả của XIRR là tỷ lệ hoa hồng dẫn theo 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ố thời dịp đầu tư và muốn xác minh 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ệ hoa hồng trong E1, như sau Công thức XNPV sẽ phân phối 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:

Hiện thời, hãy tìm tỷ lệ hoa hồng nào sẽ làm cho giá trị hiện tại ròng bằng không. So với điều này, chúng tôi sử dụng tính năng XIRR:

=XIRR(A2:A5, B2:B5)

Để xác minh xem vận tốc do XIRR tạo ra có thực sự dẫn theo 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 thể tính 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 chuẩn xác, chọn hiển thị nhiều vị trí thập phân hơn hoặc vận 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ư vậy này:

Nếu bạn không thân thuộc với ký hiệu khoa học, hãy thực hiện phép tính sau để chuyển hóa 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 xác minh.

#NUM! lỗi

Lỗi #NUM có thể xảy ra vì các nguyên nhân 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ối thiểu 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 trước hết.
  • 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 phán đ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 phân phối giá trị không phải là số.
  • Một số ngày được phân phối không thể được xác định là ngày Excel hợp lệ.

Này 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à kỳ vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

Bạn cũng có thể quan tâm:

See also  Sau danh từ là gì