If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Goal Seek returning different results
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 |
#2
|
|||
|
|||
Goal Seek returning different results
"Don Kline" wrote:
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" wrote in message ... 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 |
#3
|
|||
|
|||
Goal Seek returning different results
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" wrote: "Don Kline" wrote: 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" wrote in message ... 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 |
#4
|
|||
|
|||
Goal Seek returning different results
"Don Kline" wrote:
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 ----- "Don Kline" wrote in message ... 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" wrote: "Don Kline" wrote: 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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|