Excel IRR Function
K. Daiber
flows that vary between positive and negative over a
period of up to 54 months (although the first few are
invariably negative). When I try to calculate a monthly
IRR, I always get a NUM or DIV/0 error, but when I
collapse the monthly data into quarterly or annual
totals, Excel is able to calculate the IRR. 54 arguments
doesn't seem like it ought to be too much for Excel to
handle, but apparently it is. Is there any way to fix
this problem?
Norman Harker
Use a guess of -0.9 in the IRR function and see if it changes the response.
I think you'll find that a guess rate of 0.9 will always work (but I sit
here waiting to be corrected <vbg>).
With long cash flows the IRR default guess of 0.1 does not always find an
answer. This is especially the case with monthly cash flows because the IRR
is an effective rate per month and is likely to be further removed from the
10% rate seeded as a default.
That should get rid of #NUM! answers. DIV/0! errors are usually returned
when there are no sign changes in the cash flow; that is usually a case of,
"Doh!!" But there are investment "opportunities" that have that structure of
flows and in IRR terms we can only report an infinitely positive or negative
return.
With more than one change of sign in a cash flow there is always the
*possibility* of a multiple internal rate of return.
Be aware that where the cash flow signs change more than once during the
course of a project there is a possibility that the project balance also
changes. This might be considered practically relevant because the IRR
implies that positive and negative balances receive and pay interest at the
same rate.
"Collapsing" data to quarterly or annual totals (by simple aggregation of
flows) will give you an answer but is *not* recommended. You get significant
errors and the amount of those errors varies according to the precise
pattern of the actual cash flows. Any IRR from such summarized cash flows
should be accompanied by a "health warning".
HTH
Norman Harker
Sydney, Australia
"K. Daiber" <kda...@stanwichcapital.com> wrote in message
news:11cc901c26e14$b933dba0$37ef2ecf@TKMSFTNGXA13...
K. Daiber
Cost me 65 bp in yield, going to your point about
collapsing the cash flows.
Thanks so much for your help.
>.
>
Norman Harker
Sorry, the second reference to guess rate should have read -0.9 as per the
first one. This is the guess that "we" believe will always give you an IRR.
Interesting that there's a 65bp difference in the rate of return from
monthly as opposed to aggregated annual cash flows. Not an unusual result
though in terms of heavily front or end "loaded" cash flows and I think
you'll agree to my comment on "health warnings" being necessary for IRRs
from annualized flows. I certainly have problems with "true and fair view"
with annualized flows when you have potential for error of this magnitude or
above.
On some real life projects I've seen figures way above that level. In cases
where there is strongly "seasonal" patterns of income / expenditure you can
distort the picture quite nicely by choosing the month for your annual
analysis!
I think that for most purposes the task of presenting data and the task of
presenting the analysis should be separated. I see great merit in annual
schedules of flows but the analysis should be from monthly, or even daily
flows. The annual flow tables and analyses only need annotating to say that
the calculated returns are taken from monthly flows.
Perhaps of greater practical utility with projects, especially where there
is more than one sign change, are accumulation schedules. They allow
calculation of IRRs but also give data on:
Amounts required to be financed,
When it is required,
What is the maximum exposure and when.
When does the project balance turn positive.
Such schedules also "expose" some of the practical absurdities that are
built into IRR assumptions (i.e..e. that positive and negative balances
carry the same interest rate). Such schedules also allow greater flexibility
in terms of varying interest rates and considering various debt and equity
arrangements (including such gems as return on equity).
See John Walkenbach's Excel 2002 Formulas, page 369 for an example that
shows the basics of such an approach and contrast the data you can get from
that with data got from a simple DCF. Pages 342-345 also cover the problems
of multiple internal rates of return and the example showing the underlying
logic of Modified Internal Rate of Return is worth a close study.
In passing, it shows the vast superiority of Excel as a tool for examining
concepts and applications in comparison with formulas stuffed full of Greek
symbols that very few understand and even fewer understand in terms of
practical reality / relevance.
HTH
Norman Harker
Sydney, Australia
"K. Daiber" <kda...@stanwichcapital.com> wrote in message
news:6fcd01c26e24$05dcf540$2ae2...@phx.gbl...
Ron Rosenfeld
Are you entering the formula as 54 separate arguments (separated by
commas)?
If so, it won't work -- you need to enter the values as a single array.
--ron
Harlan Grove
...
>Use a guess of -0.9 in the IRR function and see if it changes the response.
>I think you'll find that a guess rate of 0.9 will always work (but I sit
>here waiting to be corrected <vbg>).
Presumably the '0.9' in the second line of the paragraph is a typo, and you
meant to write '-0.9'. If you meant '[+]0.9' I can give you lots of
counterexamples.
>That should get rid of #NUM! answers. DIV/0! errors are usually returned
>when there are no sign changes in the cash flow; that is usually a case of,
>"Doh!!" But there are investment "opportunities" that have that structure
of
>flows and in IRR terms we can only report an infinitely positive or
negative
>return.
Not true. You can get #DIV/0! with unusual cashflows and poor initial
guesses. For example,
=IRR({-10;-10;-10;-10;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;50},1)
SWAG: IRR returns #NUM! when it can't find an interval bracketting a zero in
a given number of iterations or blows out of the bracketted interval due to
a pathological secant estimate; it returns #DIV/0! when it brackets an
interval containing a zero but doesn't reach an acceptablely small
neighborhood of the zero in a fixed number of iterations.
>With more than one change of sign in a cash flow there is always the
>*possibility* of a multiple internal rate of return.
...
You could put this more strongly: there's a *distinct* *real-valued*
solution to the IRR for *every* sign change in the cumulative cashflow from
inception to each point in time. Further, given *any* cashflow with an odd
number of periods, N (so an even number of cashflows, N+1), there *MUST* be
a real-valued solution whether or not there's any sign changes.
>"Collapsing" data to quarterly or annual totals (by simple aggregation of
>flows) will give you an answer but is *not* recommended. You get
significant
>errors and the amount of those errors varies according to the precise
>pattern of the actual cash flows. Any IRR from such summarized cash flows
>should be accompanied by a "health warning".
True, but from the perverse perspective of rigid logical consistency this
same argument can be turned around and used to demonstrate that monthly
cashflows are inferior to weekly, weekly are inferior to daily, daily are
inferior to hourly, etc. ad nauseum.
However, this distracts from a more fundamental underlying principal: IRRs
are garbage. Under ideal conditions, you begin with cashflows of one sign
then at some all subsequent cashflows have the opposite sign, in which case
IRR gives you something akin to the yield on a bond. That was it's original
motivation. Unfortunately, real world projects typically have more than one
sign change in their cashflows. The fact that cashflows with multiple sign
changes perforce generate multiple IRRs means that IRRs have no economic
meaning and only slight mathematical utility in locating indifference
points.
A concrete example: the IRR for the cashflow {-a,+b,+c} is *NECESSARILY* the
same as the IRR for the cashflow {+a,-b,-c}. The cashflow {+10,+10,-15} has
an IRR of -17.71%. If you accept the naive belief that IRRs are somehow
related to NPVs, you probably think that a 'project' with a negative NPV is
a bad thing. Making this example real, if someone pays you $10 today and
tomorrow in exchange for you paying them $15 the day after tomorrow, would
you pass up the opportunity to clear $5 over 2 days just because you'd have
a negative IRR?
Back to theory. IRRs locate indifference points (which is why it's not a bad
thing that both sides of a series of transactions, both {-a,+b,+c} and
{+a,-b,-c}, have the same IRR). The bulk of financial economics can be
boiled down to one rule: positive NPVs good, negative NPVs bad, and nothing
to say one way or the other about zero NPVs because they're indifference
points. So how can IRRs be related to *POSITIVE* NPVs? If NPV(r,Cashflow) >
0, then NPV(r,Cashflow) > NPV(r',Cashflow) > 0 for r' slightly greater than
r, meaning the first partial derivative of the NPV with respect to r is
negative. NPV(IRR,Cashflow) = 0 by definition, so the *necessary* check for
IRRs is to calculate NPV(r'',Cashflow) where r'' is slightly greater than
IRR. If NPV(r'',Cashflow) > 0, then NO MATTER WHAT the IRR happens to be the
project has a *NEGATIVE* NPV at any discount rate consistent with risk
aversion. On the flip side, if NPV(r'',Cashflow) < 0, then the project has a
*POSITIVE* NPV at any discount rate consistent with risk aversion no matter
what the IRR is EVEN IF the IRR is negative!!
We should all live so long to be on the good side of 2 such 'negative IRR'
deals!
Note: my comments assume an inflationary or constant price level
macroeconomic environment. If you're in a deflationary macroeconomic
environment, then pretty much the entire body of classical and modern
financial economics is irrelevant, including NPV analysis, unless *ALL*
cashflows are adjusted to a common cost level, but once that's done my
comments still hold. Nominal cashflow analysis with underlying deflation is
fun & games with division by zero.
--
Public Service Announcement:
Don't attach files to postings in this newsgroup.
Norman Harker
You're running 46 minutes late and missed my correction of typo. Other point
raised re Div/0! was prefaced with "usually". You must now do penance for
lateness! <vbg>
Do you know of any cases where IRR is capable of being returned but where
the guess of -0.9 does not work? I've done quite a large number of them but
not yet come up with #NUM! But that doesn't mean to say it will always work.
Also, with a multiple IRR problem, do you know which of the answers it will
throw up?
Always curious here and it seems like you, if anyone, will know the answers.
But all the rest of your comments are fully accepted and worthy of close
study by decision makers.
You and a select band of others (including me) have been kicking the
proverbial out of IRR based analyses for years. Only in recent times have
people started to wonder whether there are other, more appropriate and
practically relevant bases for making decisions. Rather than use Discounted
Cash Flow analysis, I prefer to use Cash Flow analysis. And for the purposes
of making decisions, I much prefer a format that projects the reality as
expressed by summary schedules that include balances. The flows are a means
to and end and the end that we are interested in is balances. "Nice IRR
mate! Pity you exceed your cash resources in month 18!" and, "Great IRR!
Sensitivity testing shows little exposure to a change of interest rates.
Pity that your sensitivity model doesn't take into account the impact that
the interest rate rise has upon the other factors that impact upon outcome!"
But the real tool to use in this debate / revolution is Excel. Setting up
schedules that show what the IRR assumptions mean in practical terms with
non-standard cash flows must be the way to go! And once you start building
in inflation and risk elements, there are going to be a lot less "standard"
cash flows. All sorts of complex algebraic algorithms can be reduced to
explicit schedules that show what they really mean.
When you witness the impact of our current decision making tools in
developed and less developed countries over the last 30 years, perhaps you
might begin to wonder if we're doing something wrong? The application of the
IRR tool is just one. But note that I'm not criticizing the tool: only its
use. When a surgeon uses a spade for brain surgery, do we blame the spade or
the surgeon?
Regards.
Norman Harker
Sydney, Australia
"Harlan Grove" <Hrl...@aol.com> wrote
Harlan Grove
>the guess of -0.9 does not work? I've done quite a large number of them but
>not yet come up with #NUM! But that doesn't mean to say it will always
work.
=IRR({-1;-10;-100;1},-0.9) vs =IRR({-1;-10;-100;1},-0.99)
>Also, with a multiple IRR problem, do you know which of the answers it will
>throw up?
...
How apt! In the US dialect of the mother tongue 'throw up' means the same as
(I hope I'm remembering what little Strine I've ever 'learned') 'chunder'.
I'd guess the first one it brackets, so likely but not necessarily the one
closest to its initial estimate. Figuring out which would require graphing
the NPV for all interest rates in a large interval and seeing if the slope
in a neighborhood of the initial estimate is LARGE in absolute value.
HAND
Norman Harker
I'll write in open forum still because there are crucial Excel application
issues here. I'll top post my responses but you'll see a reasonably
co-coordinated thread, I hope.
That's five important Excel issues resolved! IRR Guess? Cases where #DIV/0!
is returned? Which multiple return? Getting all of the multiple returns?
Losses > 100% method? Not bad for a single thread! Interested? Then read on
for detail.
So as an IRR guess rate we can substitute -0.99 for -0.9. That will
"squeeze" the maximum out of the IRR although we'll have to see if Excel
will always hit on an answer in the required number of iterations. Whatever
is adopted, it's better than the Excel default and less prone to the dreaded
#NUM!
#Div/0! Most common cause may well be user stuff up in not putting
appropriate signs on the flows but there are other cases.
As to which IRR of several Excel will throw up when there are multiple rates
of return. I had wondered. But your view on closest to the bracket is
certainly the most obvious answer. (BTW Strine has various rich descriptions
of "throwing up" none of which are appropriate within 24 hours of eating).
I think my "bottom up" > "watch for sign change" > "get accurate figure" >
"continue up from there" approach should do it. Not very sophisticated but
easy enough for me to convert to a VBA routine and leave the computer to do
a few hundred thousand additional un-needed calculations in micro-seconds.
Sorry Harlan! But bugger the maths! I only want the answers and I really
don't care about the elegance of the algorithm if it works and I can explain
it in simple English / Strine to the next man. "But little does he know that
I'm the next man" (Spike Milligan as Bluebottle. Goon Show c. 1960)
What is an interesting conundrum is the possibility of a loss of >100%. This
arises in practice with some types of financial instruments where end result
is loss greater than the initial "investment". IRR can't handle that one!
Your "mirror image" views are interesting and use a principle I've taught
for years. That's the solution! I always say that you must establish the
persona or ownership of cash flows first. We merely reverse signs and adopt
"the market" as a single "persona" receiving the cash flows. The fact that
it exceeds 100%? No worries! Reverse persona = reverse result. Done! As are
those faced with margin calls etc!
To re-iterate the opening "claim". That's five important Excel issues
resolved! IRR Guess? Cases where #DIV/0! is returned? Which multiple
return? Getting all of the multiple returns? Losses > 100% method? Not bad
for a single thread!
Now for general issues of perhaps much greater significance but still
central to utility of Excel as a tool of financial analysis.
It's been great to read your strident criticisms of appropriateness of IRR
as a financial tool. We may approach the issue from different angles but we
are saying the same thing. Neither of us challenge the essential validity of
the maths. What I am now finding is that people in the real World are
looking at the patent assumptions behind my cash flow analysis. They agree
with those assumptions as reflecting practical reality. Output based on
those assumptions are "mere" Excel mechanics. When I present re-phrased DCF
and show the assumptions that IRR implies...answers are best provided in
plain brown envelopes.
There is no way that I could get acceptance based on the algebra even were I
capable of presenting (or even understanding) it. But it is important that
you maths galuys are saying the same thing.
I'll keep saying it: just use Excel to show what the IRR method is implying
in practical terms. If you can accept that (and in many cases it *is* OK)
there are no problems. But if the implications are ludicrous... "we must not
be seduced by the accuracy of our calculations" (Professor Tom Whipple).
It strikes me as self evident as a practitioner turned academic (Ha! Ha!)
that we must critically evaluate the data and critically evaluate the
assumptions of the model. The evaluation of any "mechanized" output of the
model must be conditioned by those two preliminary processes.
I also believe in starting from a sound micro investment / development
analysis base before proceeding to macro analysis. If all of the micro
analysis is distorted / biased in a single direction, I start to get worried
about macro analyses' results and their interpretation. And we've only
looked at the IRR input! That's enough for today!
Regards
Norman Harker
Sydney, Australia
"Harlan Grove" <hrl...@aol.com> wrote in message
news:#3CFHzpbCHA.2468@tkmsftngp10...