Hỏi nguyên nhân lỗi [#NUM!] khi phân tích độ nhạy IRR bằng Table trong Excel 2007?

[huuthanhxd]

Chào mọi người!
Mọi người cho em hỏi là khi em sử dụng chức năng Table để phân tích độ nhạy 2 chỉ tiêu NPV và IRR thì trong đó có chỉ tiêu IRR khi phân tích một số giá trị trả về lỗi (#NUM!), em đã điều chỉnh các biến đầu vào nhưng vẫn tồn tại các giá trị lỗi như thế này. Mọi người có thể cho em biết là vì sao lại như vậy đc không ah?
Về mô hình thì em lập chính xác. Khi phân tích NPV thì rất ok.
Xin cảm ơn mọi người!

 

[ptm0412]

Bạn xem lại bản thân hàm tính IRR. Hàm IRR của Excel được tính với phương pháp tính gần đúng với số lần lặp hữu hạn. Do đó sau khi lặp đủ số lần mà vẫn không tính được sẽ ra kết quả lỗi. Nếu tham số Guess bị bỏ qua, nguy cơ lỗi tăng lên.

IRR(values,guess)

Values is an array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.

• Values must contain at least one positive value and one negative value to calculate the internal rate of return.
• IRR uses the order of values to interpret the order of cash flows. Be sure to enter your payment and income values in the sequence you want.
• If an array or reference argument contains text, logical values, or empty cells, those values are ignored.

Guess is a number that you guess is close to the result of IRR.

• Microsoft Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can't find a result that works after 20 tries, the #NUM! error value is returned.
• In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent).
• If IRR gives the #NUM! error value, or if the result is not close to what you expected, try again with a different value for guess.
  

Nếu vẫn không được, bạn đưa file của bạn lên đây, mọi người xem. Cái mô hình bạn cho là chính xác thì mới chỉ 1 mình bạn đánh giá.

Nhân tiện, quảng cáo trong chữ ký trên GPE là không được phép, vì vậy bạn nên thay đổi chữ ký, nếu không chúng tôi sẽ dùng quyền điều hành để xoá chữ ký mà không cần thông báo.

 

[huuthanhxd]

Cảm ơn anh đã giúp đỡ.
Đây là file của em, em đã thử điều chỉnh giá trị guess nhưng vẫn không được. Có gì anh xem giúp em với. Thanks anh!
Link file: http://www.mediafire.com/?70lcb06oq79f6z8

 

[ptm0412]

Link file: http://www.mediafire.com/?70lcb06oq79f6z8

Link thì đúng, nhưng file bị set private, không down được. Bạn phải set public cho file (hoặc folder) chứ?

Với lại, bạn thử xem trong dãy số cho vào tham số thứ nhất của hàm IRR, đã có âm có dương chưa? Nếu chỉ có dương không có âm, hoặc ngược lại, thì tiêu đúng rồi.

 

[huuthanhxd]

Link thì đúng, nhưng file bị set private, không down được. Bạn phải set public cho file (hoặc folder) chứ?

Với lại, bạn thử xem trong dãy số cho vào tham số thứ nhất của hàm IRR, đã có âm có dương chưa? Nếu chỉ có dương không có âm, hoặc ngược lại, thì tiêu đúng rồi.

Vâng, xin lỗi anh lúc nãy em quên mất. Em đã check lại link, có gì anh giúp em lỗi này với.
Cảm ơn anh nhiều!

 

[ptm0412]

Tôi tải file về, để nguyên không làm gì hết, thì không có lỗi gì cả. Kiểm tra thì Guess = 0.2

Nếu sửa Guess thành 0.15 hoặc bỏ qua, thì sẽ có 1 vài ô bị #Num!, lý do tôi cũng đã nói.
Nếu sửa giá bán ô B4 và B5 thấp xuống cũng có vài trường hợp bị lỗi #Num! ở 1 vài ô.

Vấn đề then chốt nằm ở Guess mà thôi, ô nào bị #Num!, ngó ô không lỗi liền kề xem bao nhiêu, lấy con số tròn gần gần nó để làm Guess sẽ hết #Num.

 

[huuthanhxd]

Cảm ơn anh vì đã chia sẽ, nhưng em đã thay đổi như anh song nó lại trồi ô khác ra anh ah. Theo em nghĩ phải có 1 hướng giải quyết nào chứ cứ lần mãi cũng rất khó. Rất mong anh hướng dẫn thêm.
Cảm ơn anh!

 

[leonardo208]

Cảm ơn anh vì đã chia sẽ, nhưng em đã thay đổi như anh song nó lại trồi ô khác ra anh ah. Theo em nghĩ phải có 1 hướng giải quyết nào chứ cứ lần mãi cũng rất khó. Rất mong anh hướng dẫn thêm.
Cảm ơn anh!

Chào anh Thành.
Em cũng vừa đọc qua bản Excel của anh. Có lẽ nguyên nhân 1 phần là do giá trị Guess, như ở trên, nếu em thay đổi giá trị Guess từ 0,2 về 0,1 thì sẽ tìm được 2 giá trị IRR khác nhau.

Theo ý kiến của e thì thế này:

Dòng tiền thuần của anh là chuỗi 16 năm, trong đó dòng tiền đổi dấu (từ + sang - hoặc từ - sang + ) 2 lần, như vậy sẽ có tối đa 2 giá trị IRR.

Em cũng đã thử thay đổi giá bán SP Au xuống 9460, 9455, rồi 9450 (giữ giá bán SP Mo = 5950) như của anh, thì thấy như sau:
(Em dùng PP nội suy, tìm 2 giá trị i1, i2 để có NPV1>0; NPV2<0 thì IRR sẽ nằm trong khoảng (i1,i2)).

+) Tại mức giá 9460: Tìm được 2 giá trị của IRR là 18,27% và 19,92%
(i1= 18%; i2= 19%; i3= 20%) (NPV1 <0; NPV2 >0;NPV3 <0)

