Solving For IRR Based On Irregular Cash Flows

[kl271]

Hi guys,
I'm trying to solve for <abbr title="internal rate of return" style="box-sizing: border-box; border-bottom: none transparent; cursor: pointer; text-decoration-line: none; text-decoration-style: initial;">IRR</abbr> (let's say 20%) based irregular cash flows - essentially trying to find out what cash is required to arrive at an <abbr title="internal rate of return" style="box-sizing: border-box; border-bottom: none transparent; cursor: pointer; text-decoration-line: none; text-decoration-style: initial;">IRR</abbr> of 20%. So at year-0, the sponsor contributes $100 of cash in a 10-year project, I know for sure at year-1 the project will generate no cash. Then between years 2-5, the project generates $5 of cash annually. Between years 6-7, the project generates $10 of cash.
My question is, how do I find out the amount of project cash flows required in the last three years (years 8-10) to give the sponsor a 20% IRR? Thanks for your help!


[TABLE="width: 500"]
<tbody>[TR]
[TD]yr-0[/TD]
[TD]yr-1[/TD]
[TD]yr-2[/TD]
[TD]yr-3[/TD]
[TD]yr-4[/TD]
[TD]yr-5[/TD]
[TD]yr-6[/TD]
[TD]yr-7[/TD]
[TD]yr-8[/TD]
[TD]yr-9[/TD]
[TD]yr-10[/TD]
[/TR]
[TR]
[TD]($100)[/TD]
[TD]$0[/TD]
[TD]$5[/TD]
[TD]$5[/TD]
[TD]$5[/TD]
[TD]$5[/TD]
[TD]$10[/TD]
[TD]$10[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Target IRR = 20%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

There are an infinite number of solutions (within the limits of binary floating-point arithmetic and any rounding requirements).

If we assume that the cash flows in the last 3 years are equal, here is one solution.

[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]yr[/TD]
[TD="align: right"]cf[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-$100.00[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]$5.00[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]$5.00[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]$5.00[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]$5.00[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]$10.00[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]$10.00[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]$141.31[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]$141.31[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]$141.31[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]IRR[/TD]
[TD="align: right"]20.00%[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
B10:     =-(NPV(20%,B3:B9)+B2) / (1/1.2^8 + 1/1.2^9 + 1/1.2^10)
B11:B12: =$B$10
B13:     =IRR(B2:B12)

Note that the values in B10 and B13 are actually 141.310750945055 and 19.9999999996478%.

 

[joeu2004]

More generally:

[TABLE="class: grid, width: 300"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]yr[/TD]
[TD="align: right"]cf[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-$100.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]$5.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]rand%[/TD]
[TD="align: right"]rand[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]$102.0875
[/TD]
[TD="align: right"]23.12%[/TD]
[TD="align: right"]0.369772[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]$139.3250[/TD]
[TD="align: right"]31.55%[/TD]
[TD="align: right"]0.504650[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]$200.1752[/TD]
[TD="align: right"]45.33%[/TD]
[TD="align: right"]0.725056[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]IRR[/TD]
[TD="align: right"]20.00000000%[/TD]
[TD="align: right"]$441.5876
[/TD]
[TD]total[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
B10: =C10*$C$13
B11: =C11*$C$13
B12: =C12*$C$13
B13: =IRR(B2:B12)
C10: =D10/SUM($D$10:$D$12)
C11: =D11/SUM($D$10:$D$12)
C12: =D12/SUM($D$10:$D$12)
C13: =-(NPV(20%,B3:B9)+B2)/(C10/1.2^8 + C11/1.2^9 + C12/1.2^10)
D10: =RAND()
D11: =RAND()
D12: =RAND()

The cash flows in B10:B12 are a random percentage (C10:C12) of a total (C13).

Press f9 repeatedly to demonstrate the "infinite" number of solutions.

 

[pgc01]

Hi
Welcome to the board

Another option, playing with the financial functions:

=-PMT(20%,3,FV(20%,8,0,NPV(20%,B2:B9)),0)