Goal Seek Goes Haywire

[Dkline]

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

 

[John Bundy]

If 2 are fine, it would lead me to believe that something is amiss in the
code. Can you post it please?

 

[Dkline]

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]

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.GoalSeek Goal:=valSeek, ChangingCell:=Range("AD3")

jmbundy@gmail(nospam).com

 

[Dkline]

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.