Goal Seek (Part of What-If analysis tool) is very powerful feature of excel.
If you know the result that you want from a formula but you are not sure what input is? In that case you can use the Goal Seek.
Suppose that you need to borrow some money. You know how much money you want, for how long period to pay off, and how much you can pay off each month. In that case you can use goal seek feature of excel to determine what interest rate you must take to meet your loan goal.
Applying the Goal Seek to calculate monthly payment using PMT function
In the above spreadsheet screenshot, we have uses PMT function to calculate monthly payment for loan.
D2 = Principal
D3 = Interest rate
D4 = number of payments
D6 = Monthly Payment (using PMT function)
Alternating Interest Rate using Goal Seek
• Click on Data Tab from menu bar
• In Data Tool Panel, click what If analysis
• Click Goal Seek
The following dialogue box appears:
Suppose you can make max payment of $ 400 each month. In the above dialogue box in “Set cell” select D6. In “To Value” enter 400 and in “By Changing Cell” select D3 (Interest Rate).
Now Click OK and you will get the answer. Cell D3 value will become 14.62%.