Goal seek to determine IRR for a series of cashflow by changing the initial outlay

cuppcakke

New Member
Joined
Apr 3, 2017
Messages
3
How do i obtain a target IRR by leaving my cashflow fixed and changing my initial outlay using goal seek?

I am trying to determine what my initial cashflow will be to obtain a target IRR with a fixed series of cashflows.

Thanks in advance!
 

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).
Re: How do i use goal seek to determine IRR for a series of cashflow by changing the initial outlay

Welcome to the forum.

If your layout is like this:

AB
1-74.7810%
210
320
415
510
650

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
B1=IRR(A1:A6)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Just select B1, from the Data tab click What-If Analysis > Goal Seek,

and Set cell: B1
To value: 10%
by changing cell A1.

Good luck!
 
Upvote 0
Re: How do i use goal seek to determine IRR for a series of cashflow by changing the initial outlay

Welcome to the forum.

If your layout is like this:

AB
1-74.7810%
210
320
415
510
650

<tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
B1=IRR(A1:A6)

<tbody>
</tbody>

<tbody>
</tbody>



Just select B1, from the Data tab click What-If Analysis > Goal Seek,

and Set cell: B1
To value: 10%
by changing cell A1.

Good luck!

Thanks for your help Eric. How i wish it was that straight forward. I have a total development cost i am making over a 3 year period i.e my initial outlay is over a 3 year period and my cashflows are fixed. How do i tweak the cash outflows that will give me a target IRR of 15%? Please see my data below



FV
Net Cashflow Year 0 (11,100.76)
Net Cashflow Year 1 (6,500.82)
Net Cashflow Year 2 (8,667.76)
Net Cashflow Year 3 1,293.59
Net Cashflow Year 4
1,397.08
Net Cashflow Year 5
1,508.84
Net Cashflow Year 6
1,629.55
Net Cashflow Year 7 1,759.92
Net Cashflow Year 8 1,850.16
Net Cashflow Year 9 1,998.17
Net Cashflow Year 10 2,158.02
Net Cashflow Year 11 2,330.67
Net Cashflow Year 12
2,517.12
Net Cashflow Year 13 2,644.21
Net Cashflow Year 14 2,855.75
Net Cashflow Year 15
3,084.21
Net Cashflow Year 16
3,330.95
Net Cashflow Year 17
3,597.42
Net Cashflow Year 18
3,776.08
Net Cashflow Year 19 4,078.17
Net Cashflow Year 20 4,404.42
Net Cashflow Year 21 4,756.78
Net Cashflow Year 22 79,376.01

<colgroup><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
Re: How do i use goal seek to determine IRR for a series of cashflow by changing the initial outlay

I think you're going to need to explain some more conditions. The method I described above works on that data. It results in the top row = -1,042.25, and all other values the same, and gets your 15% IRR. If you want the other outlay years to be roughly equal, you can manually change the values in B1:B3 so that the sum is roughly the same, and the IRR is what you want. With just a little time manually experimenting, I got your 15% IRR with these outlays:
-5750, -5000, -4000.

If you're looking for an automated process to calculate that, you'd need the Solver. Set target to 15%, by varying cells B1:B3, and add some constraints so that B1:B3<0, and the maximum difference between B1:B3 < 1000. Let me know if you need more detail on that.
 
Upvote 0
Re: How do i use goal seek to determine IRR for a series of cashflow by changing the initial outlay

i wish it was that straight forward. I have a total development cost i am making over a 3 year period i.e my initial outlay is over a 3 year period and my cashflows are fixed. How do i tweak the cash outflows that will give me a target IRR of 15%? Please see my data below

It is. You do not need Goal Seek or Solver. In fact, they will not help you.

At issue: there are any number of solutions, unless you provide some constraints among the first 3 cash flows (outlays).

One solution....


A
B
C
D
E
F
1
Target IRR
10.23%
NPV CF 0-2-24,131.15Enter 2
2
CF 0-11,100.76
CF 0-11,100.76-11,100.76
3
CF 1-6,500.82
CF 1-6,500.82-6,500.82
4
CF 2-8,667.76
CF 2-8,667.76
5
CF 31,293.59



6
CF 41,397.08
Check IRR10.23%
7
CF 51,508.84
Check diff-2.64E-16







23
CF 214,756.78



24
CF 2279,376.01




<tbody>
</tbody>
Code:
B1: [COLOR=#ff0000]=IRR(B2:B24)[/COLOR]
E1: =-NPV(B1,B5:B24) / (1+B1)^2
E2: =IF(COUNT($F$2:$F$4)<>2, "", IF(F2<>"", F2, E1 - NPV(B1,F3:F4)))
E3: =IF(COUNT($F$2:$F$4)<>2, "", IF(F3<>"", F3, (E1 - NPV(B1,F2,0,F4)*(1+B1))*(1+B1)))
E4: =IF(COUNT($F$2:$F$4)<>2, "", IF(F4<>"", F4, (E1 - NPV(B1,F2:F3)*(1+B1))*(1+B1)^2))
[COLOR=#ff0000]E6: =IRR((E2:E4,B5:B24))
E7: =E6-B1[/COLOR]

Normally, the formulas in E2:E4 would be in B2:B4. I just left B2:B4 as you had them.

The formula in B1 is provided as "proof of concept". You would enter 15%.

The formulas in E6:E7 are provided as "proof of concept". They are not part of solution.

With the insufficient information that you provided, the best we can do is calculate the NPV of CF0, CF1 and CF2, as demonstrated in E1.

But if you provide any 2 of CF0, CF1 and CF2 in F2:F4, the formulas in E2:E4 derive the remaining CF.

A word about Excel NPV.... Like you, normally we label the first CF and "present time" as CF0. And we expect that NPV(B1,CF0:CFn) calculates the NPV to time 0. In other words, NPV = CF0 + CF1/(1+B1) +....

But Excel NPV = CF0/(1+B1) + CF1/(1+B1)^2 +.... So to calculate NPV as we expect, one way is to use the formula CF0+NPV(B1,CF1:CFn). Another way, which I use above, is NPV(B1,CF0:CFn)*(1+B1).

-----

If you do not want to enter any 2 of CF0, CF1 and CF2, you need to specify a relationship among them.

For example, suppose you want their values to decline at a rate of 10%, specified in E2.

Then the solution would be....


A
B
C
D
E
1
Target IRR
15.00%
NPV CF 0-2-13,126.69
2
CF 0-5,480.68
CF 0-2 %change-10.00%
3
CF 1-4,932.61


4
CF 2-4,439.35


5
CF 31,293.59


6
CF 41,397.08
Check IRR15.00%
7
CF 51,508.84
Check diff-3.05E-16






23
CF 214,756.78


24
CF 2279,376.01



<tbody>
</tbody>
Rich (BB code):
B2: =E1 / (NPV(B1,1,(1+E2),(1+E2)^2)*(1+B1))
B3: =B2*(1+E2)
B4: =B3*(1+E2)
E1: =-NPV(B1,B5:B24)/(1+B1)^2
E6: =IRR(B2:B24)
E7: =E6-B1

E1 and E7 are unchanged. E6 is simplified. Again, E6:E7 are not part of the solution.
 
Last edited:
Upvote 0
Re: How do i use goal seek to determine IRR for a series of cashflow by changing the initial outlay

You do not need Goal Seek or Solver. In fact, they will not help you.

I probably should not have said that last part.

The following model might be more like what you have in mind. We can solve it directly, or we could use Goal Seek or Solver. The latter might be more appealing because it does not require knowledge of NPV and algebra.


A
B
C
D
E
F
G
1
Target IRR
15.00%%Outlay

Total Outlay-14,794.89
2
CF 0-6,250.8442.25%
IRR15.00%
3
CF 1-3,661.7424.75%
IRR diff0.00E+00
4
CF 2-4,882.3133.00%



5
CF 31,293.59

Check
diff
6
CF 41,397.08

%CF042.25%0.00E+00
7
CF 51,508.84

%CF124.75%0.00E+00
8
CF 61,629.55

%CF233.00%0.00E+00
9
CF 71,759.92




10
CF 81,850.16

NPV CF 0-2-13,126.69








23
CF 214,756.78




24
CF 2279,376.01





<tbody>
</tbody>
Rich (BB code):
B2: =C2*$F$1
B3: =C3*$F$1
B4: =C4*$F$1
F1: =F10/(NPV(B1,C2:C4)*(1+B1))
F2: =IRR(B2:B24)
F3: =F2-B1
F6: =B2/$F$1
F7: =B3/$F$1
F8: =B4/$F$1
F10: =-NPV(B1,B5:B24)/(1+B1)^2
G6: =F6-$C$2
G7: =F7-$C$2
G8: =F8-$C$2

B1 and C2:C4 are data entry. You enter your goals.

F6:F8 and G6:G8 are provided as double-checks. They are not needed for the solution.

In your original example, B2:B4 might be construed as percentages of the total outlay of -26,269.34. That is a very common development model.

In that case, the formula in F1 calculates the total outlay such that the distribution of the costs in C2:C4 are needed in order to achieve the IRR in B1. F1 depends on the formula in F10, which calculates the required NPV of CF0:CF2.

With those formulas, F2 and F3 are not needed for the solution.

-----

However, if prefer to use Goal Seek or Solver, it is very straight-forward,

Remove the formula in F1. Instead, enter an initial value of -1. Unfortunately, Goal Seek and Solver do not work well if F1 is empty, unless we modify some other formulas as well.

The formula in F10 is not needed for the solution.

For Goal Seek, I use F3 to make the set-up more flexible. But they could be avoided.

Set up Goal Seek as follows:

Set cell: F3
To value: 0
By changing cell: F1

Alternatively, you can use Set Cell F2 and To Value 15%, and F3 is not needed for the solution. (But it is a nice double-check.)

If you use Goal Seek as-is, F2 might be 15.02% instead of 15.00%. (In Excel 2007, at least.) That is because of the internal default convergence tolerance for the operation. Solver comes closer to 15.00% using its default tolerance.

However, we can improve the Goal Seek result by changing Max Change under Enable Iterative Calculation in the Excel Options > Formulas section. Note: Do __not__ enable Iterative Calculation.

You could also change Max Iterations. But it was necessary in my trials.

For example, when I enter 1E-11 (in Excel 2007), Goal Seek sets F2 to about 15.0000000000357%.
 
Last edited:
Upvote 0
Re: How do i use goal seek to determine IRR for a series of cashflow by changing the initial outlay

Thanks for your help guys! i Appreciate!
 
Upvote 0
Re: How do i use goal seek to determine IRR for a series of cashflow by changing the initial outlay

Thanks for your help guys! i Appreciate!

You're welcome, if my detailed examples were helpful.

FYI, minor errata....


we can improve the Goal Seek result by changing Max Change under Enable Iterative Calculation in the Excel Options > Formulas section. Note: Do __not__ enable Iterative Calculation.

You could also change Max Iterations. But it was necessary in my trials.

I meant to write "was not necessary". Omitting the word "not", especially an emphatic "not", is a bane of my existence.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top