Goal Seek - Multiple Results at Once

[wardy0601]

Hi All,

Just wanted to know whether there is a way to do a goal seek to find some interest rate results (% of rates to be set at various intervals) that have to be input manually as the sheet won't allow a non circular calculation to be automated - over say 20 cells (outputs) at once as opposed to having to do them all manually to find each output separately?

Thanks in advance.

 

[Eric W]

Sure. Two ideas: if the formula can be inverted algebraically, then you can enter the formula in a cell and drag down. Second, if the formula can't be inverted easily (and I know may interest formulas fit into that category), then you can write a simple VBA script to loop through your list and output all the answers in a column.

For example:

	A	B	C	D	E
1	5			100	6.6E-07
2	100	200		110	0.019245
3	0.148698			120	0.037136
4				130	0.053873
5				140	0.06961
6				150	0.084472
7				160	0.09856
8				170	0.111961
9				180	0.124746
10				190	0.136974
11				200	0.148698

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =A2*(1+A3)^A1 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

B2 contains a simple formula to calculate FV given number of years in A1, PV in A2, and interest in A3. Now to figure out what interest rate is needed to achieve a FV of 100-200 in increments of 10, you could the the Goal Seek 11 times. Or this macro does it automatically:

Sub Macro1()
Dim r as long, x as long
'
    r = 1
    For x = 100 To 200 Step 10
        Range("B2").GoalSeek Goal:=x, ChangingCell:=Range("A3")
        Cells(r, "D") = x
        Cells(r, "E") = Range("A3")
        r = r + 1
    Next x
End Sub

This puts the desired FVs and corresponding interest rates in D:E as shown. Let me know if you need help adapting this to your requirements.

 

[wardy0601]

Thanks Eric,

Played around with it and it worked well in VBA!

What program do you use to extract screen/formula shots in Excel 2016 as you have done in your post above?

Appreciate your help.

 

[Eric W]

Glad you got it working!

I use the HTML Maker tool, click the link in my signature to see how to use it.