IRR and NPV

[tom ossieur]

Hi,

IRR, according to explanation in Excel, is of a similar concept with NPV.

But if my cash flow starts from say 2009 onwards (and any cash flows between
2007-2009 are 0), IRR gives me the same result whether or not I choose cells
starting from 2007 or 2009.

But with NPV function, the results are different. How can I get the right
IRRs for different periods with the IRR function?

Thanks!

tom

 

[tom ossieur]

Hi,

I add an example, hoping that it will stimulate replies

A B C D E F
year 2006 2007 2008 2009 2010
- - -9,024,420 -580,864 -85,079
Equity IRR 9.60% 9.60% 9.60%

(actually the table goes to 2029)
Shouldn't IRR starting from different years - assuming that the cashflow in
the starting year (2006 or 2007) are both zero -, both ending in 2029 give
different results?

 

[joeu2004]

IRR, according to explanation in Excel, is of a similar concept with NPV.

They are __related__, not "similar". The IRR is the rate that causes
the NPV to be zero.

But if my cash flow starts from say 2009 onwards (and any cash flows between
2007-2009 are 0), IRR gives me the same result whether or not I choose cells
starting from 2007 or 2009.

There are many ways to try to explain this. Mathematically, if you
look at the formula on the Excel NPV help page, it might become
"obvious" to you. The Excel formula is:

NPV = 0 = CF1/(1+r)^1 + CF2/(1+r)^2 + CF3/(1+r)^3 + CF4/(1+r)^4 + ....

If the first k terms (e.g. CF1 through CF3 in your case) are zero, then
the IRR (r) must be chosen so that the later N-k terms sum to zero. No
matter how many zero cash flows you put in front of them, the IRR will
be the same since 0/(1+r)^x is zero for any x. Put another way, we can
multiply the equation by (1+r)^3 (if the first 3 terms are zero) to
convert it to the following equivalent equation:

NPV = 0 = CF1/(1+r)^(-2) + CF2/(1+r)^(-1) + CF3/(1+r)^0 + CF4/(1+r)^1 +
.....

Dropping the first three terms, which are zero, we get:

NPV = 0 = CF4/(1+r)^1 + ....

So the IRR is unaffect by the initial zero cash flows. In effect, the
IRR is the rate that causes the NPV to be zero, starting with the first
non-zero cash flow.

But with NPV function, the results are different.

I am not sure what you mean. The NPV can be any value you want,
depending on the rate that you choose. And yes, given the same chosen
rate, adding more initial cash flows of zero will change the NPV (if it
is non-zero) because the value of the first non-zero cash flow --
CFn/(1+r)^n -- will change.

However, there is only one rate for which the NPV will be zero. (Well,
that might be an exaggeration mathematically speaking. Perhaps I
should say that we hope there is only one __practical__ rate.)

How can I get the right
IRRs for different periods with the IRR function?

"For different periods"? The question does not make sense to me. The
IRR is "the" rate for all periods of the series of cash flows. You can
find the IRR for different series of cash flows, for example composed
of fewer and fewer consecutive periods. But I am not sure that is what
you mean.

 

[joeu2004]

I add an example, hoping that it will stimulate replies

Patience is a virtue that you should learn. In any case, the example
does not help to understand whatever it is you are trying to do.

A B C D E F
year 2006 2007 2008 2009 2010
- - -9,024,420 -580,864 -85,079
Equity IRR 9.60% 9.60% 9.60%

In a later thread, you replace the first term (-9,024,420) with zero.
I will assume that is your intention, which forestalls some other
criticisms of this example.

(actually the table goes to 2029)

The example is not useful to me.

First, it is difficult to see how the IRR values should align with the
cash flows. That is not your fault: my newsreader does not do a good
job of presenting columnarized data.

Second, it might be helpful to see the formulas that you use to compute
the IRR. They should not all be the same, unless they align with the
zero (and "-") cash flows. If they are indeed all the same through the
year 2029, I suspect that you made one of several possible mistakes.
Some human errors that come to mind are: (1) perhaps you used an
absolute cell reference (e.g. $B$3) for the starting cash flow; or (2)
perhaps you copied the IRR formula while you had manual calculation
enabled; or (3) perhaps the numbers are such that the IRR __appears__
to be the same (but it is not) up to the 4th fractional digit; or (4)
perhaps the IRR formula is completely wrong altogether; or ....

Third, in order to check your results, we need to see __all__ cash
flows all the way through 2029. Obviously that would be tedious to
show in a row. I suggest that you present the data in a column.

Shouldn't IRR starting from different years - assuming that the cashflow in
the starting year (2006 or 2007) are both zero -, both ending in 2029 give
different results?

I hope I answered that question in my response to the first article
that you posted in the thread. If not, please write back with more
questions.

 

[tom ossieur]

Hi Joe(?)!

Thank you very much for your detailed answer! and I knwo patience is a
virtue, though sometimes it is difficult

i posted this question for a friend and many thanks from her - and me - for
these answers once again!

tom