Need to Give Goal Seek a Margin of Error in VBA

avd88

Board Regular
Joined
Jan 18, 2016
Messages
67
Hello,

Hoping someone can help me give goalseek a margin of error in VBA.

I created a spreadsheet that calculates how much you would pay on a loan if you are also getting a return (rent) on your investment.
My workbook has a couple of spreadsheets that use an amortization table, currency conversions (investment in another country), etc.

I added a vba button with goalseek that would tell me what is the maximum downpayment I can put in after all other variables are set (years, interest rate, rent, etc.) so that my loan payments are equal to my rent.
Because there are so many calculations going on the function sometimes works but most of the times it goes haywire. Because I don't need a very precise level of accuracy I think I can fix the issue by giving the goalseek a margin of error of +-1 or even +-5.

Cell O1 is equal to Rent - Loan Payment.
Goal is O1 = 0.
By changing Cell E2 which is equal to Downpayment.

This is my code:

VBA Code:
Private Sub GoalSeek_Click()
Range("O1").GoalSeek _
    Goal:=0, _
    ChangingCell:=Range("E2")

End Sub

Thanks!!
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

MrSyyr

New Member
Joined
Apr 29, 2020
Messages
31
Office Version
  1. 2016
Platform
  1. Windows
Hello,

Hoping someone can help me give goalseek a margin of error in VBA.

I created a spreadsheet that calculates how much you would pay on a loan if you are also getting a return (rent) on your investment.
My workbook has a couple of spreadsheets that use an amortization table, currency conversions (investment in another country), etc.

I added a vba button with goalseek that would tell me what is the maximum downpayment I can put in after all other variables are set (years, interest rate, rent, etc.) so that my loan payments are equal to my rent.
Because there are so many calculations going on the function sometimes works but most of the times it goes haywire. Because I don't need a very precise level of accuracy I think I can fix the issue by giving the goalseek a margin of error of +-1 or even +-5.

Cell O1 is equal to Rent - Loan Payment.
Goal is O1 = 0.
By changing Cell E2 which is equal to Downpayment.

This is my code:

VBA Code:
Private Sub GoalSeek_Click()
Range("O1").GoalSeek _
    Goal:=0, _
    ChangingCell:=Range("E2")

End Sub

Thanks!!
You could try making say P1 the margin of error (=5) then make Q1 = Rounddown(O1/P1,0) which will be 0 within the error margin and make the goal seek a value of 0 for Q1.
 

avd88

Board Regular
Joined
Jan 18, 2016
Messages
67
Hy MrSyyr, that little hack worked! Thanks, I had tried something outside of VBA yesterday but couldn't get it to work well. Thanks again, great input!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,306
Messages
5,571,460
Members
412,394
Latest member
divster27
Top