Can you automate goal seek in excel

[robwill100]

Hi ecveryone, is it possible to automate goal seek?

[#2]

Write formula as an alternative.

[#3]

@robwill100 

The Range object in Excel VBA has a hidden method GoalSeek. See GoalSeek Method [Excel 2003 VBA Language Reference] 

It still works in Excel in Microsoft 365.

[#4]

[.... deleted ....]

[#5]

@Joe User 

Thanks Joe, I have corrected the link.

[#6]

@Harun24HR

Hi Harun, thank you for your reply. I have explained below what I am trying to achieve and I would appreciate your assistance in being able to write the required formula. I have attached the spreadsheet I am working on.

The overall objective is to calculate the brokerage percentage in cell H5.

To achieve this I am currently using the goal seek function.

Prior to using the goal seek function I first have to under the heading Customer:

1. Input into cell D3 the amount of the loan.
2. Input the term of the loan in years in cell D5
3. Input the repayment amount per month into cell D7.

Once I have done that the annual interest rate for loan is shown in cell D4 where I have used the RATE formula.

Once I have completed that I then need to under the heading financier:

1. Input the new interest in cell H11.
2. At this point I use the goal seek function whereby I choose cell H12 followed by inputting into the “to Value” section the monthly pmt from cell D7. ($890 in this example) I then in the  “by changing cell” choose cell H5. (brokerage percentage). The result of the goal seek then calculates the brokerage percentage.

If its possible to do this via a formula rather than using goal seek I would really appreciate your help.

Cheers

Rob

[#7]

@robwill100 

This is a possible formula:

=(PV(H11/12, H10, D8, 0, 1)-H7-Rental_Loan_Amount)/Rental_Loan_Amount

or

=(PV(H11/12, H10,D8,0, 1)-H7)/Rental_Loan_Amount-1

[#8]

@Hans Vogelaar 

Hi Hans, thanks for your help, the formulas both worked perfectly.

May I trouble you with an additional request for assistance.

The formula provided solved the what the brokerage percentage in cell H5, I would like to add another objective.

In cells K5 to L7 I have listed some finance amount brackets. In column M are the corresponding maximum brokerages I am able to charge.

For the example in the worksheet the brokerage percentage has been calculated at 4.62% which given the amount financed is in excess of $50,000.00, the maximum I am allowed to charge of 4% is exceeded.

What I would like to achieve is that if the max brokerage is exceeded then any amount over the max brokerage is displayed as a percentage in cell H7. Whatever the % is in H7 would then be multiplied by the amount financed in H3 to workout what the origination value is in cell H8.

In this example is would be max 4% of the amount financed being $2,117.75 and in cell H7 the percentage shown would be 0.62% * the amount financed resulting in a figure of $329.61. When added together it equals it ends up with the sames result as if I had just worked with the percentage at 4.62%

The same approach would apply for amounts financed between 0-$20K and $20,000.01 & $50K with the associated maximum brokerage amounts.

Any assistance would be greatly appreciated.

Cheers

[#9]

@robwill100 

Please check your workbook. Your formulas add H7 to an amount and subtract H7 from an amount, but H7 is supposed to be a percentage. I suspect that is not what you intended.

[#10]

@Hans Vogelaar 

Thanks Hans,

Maybe I should have given a little bit more background.

We have a maximum amount of brokerage we can charge depending on the amount being financed. If the brokerage amount exceeds the maximum we have to charge the extra as an origination fee.

Therefore what I am trying to achieve in this example is to workout what the total brokerage is, if it exceeds 4% I want the brokerage amount in cell H6 to show what 4% equals, (in this case $2,117.75) and have the balance displayed as a percentage in cell H7 and the amount displayed in dollars in cell H8.

The same idea would apply for the brackets of amount financed I have listed in the workbook.

I hope this helps

[#11]

@robwill100 

Here you go. I have corrected the formulas.

[#12]

Thanks Hans, workes perfectly.

Cheers