If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IRR showed #NUM!
I am trying to use IRR function for my string of cash flows for 10 years:
Year | Cash Flows 2009 | -272,895,028,812 2010 | -207,524,139,910 2011 | -185,716,940,803 2012 | -9,306,121,279 2013 | 326,372,394,532 2014 | 245,782,146,138 2015 | 501,268,808,310 2016 | 497,663,783,563 2017 | 493,577,645,807 2018 | 488,974,344,794 The formula I am using is simply: =IRR("Range of Data") I am getting the #NUM! error...any assistance please? Thanks! |
#2
|
|||
|
|||
IRR showed #NUM!
bakbuk -
With the years in A2:A11 and the cash flows in B2:B11, in another cell =IRR(B2:B11) returns 34%. - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "bakbuk" wrote in message ... I am trying to use IRR function for my string of cash flows for 10 years: Year | Cash Flows 2009 | -272,895,028,812 2010 | -207,524,139,910 2011 | -185,716,940,803 2012 | -9,306,121,279 2013 | 326,372,394,532 2014 | 245,782,146,138 2015 | 501,268,808,310 2016 | 497,663,783,563 2017 | 493,577,645,807 2018 | 488,974,344,794 The formula I am using is simply: =IRR("Range of Data") I am getting the #NUM! error...any assistance please? Thanks! |
#3
|
|||
|
|||
IRR showed #NUM!
It worked? oww...I wonder why mine didn't...
I'll try to troubleshoot then, thanks for the result "Mike Middleton" wrote: bakbuk - With the years in A2:A11 and the cash flows in B2:B11, in another cell =IRR(B2:B11) returns 34%. - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "bakbuk" wrote in message ... I am trying to use IRR function for my string of cash flows for 10 years: Year | Cash Flows 2009 | -272,895,028,812 2010 | -207,524,139,910 2011 | -185,716,940,803 2012 | -9,306,121,279 2013 | 326,372,394,532 2014 | 245,782,146,138 2015 | 501,268,808,310 2016 | 497,663,783,563 2017 | 493,577,645,807 2018 | 488,974,344,794 The formula I am using is simply: =IRR("Range of Data") I am getting the #NUM! error...any assistance please? Thanks! |
#4
|
|||
|
|||
IRR showed #NUM!
I still have the problem with this matter. I tried it at my friend's computer
and the result is still the same (#NUM!). I also tried by creating a new excel file but contain the same value, and it happened again. Both of us use Office2007, saved and tried the file in .xls and .xlsx format. Is there something wrong? thanks again. "bakbuk" wrote: It worked? oww...I wonder why mine didn't... I'll try to troubleshoot then, thanks for the result "Mike Middleton" wrote: bakbuk - With the years in A2:A11 and the cash flows in B2:B11, in another cell =IRR(B2:B11) returns 34%. - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "bakbuk" wrote in message ... I am trying to use IRR function for my string of cash flows for 10 years: Year | Cash Flows 2009 | -272,895,028,812 2010 | -207,524,139,910 2011 | -185,716,940,803 2012 | -9,306,121,279 2013 | 326,372,394,532 2014 | 245,782,146,138 2015 | 501,268,808,310 2016 | 497,663,783,563 2017 | 493,577,645,807 2018 | 488,974,344,794 The formula I am using is simply: =IRR("Range of Data") I am getting the #NUM! error...any assistance please? Thanks! |
#5
|
|||
|
|||
IRR showed #NUM!
I get #NUM too.
Your numbers are quite big. If I divide all numbers by 100, I get 26.43%. -- Kind regards, Niek Otten Microsoft MVP - Excel "bakbuk" wrote in message ... I still have the problem with this matter. I tried it at my friend's computer and the result is still the same (#NUM!). I also tried by creating a new excel file but contain the same value, and it happened again. Both of us use Office2007, saved and tried the file in .xls and .xlsx format. Is there something wrong? thanks again. "bakbuk" wrote: It worked? oww...I wonder why mine didn't... I'll try to troubleshoot then, thanks for the result "Mike Middleton" wrote: bakbuk - With the years in A2:A11 and the cash flows in B2:B11, in another cell =IRR(B2:B11) returns 34%. - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "bakbuk" wrote in message ... I am trying to use IRR function for my string of cash flows for 10 years: Year | Cash Flows 2009 | -272,895,028,812 2010 | -207,524,139,910 2011 | -185,716,940,803 2012 | -9,306,121,279 2013 | 326,372,394,532 2014 | 245,782,146,138 2015 | 501,268,808,310 2016 | 497,663,783,563 2017 | 493,577,645,807 2018 | 488,974,344,794 The formula I am using is simply: =IRR("Range of Data") I am getting the #NUM! error...any assistance please? Thanks! |
#6
|
|||
|
|||
IRR showed #NUM!
Oh I see...so the problem is that the numbers are big...
I followed your step but divide by 1000...yup, it returned 26.43%. They're big because they're in IDR currency. This really helped me, thanks a lot! Now, I have better knowledge to handle big numbers. Phew...case closed then. Thanks again, really! Best Regards, "Niek Otten" wrote: I get #NUM too. Your numbers are quite big. If I divide all numbers by 100, I get 26.43%. -- Kind regards, Niek Otten Microsoft MVP - Excel "bakbuk" wrote in message ... I still have the problem with this matter. I tried it at my friend's computer and the result is still the same (#NUM!). I also tried by creating a new excel file but contain the same value, and it happened again. Both of us use Office2007, saved and tried the file in .xls and .xlsx format. Is there something wrong? thanks again. |
#7
|
|||
|
|||
IRR showed #NUM!
On Thu, 11 Dec 2008 12:37:44 +0100, "Niek Otten" wrote:
I get #NUM too. Your numbers are quite big. If I divide all numbers by 100, I get 26.43%. -- Kind regards, Niek Otten Microsoft MVP - Excel Niek, Why do you think these "large" numbers result in the #NUM error (which I get also in Excel 2007). I thought Excel uses an iterative technique to solve for IRR, finding the interest rate for which the NPV is zero. It must have something to do with the way Excel calculates IRR internally, but .... I set up to solve the IRR iteratively. I used the OP's original data in B2:B11 E3 will be my "guess" for the IRR. I then set up these formulas: E3: 10% G2: =B2 G3: =B3*(1/(1+$E$3)^ROWS($1:1)) Fill down to G11 G13: =SUM(G2:G11) I then used Goal Seek to set G13 to 0 by varying E3. And that comes up with the proper answer. The answer is the same as the IRR answer to 15 decimals. In other words, implementing what I thought was the IRR technique in a different way did not result in any error, and returned the correct answer. --ron |
#8
|
|||
|
|||
IRR showed #NUM!
Why do you think these "large" numbers result in the #NUM error (which
Hi. Here is my guess. IRR has two limitations. 20 Tries, and a change of .00001 When I use Goal Seek, and a start value of 10%, I get an answer close to zero of .0005. Goal Seek does not have to be that accurate. Although not documented in Excel 2007(afaik) I believe Goal Seek is able to iterate more than 20 times. (I thought it was documented in earlier versions??) I used a math program, and the Newton method to arrive at a full precision value of 0.264282301113724 However, when I plug this into the original equation, I'm left with a difference from zero of .00006. This is outside IRR's .00001 limit. Therefore, I believe the #Num error is due to not converging below ..00001 within 20 tries. What has me puzzled is if I enter this "guess" value into the IRR equation, I still get a #Num error. The Derivative of the NPV formulas shows a slope of about -2.4*10^12 near the solution. This is very high. Therefore, I believe IRR's troubles here is a combination of Excel's method of calculating a high derivative, a little loss of precision of the underlying numbers, and a limit of only 20 tries. = = = Dana DeLouis Ron Rosenfeld wrote: On Thu, 11 Dec 2008 12:37:44 +0100, "Niek Otten" wrote: I get #NUM too. Your numbers are quite big. If I divide all numbers by 100, I get 26.43%. -- Kind regards, Niek Otten Microsoft MVP - Excel Niek, Why do you think these "large" numbers result in the #NUM error (which I get also in Excel 2007). I thought Excel uses an iterative technique to solve for IRR, finding the interest rate for which the NPV is zero. It must have something to do with the way Excel calculates IRR internally, but .... I set up to solve the IRR iteratively. I used the OP's original data in B2:B11 E3 will be my "guess" for the IRR. I then set up these formulas: E3: 10% G2: =B2 G3: =B3*(1/(1+$E$3)^ROWS($1:1)) Fill down to G11 G13: =SUM(G2:G11) I then used Goal Seek to set G13 to 0 by varying E3. And that comes up with the proper answer. The answer is the same as the IRR answer to 15 decimals. In other words, implementing what I thought was the IRR technique in a different way did not result in any error, and returned the correct answer. --ron |
#9
|
|||
|
|||
IRR showed #NUM!
Thanks, Dana!
-- Kind regards, Niek Otten Microsoft MVP - Excel "Dana DeLouis" wrote in message ... Why do you think these "large" numbers result in the #NUM error (which Hi. Here is my guess. IRR has two limitations. 20 Tries, and a change of .00001 When I use Goal Seek, and a start value of 10%, I get an answer close to zero of .0005. Goal Seek does not have to be that accurate. Although not documented in Excel 2007(afaik) I believe Goal Seek is able to iterate more than 20 times. (I thought it was documented in earlier versions??) I used a math program, and the Newton method to arrive at a full precision value of 0.264282301113724 However, when I plug this into the original equation, I'm left with a difference from zero of .00006. This is outside IRR's .00001 limit. Therefore, I believe the #Num error is due to not converging below .00001 within 20 tries. What has me puzzled is if I enter this "guess" value into the IRR equation, I still get a #Num error. The Derivative of the NPV formulas shows a slope of about -2.4*10^12 near the solution. This is very high. Therefore, I believe IRR's troubles here is a combination of Excel's method of calculating a high derivative, a little loss of precision of the underlying numbers, and a limit of only 20 tries. = = = Dana DeLouis Ron Rosenfeld wrote: On Thu, 11 Dec 2008 12:37:44 +0100, "Niek Otten" wrote: I get #NUM too. Your numbers are quite big. If I divide all numbers by 100, I get 26.43%. -- Kind regards, Niek Otten Microsoft MVP - Excel Niek, Why do you think these "large" numbers result in the #NUM error (which I get also in Excel 2007). I thought Excel uses an iterative technique to solve for IRR, finding the interest rate for which the NPV is zero. It must have something to do with the way Excel calculates IRR internally, but .... I set up to solve the IRR iteratively. I used the OP's original data in B2:B11 E3 will be my "guess" for the IRR. I then set up these formulas: E3: 10% G2: =B2 G3: =B3*(1/(1+$E$3)^ROWS($1:1)) Fill down to G11 G13: =SUM(G2:G11) I then used Goal Seek to set G13 to 0 by varying E3. And that comes up with the proper answer. The answer is the same as the IRR answer to 15 decimals. In other words, implementing what I thought was the IRR technique in a different way did not result in any error, and returned the correct answer. --ron |
#10
|
|||
|
|||
IRR showed #NUM!
On Thu, 11 Dec 2008 09:13:07 -0500, Dana DeLouis wrote:
Why do you think these "large" numbers result in the #NUM error (which Hi. Here is my guess. IRR has two limitations. 20 Tries, and a change of .00001 When I use Goal Seek, and a start value of 10%, I get an answer close to zero of .0005. Goal Seek does not have to be that accurate. Although not documented in Excel 2007(afaik) I believe Goal Seek is able to iterate more than 20 times. (I thought it was documented in earlier versions??) I used a math program, and the Newton method to arrive at a full precision value of 0.264282301113724 However, when I plug this into the original equation, I'm left with a difference from zero of .00006. This is outside IRR's .00001 limit. Therefore, I believe the #Num error is due to not converging below .00001 within 20 tries. What has me puzzled is if I enter this "guess" value into the IRR equation, I still get a #Num error. The Derivative of the NPV formulas shows a slope of about -2.4*10^12 near the solution. This is very high. Therefore, I believe IRR's troubles here is a combination of Excel's method of calculating a high derivative, a little loss of precision of the underlying numbers, and a limit of only 20 tries. = = = Dana DeLouis OK, in Excel 2007, when I use Goal Seek, after having set up my worksheet as below, I get a result of: 26.4282301113724% If I use the array formula: =IRR(B2:B11/10) I obtain the same answer to Excel's 15 digit precision level They are slightly different, perhaps 5.33E-16 but close enough for gov't work g. Ron Rosenfeld wrote: On Thu, 11 Dec 2008 12:37:44 +0100, "Niek Otten" wrote: I get #NUM too. Your numbers are quite big. If I divide all numbers by 100, I get 26.43%. -- Kind regards, Niek Otten Microsoft MVP - Excel Niek, Why do you think these "large" numbers result in the #NUM error (which I get also in Excel 2007). I thought Excel uses an iterative technique to solve for IRR, finding the interest rate for which the NPV is zero. It must have something to do with the way Excel calculates IRR internally, but .... I set up to solve the IRR iteratively. I used the OP's original data in B2:B11 E3 will be my "guess" for the IRR. I then set up these formulas: E3: 10% G2: =B2 G3: =B3*(1/(1+$E$3)^ROWS($1:1)) Fill down to G11 G13: =SUM(G2:G11) I then used Goal Seek to set G13 to 0 by varying E3. And that comes up with the proper answer. The answer is the same as the IRR answer to 15 decimals. In other words, implementing what I thought was the IRR technique in a different way did not result in any error, and returned the correct answer. --ron --ron |
|
Thread Tools | |
Display Modes | |
|
|