In this example, we know the desired result (a $400 monthly payment) and are seeking the input value (the interest rate). Goal seek is useful if you know the needed result but need to find the input value that will give you the desired result. If you click this command, a menu with three options appears. To access these, select the Data tab and locate the What-If Analysis command. There are three What-If analysis tools that you can use.
The formula that appears in cell B5 in the example image is a function. The what-if analysis tool will allow you to easily calculate the interest rate. In the image below, you can see that if you didn’t have interest and just divided this $20,000 into 60 monthly payments, you would pay $333.33 per month. But what you need to know is what interest rate you need to qualify for to make the payment $400 per month. You know how much money you want to borrow, how long of a period of time you want to take to pay off the loan (the term), and what payment you can afford to make each month.
Download the example to work along with the video.