Re: IRR Calculation Frustrations

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Dec 3, 6:20 am, jkramos2005 <jkramos2...@xxxxxxxxxxxxxxxxxxxxxxxxx>
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 <jkramos2...@xxxxxxxxxxxxxxxxxxxxxxxxx>
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!

.

Relevant Pages

  • IRR, NPV
    ... NPV discounts future cash flows at a constant interest ... algorithim to solve for an discount rate that equates to ... There is more than 1 IRR solution if you have cash flows ...
    (microsoft.public.excel.programming)
  • Re: IRR and NPV
    ... the NPV to be zero. ... If the first k terms are zero, ... the IRR must be chosen so that the later N-k terms sum to zero. ... So the IRR is unaffect by the initial zero cash flows. ...
    (microsoft.public.excel.worksheet.functions)
  • irr, xirr, npv frustrations
    ... I have a series of monthly cash flows for a project. ... Why is my IRR not working when XIRR is? ... Why is my NPV check not matching the XIRR result? ...
    (microsoft.public.excel.worksheet.functions)
  • RE: XNPV vs. NPV(quarterly) different results
    ... if you want to compare XNPV() ... and NPV() results, use RATEfor the ... XNPVuses a daily compounding rate, ... cash flows using 16%/4 for the periodic rate. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: XIRR versus IRR
    ... returned by the XIRR or convert the XIRR to the quarterly effective ... rate returned by the IRR from quarterly cash flows. ...
    (microsoft.public.excel.misc)