| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Goal Seek Goes Haywire
I am using Goak Seek in a macro to calculate the interest rate needed on a
cash flow to hit a known amount. Below shows the problem I'm having in some of the 60 columns I'm using. Evetually this will be 121 columns. In each year I need to find the ROR such that the sum of each year's Cash Flow at the ROR to the power of the number of years remaining equals the Target Amount. In the first row is the "To Value" AKA the "Set Cell". The second row is the "guess" for the rate. Goal Seek will be using that as the "By Changing Cell". The last number at the bottom of the colum is the sum of the Cash Flow at Interest. Each cash flow in a column is being calculated as: Cash Flow * ((1 + Current Year) ^ Remaining Years In the three examples below, the first and third are fine. The question is why does the middle one go haywire? Is there a better method than Goal Seek for this? Year 17 `````````Year 18 Year 19 1,171,483.02 1,198,769.86 1,230,301.72 <Target Amount 3.0962873% -222.3366976% 4.9282072% <Goal Seek's Answer 17347.29485 389154.3337 25766.36287 16826.30413 -318101.0616 24556.18329 16320.96031 260020.965 23402.84271 15830.79346 -212545.3524 22303.67157 15355.34778 173738.017 21256.12566 14894.18114 -142016.2718 20257.78028 14446.8647 116086.403 19306.3246 14012.9825 -94890.9079 18399.55635 13592.13107 77565.36655 17535.37666 13183.91906 -63403.18815 16711.78526 5286.023088 21423.07939 6583.519821 4478.854835 -15296.99417 5480.836563 3668.8139 10559.68761 4411.195657 2858.200063 -6932.729974 3376.556616 2005.303756 4099.001722 2327.621458 1158.544046 -1995.706888 1321.281967 216.5022034 314.2916191 242.6029359 1171483.021 990.9272504 -891.8042216 1198769.86 -2046.10004 1230301.72 <sum of column |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Goal Seek Goes Haywire
If 2 are fine, it would lead me to believe that something is amiss in the
code. Can you post it please? -- -John Please rate when your question is answered to help us and others know what is helpful. "Dkline" wrote: I am using Goak Seek in a macro to calculate the interest rate needed on a cash flow to hit a known amount. Below shows the problem I'm having in some of the 60 columns I'm using. Evetually this will be 121 columns. In each year I need to find the ROR such that the sum of each year's Cash Flow at the ROR to the power of the number of years remaining equals the Target Amount. In the first row is the "To Value" AKA the "Set Cell". The second row is the "guess" for the rate. Goal Seek will be using that as the "By Changing Cell". The last number at the bottom of the colum is the sum of the Cash Flow at Interest. Each cash flow in a column is being calculated as: Cash Flow * ((1 + Current Year) ^ Remaining Years In the three examples below, the first and third are fine. The question is why does the middle one go haywire? Is there a better method than Goal Seek for this? Year 17 `````````Year 18 Year 19 1,171,483.02 1,198,769.86 1,230,301.72 <Target Amount 3.0962873% -222.3366976% 4.9282072% <Goal Seek's Answer 17347.29485 389154.3337 25766.36287 16826.30413 -318101.0616 24556.18329 16320.96031 260020.965 23402.84271 15830.79346 -212545.3524 22303.67157 15355.34778 173738.017 21256.12566 14894.18114 -142016.2718 20257.78028 14446.8647 116086.403 19306.3246 14012.9825 -94890.9079 18399.55635 13592.13107 77565.36655 17535.37666 13183.91906 -63403.18815 16711.78526 5286.023088 21423.07939 6583.519821 4478.854835 -15296.99417 5480.836563 3668.8139 10559.68761 4411.195657 2858.200063 -6932.729974 3376.556616 2005.303756 4099.001722 2327.621458 1158.544046 -1995.706888 1321.281967 216.5022034 314.2916191 242.6029359 1171483.021 990.9272504 -891.8042216 1198769.86 -2046.10004 1230301.72 <sum of column |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Goal Seek Goes Haywire
Here's the code. It is perplexing to me why it works for most of the columns
and not for a handful. Option Explicit Dim wb As Workbook Dim wsGUI As Worksheet Dim wsCalcs As Worksheet Dim rngGSTargetValue As Range Dim valSeek As Variant Dim intTargetCellRow As Integer Dim intTargetCellCol As Integer Sub SideFundSolve() ' ' Side Fund Rate Solve ' Donald R. Kline, CLU, ChFC ' ' Set wb = ThisWorkbook Set wsGUI = wb.Worksheets("GUI") Set wsCalcs = wb.Worksheets("Calcs") wsCalcs.Activate Range("A1").Select intTargetCellRow = Range("GSTargetRow") intTargetCellCol = Range("GSTargetCol") ActiveCell.Offset(intTargetCellRow - 1, intTargetCellCol - 1).Select Set rngGSTargetValue = Range("GSTargetValue") valSeek = rngGSTargetValue.Value 'To Value ActiveCell.GoalSeek Goal:=valSeek, ChangingCell:=Range("AD3") wsGUI.Activate Set rngGSTargetValue = Nothing Set wsCalcs = Nothing Set wsGUI = Nothing Set wb = Nothing Range("A2").Activate End Sub "John Bundy" wrote: If 2 are fine, it would lead me to believe that something is amiss in the code. Can you post it please? -- -John Please rate when your question is answered to help us and others know what is helpful. "Dkline" wrote: I am using Goak Seek in a macro to calculate the interest rate needed on a cash flow to hit a known amount. Below shows the problem I'm having in some of the 60 columns I'm using. Evetually this will be 121 columns. In each year I need to find the ROR such that the sum of each year's Cash Flow at the ROR to the power of the number of years remaining equals the Target Amount. In the first row is the "To Value" AKA the "Set Cell". The second row is the "guess" for the rate. Goal Seek will be using that as the "By Changing Cell". The last number at the bottom of the colum is the sum of the Cash Flow at Interest. Each cash flow in a column is being calculated as: Cash Flow * ((1 + Current Year) ^ Remaining Years In the three examples below, the first and third are fine. The question is why does the middle one go haywire? Is there a better method than Goal Seek for this? Year 17 `````````Year 18 Year 19 1,171,483.02 1,198,769.86 1,230,301.72 <Target Amount 3.0962873% -222.3366976% 4.9282072% <Goal Seek's Answer 17347.29485 389154.3337 25766.36287 16826.30413 -318101.0616 24556.18329 16320.96031 260020.965 23402.84271 15830.79346 -212545.3524 22303.67157 15355.34778 173738.017 21256.12566 14894.18114 -142016.2718 20257.78028 14446.8647 116086.403 19306.3246 14012.9825 -94890.9079 18399.55635 13592.13107 77565.36655 17535.37666 13183.91906 -63403.18815 16711.78526 5286.023088 21423.07939 6583.519821 4478.854835 -15296.99417 5480.836563 3668.8139 10559.68761 4411.195657 2858.200063 -6932.729974 3376.556616 2005.303756 4099.001722 2327.621458 1158.544046 -1995.706888 1321.281967 216.5022034 314.2916191 242.6029359 1171483.021 990.9272504 -891.8042216 1198769.86 -2046.10004 1230301.72 <sum of column |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Goal Seek Goes Haywire
I have come up with a solution to my problem. I use the current year's ROR
solution as the guess for the next year. By giving it the starting ROR from the prior year, it has a better starting point. I'm good to go now. Thanks for taking a look at my problem. "John Bundy" wrote: Without seeing the data and what your ranges are set to I can't see it. You are welcome to email it to me to have a look. Other than that I would step through the code and check what each value is at each point in the calculation. Also, I'm confused as to why you are always changing cell AD3? ActiveCell.Goalom the priorSeek Goal:=valSeek, ChangingCell:=Range("AD3") -- -John Please rate when your question is answered to help us and others know what is helpful. "Dkline" wrote: Here's the code. It is perplexing to me why it works for most of the columns and not for a handful. Option Explicit Dim wb As Workbook Dim wsGUI As Worksheet Dim wsCalcs As Worksheet Dim rngGSTargetValue As Range Dim valSeek As Variant Dim intTargetCellRow As Integer Dim intTargetCellCol As Integer Sub SideFundSolve() ' ' Side Fund Rate Solve ' Donald R. Kline, CLU, ChFC ' ' Set wb = ThisWorkbook Set wsGUI = wb.Worksheets("GUI") Set wsCalcs = wb.Worksheets("Calcs") wsCalcs.Activate Range("A1").Select intTargetCellRow = Range("GSTargetRow") intTargetCellCol = Range("GSTargetCol") ActiveCell.Offset(intTargetCellRow - 1, intTargetCellCol - 1).Select Set rngGSTargetValue = Range("GSTargetValue") valSeek = rngGSTargetValue.Value 'To Value ActiveCell.GoalSeek Goal:=valSeek, ChangingCell:=Range("AD3") wsGUI.Activate Set rngGSTargetValue = Nothing Set wsCalcs = Nothing Set wsGUI = Nothing Set wb = Nothing Range("A2").Activate End Sub "John Bundy" wrote: If 2 are fine, it would lead me to believe that something is amiss in the code. Can you post it please? -- -John Please rate when your question is answered to help us and others know what is helpful. "Dkline" wrote: I am using Goak Seek in a macro to calculate the interest rate needed on a cash flow to hit a known amount. Below shows the problem I'm having in some of the 60 columns I'm using. Evetually this will be 121 columns. In each year I need to find the ROR such that the sum of each year's Cash Flow at the ROR to the power of the number of years remaining equals the Target Amount. In the first row is the "To Value" AKA the "Set Cell". The second row is the "guess" for the rate. Goal Seek will be using that as the "By Changing Cell". The last number at the bottom of the colum is the sum of the Cash Flow at Interest. Each cash flow in a column is being calculated as: Cash Flow * ((1 + Current Year) ^ Remaining Years In the three examples below, the first and third are fine. The question is why does the middle one go haywire? Is there a better method than Goal Seek for this? Year 17 `````````Year 18 Year 19 1,171,483.02 1,198,769.86 1,230,301.72 <Target Amount 3.0962873% -222.3366976% 4.9282072% <Goal Seek's Answer 17347.29485 389154.3337 25766.36287 16826.30413 -318101.0616 24556.18329 16320.96031 260020.965 23402.84271 15830.79346 -212545.3524 22303.67157 15355.34778 173738.017 21256.12566 14894.18114 -142016.2718 20257.78028 14446.8647 116086.403 19306.3246 14012.9825 -94890.9079 18399.55635 13592.13107 77565.36655 17535.37666 13183.91906 -63403.18815 16711.78526 5286.023088 21423.07939 6583.519821 4478.854835 -15296.99417 5480.836563 3668.8139 10559.68761 4411.195657 2858.200063 -6932.729974 3376.556616 2005.303756 4099.001722 2327.621458 1158.544046 -1995.706888 1321.281967 216.5022034 314.2916191 242.6029359 1171483.021 990.9272504 -891.8042216 1198769.86 -2046.10004 1230301.72 <sum of column |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Goal Seek | Excel Discussion (Misc queries) | |||
| Goal Seek | Excel Discussion (Misc queries) | |||
| Goal Seek | Excel Worksheet Functions | |||
| Goal Seek | Excel Discussion (Misc queries) | |||
| Goal Seek | Excel Worksheet Functions | |||
Linear Mode
