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!

yr-0	yr-1	yr-2	yr-3	yr-4	yr-5	yr-6	yr-7	yr-8	yr-9	yr-10
($100)	$0	$5	$5	$5	$5	$10	$10	?	?	?
Target IRR = 20%

<tbody>
</tbody>

 

[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.

	A	B
1	yr	cf
2	0	-$100.00
3	1	$0.00
4	2	$5.00
5	3	$5.00
6	4	$5.00
7	5	$5.00
8	6	$10.00
9	7	$10.00
10	8	$141.31
11	9	$141.31
12	10	$141.31
13	IRR	20.00%

<tbody>
</tbody>

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:

	A	B	C	D
1	yr	cf
2	0	-$100.00
3	1	$0.00
4	2	$5.00
5	3	$5.00
6	4	$5.00
7	5	$5.00
8	6	$10.00
9	7	$10.00	rand%	rand
10	8	$102.0875	23.12%	0.369772
11	9	$139.3250	31.55%	0.504650
12	10	$200.1752	45.33%	0.725056
13	IRR	20.00000000%	$441.5876	total

<tbody>
</tbody>

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)