Not sure if a loan is right for you? Did you know Excel has a simple way to help you calculate what kind of loan you should get that will be appropriate for your financial situation?
What is Goal Seek?
Goal Seek is a what-if analysis tool in Excel that allows you to figure out what kind of inputs you need to obtain a desired outcome. For example, suppose you have a loan that has a monthly payment that is a little too high for you. Goal Seek lets you change a parameter of the loan (such as how much time you have to repay the loan) to get the monthly amount that you can afford.
It’s really easy to use Goal Seek. Just follow the following example.
Example: How can we lower our monthly payment?
It’s time to get your feet wet. Open up a new spreadsheet in Excel.
To get started, let’s fill in some headings to help keep track of what our data means.
A1 – Loan Analysis:
A2 – Annual Interest Rate:
A3 – # of Monthly Payments:
A4 – How Much You Owe:
A5 – Monthly Payment:
Let’s assume that we’re considering a $200,000 loan with a 5% annual rate that is to be repaid over 5 years (60 months). Now we’re going to input the data associated with this loan.
B2 – 5%
B3 – 60
B4 – $200,000
B5 – =PMT( B2 / 12, B3, -B4 )
If you’re not familiar with the PMT function, check out the previous hub about it.
If you’ve done everything correctly so far, your spreadsheet should look like this right now.
For the loan we’re considering, we would have to make a monthly payment of $3,774.25. Maybe that’s a little out of our budget. But we really need that loan and we’re able to pay a little less each month. So, let’s assume we can afford to pay $3,500 each month.
Now we’re going to use Goal Seek to help determine how many months we should extend our loan to obtain the desired monthly payment.
Excel 2003 and beforeGo to the Tools menu.
Select Goal Seek….
Excel 2007 and afterGo to the Data tab.
Under Data Tools, select What-If Analysis.
Select Goal Seek….
The Goal Seek dialog box should pop up now and it should look like this.
Enter these values for the parameters:
Set cell: B6
To value: 3500
By changing cell: B3
The Set cell parameter is the cell that contains our goal. In our case, it’s the monthly payment (B5).
The To value parameter is what we would like our goal to be. In our situation, we want the monthly payment to be 3500.
The By changing cell parameter is what we’re going to be changing to obtain our goal. We’re going to be changing the number of monthly payments (B3) we have to make.
The Goal Seek box should look like this now.
Click OK and Goal Seek will determine how many monthly payments you will have to make to have your desired monthly payment of $3,500.
A Goal Seek Status dialog box will pop up informing you that a solution was found. If you click OK, the loan data will be changed to what you were looking for. If you click Cancel, the loan data will change back to what it was before we used Goal Seek.
Click OK to keep the new loan data.
You’ll notice that the monthly payment is now what we wanted ($3,500) and the number of monthly payments has been changed to 65.399964. This means we’re going to need a loan with at least 66 monthly payments in order for us to pay $3,500 each month. With this in mind, we can try to negotiate for 6 more payments to obtain a loan we can afford.
You’ve just learned how to use Goal Seek to change a parameter of a loan to obtain a loan that is favorable to your financial situation. Now have fun with this and play around with it to figure out the loan that is right for you.