Need to Give Goal Seek a Margin of Error in VBA

[avd88]

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:

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

End Sub

Thanks!!

 

[MrSyyr]

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:

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]

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!