Hàm IRR báo lỗi NUM! Cách khắc phục?

[phamtuonggiang]

Chào các anh chị!
Em đang tính toán hiệu quả của một dự án.
Tuy nhiên không hiểu sao khi em đặt công thức tính IRR thì nó toàn báo lỗi ( File đính kèm)
Em rất mong các anh chị chỉ ra lỗi và cách khắc phục giúp em
Cảm ơn các anh chị rất nhiều!

 

[ZzNHCzZ]

Hàm IRR()

Tính lợi suất nội hàm (hay còn gọi là hàm tính tỷ suất lưu hành nội bộ, hay tỷ suất hoàn vốn nội bộ) cho một chuỗi các lưu động tiền mặt được thể hiện bởi các trị số. Các lưu động tiền mặt này có thể không bằng nhau, nhưng chúng phải xuất hiện ở những khoảng thời gian bằng nhau (hằng tháng, hằng năm chẳng hạn). Lợi suất thực tế là lãi suất nhận được từ một khoản đầu tư gồm các khoản chi trả (trị âm) và các khoản thu nhập (trị dương) xuất hiện ở những kỳ ổn định.

Lợi suất nội hàm IRR (hay tỷ suất hoàn vốn nội bộ) là mức lãi suất mà nếu dùng nó làm suất chiết khấu để tính chuyển các khoản thu chi của dự án về cùng mặt bằng thời gian hiện tại thì tổng thu sẽ cân bằng với tổng chi, tức là NPV = 0. Nếu IRR > lãi suất chiết khấu (xem hàm NPV) thì coi như dự án khả thi, còn ngược lại thì không.

Cú pháp: = IRR(values, guess)

Values : Là một mảng hoặc các tham chiếu đến các ô có chứa số liệu cần cho việc tính toán lợi suất thực tế.
- Values phải chứa ít nhất 1 giá trị âm và 1 giá trị dương.

- IRR() sử dụng thứ tự các giá trị của values như là thứ tự lưu động tiền mặt. Do đó cần cẩn thận để các thứ tự chi trả hoặc thu nhập luôn được nhập đúng.

- IRR() chỉ tính toán các giá trị số bên trong các mảng hoặc tham chiếu của values; còn các ô rỗng, các giá trị logic, text hoặc các giá trị lỗi đều sẽ bị bỏ qua.
Guess : Một con số % ước lượng gần với kết quả của IRR(). Nếu bỏ qua, thì mặc định guess = 10%.
- Excel dùng chức năng lặp trong phép tính IRR. Bắt đầu với guess, IRR lặp cho tới khi kết quả chính xác trong khoảng 0.00001%. Nếu IRR không thể đưa ra kết quả sau 20 lần lặp, IRR sẽ trả về giá trị lỗi #NUM!

- Trong trường hợp IRR trả về giá trị lỗi #NUM!, hoặc nếu kết quả không xấp xỉ giá trị mong đợi, hãy thử lại với một giá trị guess khác.

Lưu ý:

• IRR() có liên quan mật thiết với hàm NPV(), là hàm tính hiện giá ròng của một khoản đầu tư. Tỷ suất do IRR trả về chính là lãi suất rate sao cho NPV = 0.

Ví dụ:

• Một dự án đầu tư có chi phí tính đến thời điểm dự án bắt đầu đi vào hoạt động sản xuất là 100 triệu USD, doanh thu hàng năm của dự án là 50 triệu USD. Chi phí hằng năm là 20 triệu USD, đời của dự án là 5 năm. Hãy xác định tỷ suất hoàn vốn nội bộ biết lãi suất vay dài hạn là 12%/năm.

= IRR({-100000000, 30000000, 30000000, 30000000, 30000000, 30000000}) = 15%
Do 15% > 12% nên dự án mang tính khả thi.

• Một dự án đầu tư có chi phí tính đến thời điểm dự án bắt đầu đi vào hoạt động sản xuất là 70 triệu USD, lãi thực trong năm thứ nhất là 12 triệu USD, trong năm thứ hai là 15 triệu USD, trong năm thứ ba là 18 triệu USD, trong năm thứ tư là 21 triệu USD và trong năm thứ năm là 26 triệu USD. Tính IRR của dự án này sau 2 năm, sau 4 năm, sau 5 năm ?

