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.84 | 42.25% |
| IRR | 15.00% |
|
3
| CF 1 | -3,661.74 | 24.75% |
| IRR diff | 0.00E+00 |
|
4
| CF 2 | -4,882.31 | 33.00% |
|
|
|
|
5
| CF 3 | 1,293.59 |
|
| Check |
| diff
|
6
| CF 4 | 1,397.08 |
|
| %CF0 | 42.25% | 0.00E+00 |
7
| CF 5 | 1,508.84 |
|
| %CF1 | 24.75% | 0.00E+00 |
8
| CF 6 | 1,629.55 |
|
| %CF2 | 33.00% | 0.00E+00 |
9
| CF 7 | 1,759.92 |
|
|
|
|
|
10
| CF 8 | 1,850.16 |
|
| NPV CF 0-2 | -13,126.69 |
|
|
|
|
|
|
|
|
|
23
| CF 21 | 4,756.78 |
|
|
|
|
|
24
| CF 22 | 79,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%.