Home |
Search |
Today's Posts |
#1
|
|||
|
|||
irr, xirr, npv frustrations
Copied below, I have a series of monthly cash flows for a project. The IRR
function is giving me a "#DIV/0!" error, while the XIRR gives me a return of 10.19%. However, when I try to double check that return by discounting each cash flow individually (NPV), I arrive at a rate of 9.75%. So I have two questions for all you excel brains out the 1) Why is my IRR not working when XIRR is? and 2) Why is my NPV check not matching the XIRR result? I've been searching through microsoft excel's help section and poring over various forums without any luck. Any advice would be much appreciated, and allow me to stop bashing my head against the wall! Thanks, Zach 12/1/2005 ($128,931,571) 1/1/2006 ($1,431,571) 2/1/2006 ($1,431,571) 3/1/2006 ($1,431,571) 4/1/2006 ($130,363,142) 5/1/2006 ($2,863,142) 6/1/2006 ($2,863,142) 7/1/2006 ($2,863,142) 8/1/2006 ($2,863,142) 9/1/2006 ($2,863,142) 10/1/2006 ($2,863,142) 11/1/2006 ($2,863,142) 12/1/2006 ($621,565,535) 1/1/2007 ($45,536,678) 2/1/2007 ($2,822,847) 3/1/2007 ($2,458,944) 4/1/2007 ($2,170,616) 5/1/2007 ($1,883,945) 6/1/2007 ($1,598,931) 7/1/2007 ($1,315,573) 8/1/2007 ($1,033,872) 9/1/2007 ($753,828) 10/1/2007 ($475,440) 11/1/2007 ($198,708) 12/1/2007 $76,366 1/1/2008 $349,784 2/1/2008 $440,556 3/1/2008 $440,556 4/1/2008 $443,149 5/1/2008 $444,446 6/1/2008 $444,446 7/1/2008 $444,446 8/1/2008 $444,446 9/1/2008 $444,446 10/1/2008 $444,446 11/1/2008 $444,446 12/1/2008 $444,446 1/1/2009 $444,446 2/1/2009 $444,446 3/1/2009 $444,446 4/1/2009 $444,446 5/1/2009 $444,446 6/1/2009 $444,446 7/1/2009 $444,446 8/1/2009 $444,446 9/1/2009 $444,446 10/1/2009 $444,446 11/1/2009 $444,446 12/1/2009 $2,228,768 1/1/2010 $2,332,674 2/1/2010 $2,413,092 3/1/2010 $2,539,117 4/1/2010 $2,643,023 5/1/2010 $2,746,928 6/1/2010 $2,850,834 7/1/2010 $2,969,999 8/1/2010 $3,089,164 9/1/2010 $3,208,128 10/1/2010 $3,327,293 11/1/2010 $1,400,293,587 |
#2
|
|||
|
|||
irr, xirr, npv frustrations
Zachary Chan wrote:
Copied below, I have a series of monthly cash flows for a project. The IRR function is giving me a "#DIV/0!" error Of course, it might have been helpful if you had posted the IRR() formula that you are using. I cannot answer your questions. But I __can__ tell you how to make IRR() work with your data, which I think is what you really want to know. Add a "guess" of 0.01. I discovered this by debugging your problem in the following manner. This might help you solve problems on your own in the future. I entered the formula =IRR(Bx:B60) into D1, starting with B24 for "Bx" and decreasing it (actually using a "binary search" methodology). I discovered that for IRR(B13:B60), I got a #NUM! error. The IRR help text explains that this means that IRR() could not determine the rate of return after 20 tries starting with a guess of 10%. When I got to IRR(B12:B60), the #DIV/0! error appeared. Apparently, this has the same implications as #NUM!. The implementation of IRR() must be reaching a divisor of zero even before the 20 iteration limit. (Wouldn't it be nice of that were documented in the IRR help text?) That convinced me to try a "guess" less than 10%. Experimentally, I discovered that 1% worked. IRR(B1:B60,0.01) results in a rate of return 0.81%. I checked this number by putting =PV($D$1,ROW()-1,,-B1) into C1 and copying the formula down through B60. SUM(C1:C60) is approximately 0, as we would expect. |
#3
|
|||
|
|||
irr, xirr, npv frustrations
joeu2004 - Thank you very very much! I was simply using IRR(cash flows) and
not putting in a "guess" value. As you discovered, Excel starts from 10% and tries 20 iterations before giving up. Honestly, that's pretty disappointing that it only gives you 20 iterations. I wonder if that is adjustable, the way general iterations on excel are... As you showed, using the excel PV() function also confirms the IRR. I was using the more old fashioned formula of Bx/(1+$D$1)^(ROW()-1), and didn't realize that I was getting a simple rate, not compounded. Compounding appropriately gave me the same answer as PV(). Thanks again - I had posted earlier on excelforum.com and had 10 views and NO ONE could solve my problem! " wrote: Zachary Chan wrote: Copied below, I have a series of monthly cash flows for a project. The IRR function is giving me a "#DIV/0!" error Of course, it might have been helpful if you had posted the IRR() formula that you are using. I cannot answer your questions. But I __can__ tell you how to make IRR() work with your data, which I think is what you really want to know. Add a "guess" of 0.01. I discovered this by debugging your problem in the following manner. This might help you solve problems on your own in the future. I entered the formula =IRR(Bx:B60) into D1, starting with B24 for "Bx" and decreasing it (actually using a "binary search" methodology). I discovered that for IRR(B13:B60), I got a #NUM! error. The IRR help text explains that this means that IRR() could not determine the rate of return after 20 tries starting with a guess of 10%. When I got to IRR(B12:B60), the #DIV/0! error appeared. Apparently, this has the same implications as #NUM!. The implementation of IRR() must be reaching a divisor of zero even before the 20 iteration limit. (Wouldn't it be nice of that were documented in the IRR help text?) That convinced me to try a "guess" less than 10%. Experimentally, I discovered that 1% worked. IRR(B1:B60,0.01) results in a rate of return 0.81%. I checked this number by putting =PV($D$1,ROW()-1,,-B1) into C1 and copying the formula down through B60. SUM(C1:C60) is approximately 0, as we would expect. |
#4
|
|||
|
|||
irr, xirr, npv frustrations
With respect to the limit of 20 iterations, this makes sense in my experience. I
have programmed the Newton-Raphson method to calculate the rate, and it is *extremely* efficient. I have seen it converge from a guess of 10% to the correct answer of -20% on a large set of data in less than 7 iterations. If IRR doesn't get to the right answer in 20 tries, it's almost always spinning its wheels and you're better off if it quit. -- Regards, Fred "Zachary Chan" wrote in message ... joeu2004 - Thank you very very much! I was simply using IRR(cash flows) and not putting in a "guess" value. As you discovered, Excel starts from 10% and tries 20 iterations before giving up. Honestly, that's pretty disappointing that it only gives you 20 iterations. I wonder if that is adjustable, the way general iterations on excel are... As you showed, using the excel PV() function also confirms the IRR. I was using the more old fashioned formula of Bx/(1+$D$1)^(ROW()-1), and didn't realize that I was getting a simple rate, not compounded. Compounding appropriately gave me the same answer as PV(). Thanks again - I had posted earlier on excelforum.com and had 10 views and NO ONE could solve my problem! " wrote: Zachary Chan wrote: Copied below, I have a series of monthly cash flows for a project. The IRR function is giving me a "#DIV/0!" error Of course, it might have been helpful if you had posted the IRR() formula that you are using. I cannot answer your questions. But I __can__ tell you how to make IRR() work with your data, which I think is what you really want to know. Add a "guess" of 0.01. I discovered this by debugging your problem in the following manner. This might help you solve problems on your own in the future. I entered the formula =IRR(Bx:B60) into D1, starting with B24 for "Bx" and decreasing it (actually using a "binary search" methodology). I discovered that for IRR(B13:B60), I got a #NUM! error. The IRR help text explains that this means that IRR() could not determine the rate of return after 20 tries starting with a guess of 10%. When I got to IRR(B12:B60), the #DIV/0! error appeared. Apparently, this has the same implications as #NUM!. The implementation of IRR() must be reaching a divisor of zero even before the 20 iteration limit. (Wouldn't it be nice of that were documented in the IRR help text?) That convinced me to try a "guess" less than 10%. Experimentally, I discovered that 1% worked. IRR(B1:B60,0.01) results in a rate of return 0.81%. I checked this number by putting =PV($D$1,ROW()-1,,-B1) into C1 and copying the formula down through B60. SUM(C1:C60) is approximately 0, as we would expect. |
#5
|
|||
|
|||
irr, xirr, npv frustrations
Does Excel use this "Newton-Raphson" method? Because as in my case, IRR
couldn't move from 10% to 0.81% in 20 iterations, returning an error message instead. "Fred Smith" wrote: With respect to the limit of 20 iterations, this makes sense in my experience. I have programmed the Newton-Raphson method to calculate the rate, and it is *extremely* efficient. I have seen it converge from a guess of 10% to the correct answer of -20% on a large set of data in less than 7 iterations. If IRR doesn't get to the right answer in 20 tries, it's almost always spinning its wheels and you're better off if it quit. -- Regards, Fred "Zachary Chan" wrote in message ... joeu2004 - Thank you very very much! I was simply using IRR(cash flows) and not putting in a "guess" value. As you discovered, Excel starts from 10% and tries 20 iterations before giving up. Honestly, that's pretty disappointing that it only gives you 20 iterations. I wonder if that is adjustable, the way general iterations on excel are... As you showed, using the excel PV() function also confirms the IRR. I was using the more old fashioned formula of Bx/(1+$D$1)^(ROW()-1), and didn't realize that I was getting a simple rate, not compounded. Compounding appropriately gave me the same answer as PV(). Thanks again - I had posted earlier on excelforum.com and had 10 views and NO ONE could solve my problem! " wrote: Zachary Chan wrote: Copied below, I have a series of monthly cash flows for a project. The IRR function is giving me a "#DIV/0!" error Of course, it might have been helpful if you had posted the IRR() formula that you are using. I cannot answer your questions. But I __can__ tell you how to make IRR() work with your data, which I think is what you really want to know. Add a "guess" of 0.01. I discovered this by debugging your problem in the following manner. This might help you solve problems on your own in the future. I entered the formula =IRR(Bx:B60) into D1, starting with B24 for "Bx" and decreasing it (actually using a "binary search" methodology). I discovered that for IRR(B13:B60), I got a #NUM! error. The IRR help text explains that this means that IRR() could not determine the rate of return after 20 tries starting with a guess of 10%. When I got to IRR(B12:B60), the #DIV/0! error appeared. Apparently, this has the same implications as #NUM!. The implementation of IRR() must be reaching a divisor of zero even before the 20 iteration limit. (Wouldn't it be nice of that were documented in the IRR help text?) That convinced me to try a "guess" less than 10%. Experimentally, I discovered that 1% worked. IRR(B1:B60,0.01) results in a rate of return 0.81%. I checked this number by putting =PV($D$1,ROW()-1,,-B1) into C1 and copying the formula down through B60. SUM(C1:C60) is approximately 0, as we would expect. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IRR, XIRR and NPV - a very frustrating problem | Excel Discussion (Misc queries) | |||
How can I get the XIRR funct to work for a positive first number? | Excel Worksheet Functions | |||
XIRR function | Excel Worksheet Functions | |||
Problem with XIRR function returning #NUM! | Excel Worksheet Functions | |||
XIRR and IRR | Excel Worksheet Functions |