IRR sau 2 năm:
= IRR({-70000000, 12000000, 15000000}, -10%) = -44%
(nếu không cho guess = -10%, IRR sẽ trả về lỗi #NUM!)

IRR sau 4 năm:
= IRR({-70000000, 12000000, 15000000, 18000000, 21000000}) = -2%

IRR sau 5 năm:
IRR({-70000000, 12000000, 15000000, 18000000, 21000000, 26000000}) = 9%

Không cần biết lãi suất cho vay dài hạn để thực hiện dự án, ta cũng có thể thấy rằng dự án này ít nhất phải sau 5 năm mới mang tính khả thi.


Thân!

 

[phamtuonggiang]

Cảm ơn anh đã trả lời em. Nhưng anh ơi, anh xem cụ thể cách khắc phục vấn đề trong PA em gửi kèm với ạ.

 

[BNTT]

Cảm ơn anh đã trả lời em. Nhưng anh ơi, anh xem cụ thể cách khắc phục vấn đề trong PA em gửi kèm với ạ.

Bạn cảm ơn, nhưng bạn có đọc bài đó không?
Chắc là không... Vì nếu đọc kỹ thì bạn đã không hỏi lại một lần nữa!

Thôi thì xem lại nhé. Chỉ cần xem đoạn này thôi:

Cú pháp: = IRR(values, guess)

Values : Là một mảng hoặc các tham chiếu đến các ô có chứa số liệu cần cho việc tính toán lợi suất thực tế.

- Values phải chứa ít nhất 1 giá trị âm và 1 giá trị dương.

- IRR() sử dụng thứ tự các giá trị của values như là thứ tự lưu động tiền mặt. Do đó cần cẩn thận để các thứ tự chi trả hoặc thu nhập luôn được nhập đúng.

- IRR() chỉ tính toán các giá trị số bên trong các mảng hoặc tham chiếu của values; còn các ô rỗng, các giá trị logic, text hoặc các giá trị lỗi đều sẽ bị bỏ qua.​

Guess : Một con số % ước lượng gần với kết quả của IRR(). Nếu bỏ qua, thì mặc định guess = 10%.

- Excel dùng chức năng lặp trong phép tính IRR. Bắt đầu với guess, IRR lặp cho tới khi kết quả chính xác trong khoảng 0.00001%. Nếu IRR không thể đưa ra kết quả sau 20 lần lặp, IRR sẽ trả về giá trị lỗi #NUM!

- Trong trường hợp IRR trả về giá trị lỗi #NUM!, hoặc nếu kết quả không xấp xỉ giá trị mong đợi, hãy thử lại với một giá trị guess khác.​

Công thức của bạn không dùng tới tham số Guess, do đó Excel mặc định cho nó là 10%. Vậy bạn hãy thử lại với con số khác, nhé.

 

[phamtuonggiang]

Hic, em xin cảm ơn admin! Nhưng thưa Admin, em quả thật là không biết phải dùng guess nào cho phù hợp. Khó quá ạ

 

[ptm0412]

Không chỉ lỗi không có tham số guess đâu. Còn 1 lỗi nữa là dòng tiền không đều: từ C đến AB là khoản thu, chi từng năm, ô AC19 là khoản thu gộp cho 40 năm, con số quá lớn nhưng Excel vẫn coi là thu của 1 năm, thế là Excel hông thèm tính! (nếu tính chỉ đến năm 35 thì được, với guess 15%)
Bi giờ, 40 năm còn lại phải ghi rõ từng năm, mỗi năm bao nhiêu. Chuyển qua hàng dọc cho dễ 1 tí.

Kết quả: 12,881%

Ghi chú: BNTT hông phải Admin đâu nha, nick đỏ mới Admin.

 

[tuananhtg]

Giá trị IRR là 13.847%. Theo mình nghĩ:
- Giá trị Dòng tiền của bạn quá lớn(Cụ thể nó tính giá trị lớn nhất là bao nhiêu thì tôi chưa biết). Thông thường các dự án lớn thường quy đổi ra USD.
- Mình đã quy đổi ra USD với một tỷ giá nào đó VD:16000. và đặt lại hàm irr. sẽ cho kết quả như ý.

Bạn thử test lại xem nhé

 

[phamtuonggiang]

cảm ơn các anh chị nhiều

 

[ptm0412]

Giá trị Dòng tiền của bạn quá lớn(Cụ thể nó tính giá trị lớn nhất là bao nhiêu thì tôi chưa biết).

Trong trường hợp bài này, là sai ở dòng tiền không đều: tất cả các dòng tiền là 1 năm, trừ 1 dòng tiền cuối là 40 năm. Nếu cố ép Excel tính thì cũng ra kết quả sai. Tôi để nguyên số tiền (hàng chục tỷ), vẫn tính được nếu tách cái dòng tiền 40 năm đó ra 40 ô.

 

[tuananhtg]

Mình đang nghiên cứu cái này.
Mình đã test cách của ptm0412, giả sử mình bỏ thời gian 40 năm kia. Và tính dòng dời của dự án là đến 2035. hàm vẫn báo lỗi NUM.
Test theo cách khác: rút ngắn dự án lại đến ~~2014 irr cho kết quả. Kết quả giá trị âm.
vượt qua 2015, irr báo lỗi num.
Để yên đó. Mình chia số 479 tỷ ra làm 3 năm. và tính irr hàm vẫn báo lỗi num.
Quy đổi cả dòng tiền ra usd(giảm giá trị). hàm cho kết quả.

 

[ptm0412]

Để nguyên số hàng chục tỷ:

- Bỏ 40 năm, tính 25 năm: 12,5476%
- Tính 64 năm: 12,8810%
- Tính 65 năm: lỗi

Chia cho 1000 để dùng đơn vị tính "ngàn đồng": đương nhiên ra: 12,8813%
(Nếu chỉ chia 3 cũng tính được)

Nhưng điều quan trọng tôi muốn nói, là khoản thu 40 năm phải liệt kê đủ 40 lần, không được tính gộp là 1 lần thu tiền. Tính gộp là sai về cơ bản, về lý thuyết, về ý nghĩa!!!

 

[tuananhtg]

Nhưng điều quan trọng tôi muốn nói, là khoản thu 40 năm phải liệt kê đủ 40 lần, không được tính gộp là 1 lần thu tiền. Tính gộp là sai về cơ bản, về lý thuyết, về ý nghĩa!!!

Về nguyên lý tính toán dự án thì chính xác là như vậy! Mình không rành về dự án lắm. Nhưng khi chia ra các khoảng thời gian để tính toán dự án thì phải chia đều. Vd: theo tháng, quý, năm, Xnăm,... tùy vào dự án!