Goal Seek returning different results

[Don Kline]

I am using Goal Seek to determine an interest rate for an investment stream
that returns a zero. The investment is irrgular in amount but the period is
annual for each transaction.

My problem is I am getting different answers depending on the source of the
numbers.

BUT the imported investment stream is the same regardless of the source. If
I run it from one source Goal Seek returns 3.1362%. If I flip to the "other"
source, Goal Seek returns 3.2072%

I am stymied as to why there are different results depending on identical
sources.

Below is the investment stream

8,616.77
8,616.77
8,616.77
8,616.77
8,616.77
8,616.77
8,616.77
8,616.77
8,616.77
8,616.77
8,616.77
8,616.77
8,616.77
8,616.77
8,616.77
8,616.77
3,269.89
2,062.33
940.42
-165.44
-18,279.87
-20,304.66
-22,524.53
-25,033.38
-27,804.93

 

[JoeU2004]

If I run it from one source Goal Seek returns 3.1362%. If I flip to
the "other" source, Goal Seek returns 3.2072%

Well, I don't see how you get either of those numbers or even close. I get
about -1.6401%, if I understand your problem correctly. I get that with
IRR, as well as with Goal Seek and Solver. IRR requires a hint .

I believe you are saying that you invest the positive amounts for 19 years,
then you withdraw the negative amounts for 6 years, and you expect a balance
of zero. All transactions are at the beginning of the year.

As for the different results depending the source, well, I guess I don't
know what you mean by "source". Exactly where are you importing the values
from, and how?

My first impression was a rounding problem; format the cells to 4 decimal
places. But in my experiments with the numbers provided, that did not
change the result significantly -- still -1.6401% to 4 dp.


----- original message -----

 

[Don Kline]

I'm sorry as I have mistated the question. Mea culpa.

Upon rereading it, I left out a vital piece. The listing of the amounts at
the bottom of the initial posting is the entire investment stream. The goal
seek is to get the rate that when applied to the entire investment stream
will return a value of 779,454.

 

[JoeU2004]

I'm sorry as I have mistated the question. Mea culpa.
[....] the entire investment stream will return a value of 779,454.

Sorry, but I cannot help you. You're still not telling the whole story, at
least not accurately.

With my previously-stated understanding of the cash flow, to end up with
779,454, the rate of return is either about 10.7543% or about 11.2807%,
depending on interpretation. I get that with IRR, Goal Seek and Solver.

The difference depends on whether you mean that the investment is worth
779,454 one year after the last cash flow or immediately after the last cash
flow respectively. To use IRR() for the latter, add -779454 to the last
cash flow.

In either case, this is significantly different from the 3.1362% and 3.2072%
that say you computed.

If you want further assistance, I suggest that you answer my questions.

What are the sources of your data, and how are you importing them?

Are your data the same from both sources when you format to 4 decimal
places? If not, what are the two sets of data formatted to 4 dp?

Previously, I said I determined that rounding differences cannot account for
the difference of about 7.1 basis points. But that was with a very
simplistic change to all data. Perhaps a more varied change to the data
could cause that much of a percentage change. I don't know.

How are you setting up your data and formulas for Goal Seek? How are you
setting up Goal Seek?

For example, I put your data into A2:A6, and in B2, I put the following
formula and copied down: =B1*(1+$A$1)+A2, where A1 contains the rate of
return. In Goal Seek, I put $C$26 into "set cell", 779454 into "to value",
and $A$1 into "by changing".


----- original message -----