IRR formula seems wrong

You are giving it a guess of 90 (9000%) which is leading it towards the wrong solution. Try a guess of 0.01.

Originally Posted by aalsaffa

I have the same data in 2 different scenarios. Scenario 1 i use 4BN in year 1 and 1BN in year 2. Scenario 2 i use 3BN in year 1 and 2 BN in year 2. However you can see the IRR changes significantly. Can someone please help me with this?

When you shifted 1 bln from Year 1 to Year 2 in Scenario 2, you made a huge paradigm shift in the model.

Whereas Scenario 1 has only 1 sign change in the Totals line, Scenario 2 has 2 sign changes. As the wiki page for "internal rate of return" explains, the more sign changes there are in the CFs, the more likely it is that there are multiple IRRs.

I like to build a table of NPV values for varying discount rates.

	C	D	E
30	%Dscnt	Scenario 1	Scenario 2
31	-99%	3.11E+74	3.11E+74
32	-90%	3.41E+41	3.41E+41
33	-80%	4.45E+31	4.45E+31
--	--	--	--
39	-20%	2.15E+12	2.15E+12
40	-10%	5.60E+10	5.59E+10
41	0%	-3.94E+09	-3.94E+09
42	10%	-3.87E+09	-3.79E+09
--	--	--	--
57	700%	-1.14E+08	-4.67E+06
58	800%	-1.00E+08	-1.56E+06
59	900%	-8.95E+07	5.18E+05
60	1000%	-8.07E+07	1.93E+06

Please Login or Register  to view this content.

There, we see both the problem and the solution clearly.

I highlighted the pairs of discount rates where the NPV changes sign. Presumably, NPV=0 lies in between, and that discount rate is an IRR.

For both Scenario 1 and 2, presumably NPV=0 lies between -10% and 0%. A guess of -5% might work for both Excel IRR calculations. In fact, Excel IRR(...,-5%) finds -3.34% for Scenario 1 and -3.36% for Scenario 2.

For Scenario 2, presumably NPV=0 also lies between 800% and 900%. But that is not the case for Scenario 1. Although specifying a guess of 850% might find that IRR, intuition suggests that we are not interested in that IRR because it is unique to Scenario 2.

BTW, the "guess" parameter is a percentage. 90 is 9000%. If you wanted 90%, write 90% or 0.9.