Goal Seek Goes Haywire

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dkline - 07 May 2008 14:12 GMT

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 to this Message

John Bundy - 07 May 2008 14:35 GMT

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

Signature

-John
Please rate when your question is answered to help us and others know what
is helpful.

> 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
[quoted text clipped - 37 lines]
> 1230301.72
> <sum of column

Reply to this Message

Dkline - 07 May 2008 14:41 GMT

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

> If 2 are fine, it would lead me to believe that something is amiss in the
> code. Can you post it please?
[quoted text clipped - 40 lines]
> > 1230301.72
> > <sum of column

Reply to this Message

John Bundy - 07 May 2008 15:08 GMT

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

Signature

-John
Please rate when your question is answered to help us and others know what
is helpful.

> Here's the code. It is perplexing to me why it works for most of the columns
> and not for a handful.
[quoted text clipped - 85 lines]
> > > 1230301.72
> > > <sum of column

Reply to this Message

Dkline - 07 May 2008 15:22 GMT

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.

> 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
[quoted text clipped - 93 lines]
> > > > 1230301.72
> > > > <sum of column

Reply to this Message