+) Tại mức giá 9455: Tìm được 2 giá trị của IRR là 18,76% và 19,42%
(i1= 18,5%; i2= 19%; i3=19,5%)

+)Tuy nhiên, khi giảm đến mức giá 9450 thì không thể tìm được bất cứ 1 Lãi suất i nào để NPV đổi dấu, NPV luôn <0.
Nhận thấy là cả 3 giá trị i càng ngày càng tiệm cận về nhau, NPV luôn <0 (NPV đạt max ~= -150 trđ tại i ~= 19,1%)

Do đó, với bài toán của anh, trường hợp cuối, chúng ta sẽ ko thể tìm được giá trị IRR, mà sẽ chỉ tìm được khoảng giá trị xấp xỉ mà thôi.

P/s: Trên đây là 1 số nhận xét của cá nhân em, mặc dù e cũng ko chắc chắn là đúng không nữa, mong anh và mọi người cùng nhận xét.

 

[huuthanhxd]

Mình đã tính toán lại và dòng tiền chỉ thay đổi dấu 1 lần bắt đầu từ năm thứ 2. Nhưng sau khi điều chỉnh các giá trị Guess (mỏi cả tay) nhưng mà các ô #NUM! vẫn không hết. Không biết chính xác ngọn nghành của nguyên nhân này là do đâu? và cho mình hỏi nếu trong bảng tính vẫn còn tồn tại 1 vài ô như vậy thì có đc không (ý mình là khi ko tìm được giá trị Guess thích hợp nhất). Rất mong các cao thủ chỉ giáo thêm.
Thanks mọi người nhiều!

Cảm ơn mọi người. Sau thời gian tự tìm hiểu mình đã tìm ra nguyên nhân của nó. Trước tiên mình xin cảm ơn mọi người đã giúp đỡ và sau đó mình xin được nói lỗi mà mình gặp vì lý do:
- Số chữ số trong dòng tiền tính NPV quá nhiều (hàng trăm tỷ: 12 chữ số) do đó nó sẽ báo lỗi khi tính IRR. Để khắc phục đơn giản chỉ cần làm giảm số chữ số (chia cho 1000 hoặc 1.000.000) là OK!