Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#NUM errors when calculating IRR
I am trying to calculate a few IRRs that are returning #NUM. I can't
figure out why. I've been trying different guesses and I've been tweaking the number of iterations and max change in Tools | Options | Calculation, and yet I still get #NUM. Can anyone take a look and figure out what I'm doing wrong? Here are the three cash flows for which I can't get an IRR: Cash Flow 1: $ (14,038,400) $ 9,419,009 $ (172,248) $ 6,172,725 $ (269,064) $ 2,955,516 $ (269,064) $ (5,730,809) $ (79,376) Cash Flow 2: $ (9,826,880) $ 6,500,691 $ (120,573) $ 4,433,082 $ (188,344) $ 2,391,228 $ (188,344) $ (4,011,566) $ (55,563) Cash Flow 3: $ (4,211,520) $ 2,918,318 $ (51,674) $ 1,739,643 $ (80,719) $ 564,288 $ (80,719) $ (1,719,243) $ (23,813) I would REALLY appreciate any help anyone could provide! Thanks in advance, Adam Sinclair |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#NUM errors when calculating IRR
Here's a post I just found, similar to your question http://tinyurl.com/j6zs3 -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=537546 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#NUM errors when calculating IRR
"vandenberg p" wrote:
Why? For the same reason you will not be able to find the value of X in the following: 6X^2 - 10X + 5 = 0[.] Look up Descartes rule of signs. There are 6 sign changes. Therefore the solution may have as many as 6 answers or as few as zero. I do not believe that necessarily explains the #NUM errors. IRR has no trouble computing the rate (2%) of the following cash flow, despite 8 sign changes: -100000 {10000,-1000} eight times 53435 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#NUM errors when calculating IRR
" wrote:
I am trying to calculate a few IRRs that are returning #NUM. IRR (Office Excel 2003) had no trouble computing the rate (20.13% per period) for the second cash flow. I assume the cash flows read left-to-right, top-to-bottom. As for the first and third cash flows, IRR computes the rate at which the NPV is zero. But in those cases, the NPV is never zero for any rate. Ergo, the IRR cannot be computed. If these are real-life cash flows, I wonder if the periods are spaced unevenly. If so, you need to insert zero for the periods when there are no cash flows, or use XIRR and actual dates of each non-zero cash flow. If this is a class exercise, it would be prudent to graph the NPV for ranges of rates. For the first and third cash flows, consider starting at -12% incrementing by 1% for 100 points. You might notice something interesting for large positive rates. But if you graph exceedingly (absurdly) large rates, you will see that even then, NPV never quite reaches zero. (Close, but no cigar.) ----- " wrote: I am trying to calculate a few IRRs that are returning #NUM. I can't figure out why. I've been trying different guesses and I've been tweaking the number of iterations and max change in Tools | Options | Calculation, and yet I still get #NUM. Can anyone take a look and figure out what I'm doing wrong? Here are the three cash flows for which I can't get an IRR: Cash Flow 1: $ (14,038,400) $ 9,419,009 $ (172,248) $ 6,172,725 $ (269,064) $ 2,955,516 $ (269,064) $ (5,730,809) $ (79,376) Cash Flow 2: $ (9,826,880) $ 6,500,691 $ (120,573) $ 4,433,082 $ (188,344) $ 2,391,228 $ (188,344) $ (4,011,566) $ (55,563) Cash Flow 3: $ (4,211,520) $ 2,918,318 $ (51,674) $ 1,739,643 $ (80,719) $ 564,288 $ (80,719) $ (1,719,243) $ (23,813) I would REALLY appreciate any help anyone could provide! Thanks in advance, Adam Sinclair |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#NUM errors when calculating IRR
Thanks, all! This is for a school exercise... the periods are evenly
spaced but the project throws off some irregular cash flows... I had planned on showing NPVs for a couple of discount rates, but graphing 100 rates sounds like a great idea (if I can find the time). |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#NUM errors when calculating IRR
Read my answer carefully: It says that there can be as many answer as sign changes or "less." As long as there is an answer and the guess rate (either the implicit one or the supplied one) is in the right area an answer is provided. But if the guess rate is in the wrong area (in that case usually the root finder bumps the time and/or try limit) or the cash flows have the wrong sequence or scale no answer will be found and #num is returned. So whether you get an answer does depend upon the actual sequence of numbers their size and the guess rate. Your particular choice of numbers allowed the IRR to find a rate and report it. Since the Excel IRR (as all are) is a numerical root finding technique, it produces only one answer, if it can find it, that is closest (in an algorithm sense, since it does depend on the slope in addition to actual numerical closeness) to the guess rate it uses. I don't know exactly which algorithm it uses, but it is probably some version of Newton-Rhapson, which is generally very fast and efficient. Google "Newton-Rhapson" I got about 800 hits. The following sequence of cash flows without a guess rate will produce an answer of 25%. Cash Flow-504.00 2862.00 -6070.00 5700.00 -2000.00 But with different guess rates produce a whole slew of different answers. Guess IRR 00.00% 25.00% 10.00% 25.00% 20.00% 25.00% 30.00% 33.33% 40.00% 42.86% 50.00% 42.86% 60.00% 66.67% 70.00% 66.67% 80.00% 66.67% 90.00% 66.67% It is easiest to demonstrate use a simpler equation that we can solve with the Quadratic Formula. Take the following cash flows: -28.00 53.00 -8.00 Two sign changes, therefore there can be 0, 1 or 2 roots Find the IRR using Quadratic Formula -28+53/(1+r)^1-8/(1+r)^2=0 Multiply through by (1+r)^2 -28*(1+r)^2 + 53*(1+r)^1 -8 =0 Let X = (1+r) -28*X^2+53*X-8=0 The quadratic formula: 1.727 =(-53-((53^2)-(4*(-28)*(-8)))^0.5)/(2*-28) 0.165 =(-53+((53^2)-(4*(-28)*(-8)))^0.5)/(2*-28) 0.727 =1.727-1 -0.835 =.165-1 It is easy in this form to see what the problem is. If you change the the 53 to 29, IRR will return a #num. Why? Because there is no solution. Descartes' rule tells that the is possible, yet if we change 29 to 30 we get one answer (-50%). Finally here is Descartes' Rule of Signs "We can determine also the number of true and false roots that any equation can have, as follows: An equation can have as many true roots as it contains changes of sign, from + to - or from - to +; and as many false roots as the number of times two + signs or two - signs are found in succession." Source: http://www.cut-the-knot.org/fta/ROS2.shtml Also try: http://www.purplemath.com/modules/drofsign.htm wrote: : "vandenberg p" wrote: : Why? For the same reason you will not be able to find the : value of X in the following: 6X^2 - 10X + 5 = 0[.] : Look up Descartes rule of signs. There are 6 sign changes. : Therefore the solution may have as many as 6 answers or : as few as zero. : I do not believe that necessarily explains the #NUM errors. : IRR has no trouble computing the rate (2%) of the following : cash flow, despite 8 sign changes: : -100000 : {10000,-1000} eight times : 53435 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#NUM errors when calculating IRR
"vandenberg p" wrote:
Read my answer carefully: It says that there can be as many answer as sign changes or "less." As long as there is an answer and the guess rate (either the implicit one or the supplied one) is in the right area an answer is provided. And read my answers carefully -- both of them. First, your original response did not allude to the fact that proper choice of "guess" might find a solution. I did not want other readers to be left with the (wrong) impression that simply because there are a lot of cash flow sign changes, there would be no solution or it would be difficult for IRR to find a solution. As you point out in your second response, it all depends on how close the (default) "guess" is to any one of the solutions. Second, and more significantly, I said that the number of sign changes was not "necessarily" the answer -- as in "necessary and sufficient" -- as you seemed to imply. In fact, as I indicated in my other response, the real problem is that there is __no__ solution, at least to 2 of the 3 examples. It would not matter how close a "guess" you make. It has little to do with the number of sign changes (albeit we might need more than 2 or 3 for the situation to arise). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tracking Errors | Excel Worksheet Functions | |||
How do I ignore cells with errors when calculating an average? | Excel Worksheet Functions | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! | Excel Discussion (Misc queries) | |||
Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions |