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!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,977
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!
 

cuppcakke

New Member
Joined
Apr 3, 2017
Messages
3
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>
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,977
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.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,580
Office Version
2010
Platform
Windows
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
[HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR]
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
[HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR]
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:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,580
Office Version
2010
Platform
Windows
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
[HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR][HR][/HR]
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:

cuppcakke

New Member
Joined
Apr 3, 2017
Messages
3
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!
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,580
Office Version
2010
Platform
Windows
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.
 

Forum statistics

Threads
1,081,984
Messages
5,362,554
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top