Forum Discussion
Can you automate goal seek in excel
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:
- Input into cell D3 the amount of the loan.
- Input the term of the loan in years in cell D5
- 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:
- Input the new interest in cell H11.
- 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
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
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
