IRR Calculation Frustrations

[jkramos2005]

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!

 

[joeu2004]

=(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 -----

 

[joeu2004]

PS....

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.