# Solving For IRR Based On Irregular Cash Flows

#### kl271

##### New Member
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>

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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>
Rich (BB code):
``````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%.

Last edited:
More generally:

ABCD
1yrcf
20-\$100.00
31\$0.00
42\$5.00
53\$5.00
64\$5.00
75\$5.00
86\$10.00
97\$10.00rand%rand
108\$102.0875
23.12%0.369772
119\$139.325031.55%0.504650
1210\$200.175245.33%0.725056
13IRR20.00000000%\$441.5876
total

<tbody>
</tbody>

Rich (BB code):
``````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.

Hi
Welcome to the board

Another option, playing with the financial functions:

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

Last edited:

Replies
3
Views
992
Replies
3
Views
1K
Replies
3
Views
97
Replies
1
Views
686
Replies
7
Views
1K

1,196,269
Messages
6,014,352
Members
441,816
Latest member
Klingon1960

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back