Strange IRR calculation

M

MannyT

I have an analysis involving cashflows for two scenarios.
The ONLY difference between the two scenarios is that
Scenario1 has a gain from a sale (hence, the positive
cashflow in Year0) while Scenario2 has no gain from sale.

Scenraio1 Scenario2
Year0 $254,486 ($221,750)
Year1 $384,508 $384,508
Year2 ($13,438) ($13,438)
Year3 ($12,038) ($12,038)
Year4 ($11,258) ($11,258)
Year5 ($11,068) ($11,068)
Year6 ($11,068) ($11,068)
Year7 ($11,068) ($11,068)
Year8 ($11,068) ($11,068)
Year9 ($11,068) ($11,068)
Year10 ($376,429) ($376,429) -- these are terminal values

Using the Excel IRR function, I get the following IRRs:
IRR: Scenerio1 -3.56%
IRR: Scenario2 19.95%

The NPV for the two scenarios, using a discount rate of
11%, is as follows:
NPV: Scenario 1 $414,247
NPV: Scenario 2 ($61,989)

My question:
Given that I am getting an upfront gain from a sale in
Scenario1, shouldn't the internal rate of return be better
(i.e. higher) than Scenario2 with no upfront gain??
The higher NPV for Scenario1 makes more sense to me. What
am I missing with the IRR?

Any help/insight is appreciated.
 
H

Harlan Grove

MannyT said:
I have an analysis involving cashflows for two scenarios.
The ONLY difference between the two scenarios is that
Scenario1 has a gain from a sale (hence, the positive
cashflow in Year0) while Scenario2 has no gain from sale.

Scenraio1 Scenario2
Year0 $254,486 ($221,750)
Year1 $384,508 $384,508
Year2 ($13,438) ($13,438)
Year3 ($12,038) ($12,038)
Year4 ($11,258) ($11,258)
Year5 ($11,068) ($11,068)
Year6 ($11,068) ($11,068)
Year7 ($11,068) ($11,068)
Year8 ($11,068) ($11,068)
Year9 ($11,068) ($11,068)
Year10 ($376,429) ($376,429) -- these are terminal values

Using the Excel IRR function, I get the following IRRs:
IRR: Scenerio1 -3.56%
IRR: Scenario2 19.95%

The NPV for the two scenarios, using a discount rate of
11%, is as follows:
Click to expand...
....

Irrelevant. The NPVs for each scenario using its own IRR result as the
discount rate give $0.00, so both are correct IRR results whether or not
they make sense to you.
My question:
Given that I am getting an upfront gain from a sale in
Scenario1, shouldn't the internal rate of return be better
(i.e. higher) than Scenario2 with no upfront gain??
The higher NPV for Scenario1 makes more sense to me. What
am I missing with the IRR?
Click to expand...

In scenario 1, the sum of the initial positive cashflows exceeds the sum of
the subsequent negative cashflows. Invariably that means the IRR will be
negative. This would make more sense if you reversed the signs, so the
initial cashflows were negative and the subsequent ones positive. Then you'd
never reach a positive cumulative balance, thus the negative IRR.

What you're missing is the fact that IRRs are almost entirely useless. All
they are are the interest rates that render a zero NPV for a sequence of
cashflows. Nothing more. That's why the IRR is the same if you reverse the
signs of all cashflows.

As for the second scenario, the IRR is positive because there are multiple
sign changes in the cashflow, and that almost always leads to seriously
screwy results. In this case, there's a second IRR of 62.61%. It's
absolutely as meaningful as the 19.95% IRR, i.e., not at all.

Whatever you're trying to do, you're not going to achieve it using IRRs.
Also, if you're calculating NPVs for these scenarios, NPVs *are* sensitive
to signs. If positives mean cash inflows, negatives cash outflows, and the
inflows preceed the outflows, then risk-adjusted discount rates are *LESS*
*THAN* risk-free interest rates. So your 11% NPVs are meaningless. NPVs at
1.0% discount rates would be more meaningful.
 
F

Fred Smith

IRR does not know where your year 0 amount comes from. It just deals with
the numbers you give it.

Look at it without any percentages -- just deal with profit or loss.

In scenario 1, you invested $638,984 (254486 + 384508). Your return was
$468,503. So you lost $170,481. If you lose money, IRR has to be negative.

In scenario 2, you invested $384,508. Your return was $690,253. So you made
$306,745. Hence IRR is positive.

So your IRR results look right to me, given the data you fed it.

I would question your year 0 cash flow for scenario 2. You say there is "no
gain from sale". But if there is no gain, why did you tell IRR you had a
return of $221,750 in year 0 -- before you've even invested any money, no
less?
 
Top