Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IRR Calculation Frustrations
I am trying to utilize the IRR function in excel to calculate IRR for my
string of cash flows for 30 years: Year Cash Flows 2008 - 2009 852 2010 2,163 2011 11,181 2012 9,598 2013 7,774 2014 (203) 2015 329 2016 1,926 2017 (2,475) 2018 (22,903) 2019 (16,136) 2020 3,576 2021 (4,109) 2022 1,329 2023 1,401 2024 2,572 2025 (477) 2026 1,556 2027 1,370 2028 1,045 2029 571 2030 (13,113) 2031 6,178 2032 6,345 2033 6,515 2034 6,764 2035 7,039 2036 7,333 2037 7,672 2038 8,017 2039 8,329 residual 56,607 The formula I am using is: =(1+IRR("Range of Data),(1+0.0875)^0.25-1))^4-1 I am getting the "#DIV/0!" error. What am I missing here? Any assistance would be most appreciated. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IRR Calculation Frustrations
On Dec 3, 6:20*am, jkramos2005
wrote: =(1+IRR("Range of Data),(1+0.0875)^0.25-1))^4-1 I am getting the "#DIV/0!" error. What am I missing here? It's just another way that IRR says that you've exceeded its computational limits. As you may know, IRR is the periodic rate at which the sum of the discounted cash flows (NPV) is zero. You can compute each discounted cash flow in a parallel column with the formula: =A2/(1+$C$1)^(row(A2)-row($A$1)) were C1 contains the rate and your data are in A1:A33. If those formulas are in B2:B33, the NPV is =SUM(B2:B33) For our purposes here, it is also useful to simply compute the discount factor -- the denominator above -- in a parallel column, to wit: =1/(1+$C$1)^(row(A2)-row($A$1)) Now, increase C1 and watch what happens to the NPV and the last discount factor. The NPV becomes less than 0.01 rounded somewhere around 17,100,000% (!). But the real problem is: the discount factor becomes 0 around 411,300,000,000%, and NPV still is not zero (0.000000207148 rounded). IRR is trying to tell you that something is screwy with your data. Personally, I find it suspicious that you start with positive cash flows (inflows). Normally, we need to make an investment (negative outflow) before we see a "return" ;-). ------ original posting ----- On Dec 3, 6:20 am, jkramos2005 wrote: I am trying to utilize the IRR function in excel to calculate IRR for my string of cash flows for 30 years: Year * *Cash Flows 2008 * * - * 2009 * * 852 2010 * * 2,163 2011 * * 11,181 2012 * * 9,598 2013 * * 7,774 2014 * * (203) 2015 * * 329 2016 * * 1,926 2017 * * (2,475) 2018 * * (22,903) 2019 * * (16,136) 2020 * * 3,576 2021 * * (4,109) 2022 * * 1,329 2023 * * 1,401 2024 * * 2,572 2025 * * (477) 2026 * * 1,556 2027 * * 1,370 2028 * * 1,045 2029 * * 571 2030 * * (13,113) 2031 * * 6,178 2032 * * 6,345 2033 * * 6,515 2034 * * 6,764 2035 * * 7,039 2036 * * 7,333 2037 * * 7,672 2038 * * 8,017 2039 * * 8,329 residual * * * * 56,607 The formula I am using is: =(1+IRR("Range of Data),(1+0.0875)^0.25-1))^4-1 I am getting the "#DIV/0!" error. *What am I missing here? *Any assistance would be most appreciated. Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IRR Calculation Frustrations
PS....
On Dec 3, 12:51*pm, I wrote: But the real problem is: *the discount factor becomes 0 around 411,300,000,000%, and NPV still is not zero (0.000000207148 rounded). Of course, 1/x is never zero. I forgot to mention that it becomes zero because of the computational limitations of binary computer arithmetic. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenation frustrations.... | Excel Worksheet Functions | |||
irr, xirr, npv frustrations | Excel Worksheet Functions | |||
DSUM frustrations | Excel Worksheet Functions | |||
DSUM frustrations | Excel Worksheet Functions | |||
DSUM frustrations | Excel Worksheet Functions |