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.