Internal Rate of Return - Multiple Changes in Cash Flows (Positive and Negative)

CFA Level I
#1

I am analyzing an 11-year investment project. After an initial investment period, the project cash flows are positive in years 1 to 3 but turn negative from years 4 through year 11. The initial investment period is actually two years. The Internal Rate of Return (IRR) I get in excel is approximately 20.0 percent.

I am aware of the inherent flaws with the IRR but not sure how to overcome or handle it. I know excel must NOT be giving the right answer because as a cross-check I tested what the NPV is at a 20% discount rate. The indicated NPV in excel is $15.8 million. Since the IRR is the discount rate that drives NPV to Zero I know something is wrong. Also, I understand that the MIRR is suggested as a remedy but I am not sure how to do it. I think the biggest issue is the project costs are simply too much.

The Initial Investment and the Project Cash Flows are below with (negative cash flows) in parentheses:

Initial Initial Investment Period 1: ($80 million)

Investment Period 2: ($80 million)

Year 1: $90 million

Year 2: $93.4 million

Year 3: $95 million

Year 4: ($4.5 million)

Year 5: ($4.1 million)

Year 6: ($3.8 million)

Year 7: ($3.5 million)

Year 8 ($3.1 million)

Year 9: ($2.7 million)

Year 10 ($2.2 million)

Year 11: ($84.1 million) Note that in year 11 there is a big loan payoff hence the negative $84.1 million.

If the project is not financed and done 100% with equity I get a 6.0 percent IRR (but the initial investment is $200 million each in Year 1 and Year 2.

Any insight is appreciated. Thank you.

#2

The easiest way to calculate the possible IRRs on a project with multiple sign changes is “brute force” it. In other words, do a one variable data table and estimate NPV for a wide range of discount rates. When you do, you’ll see that the NPV is Negative for large negative rates, maxes at about roughly +6%, and then decreases. It shows zero NPVs at roughly -2% and +20 % (-1.89% and 19.94% to be more precise).

#3

you could just use a financial calculator.

BAII Plus gives me -1.894012411 or about -1.894%

(unless, of course, if i made a mistake when entering the numbers)

#4

A financial calculator will give you one IRR (assuming that there is one), but not necessarily the sensible one (when there’s more than one). For that, you’re better off using Excel to brute force it as busprof suggests.

Note that on the exam, the correct answer will be whatever the calculator gives, whether it’s the sensible IRR or not.

#5

I got an NPV of zero using the IRR that Excel generated.

#6

S2000Magician: Interesting. I re-entered the data into Excel and I am now getting the same result you are getting. A 19.9 percent IRR and an NPV of $0 (in Excel).

I will try to brute force it as others have suggested. I appreciate the replies. Thanks to all.