Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you create a formula in Excel, it's easy to ask questions such as, how much is 15% of 200. It's much more difficult to ask a question such as, how much can my business borrow and have a monthly payment of $125,000 or less. Rather than create a complex formula, you can use the Goal Seek feature to find answers to that type of question quickly. In this worksheet, I have information about a loan, the term, interest rate, and monthly payments, and also, I have my target payment of $125,000.
So as these things stand, my loan amount, term of paying off over 10 years, and interest rate of 5.75% will result in a monthly payment of over $164,000. I want to get that payment to $225,000 or less, and the only way that I can do that, at least for the purposes of this exercise, is to change my loan amount. If I want to find out how much you can borrow on these terms and end up with the target payment of $125,000, I can use what's called Goal Seek. To work with Goal Seek, I can go to the data tab of the Ribbon, click the What-If button, and then click Goal Seek.
The Goal Seek dialog box has three controls. The first is Set cell, and that cell is the cell that contains my target value. In other words, my payment, I want to be $125,000, so that is the cell I am going to change. This cell here, B9 is only for illustrative purposes. It's a flat value. I can't add anything to it, so instead, I need to use this cell here, which has a formula that calculates a monthly payment based on loan amount, term, and interest rate. So the cell that I want to set is cell B7.
I want to sell it to my target value of $125,000, and I want to do that by changing the loan amount, which is in cell B3. Everything looks good. When I press Return or click OK, Goal Seek returns a solution, and it also displays the solution in the dialog box. If I want to have a target payment of $125,000, I can borrow $11,387,000, and a little extra. If you want to keep that value in the worksheet, you can click OK; or if you want to go back to your previous values, you can click Cancel.
Goal Seek analysis let's you find answers to questions that are easy to solve using a formula. Whenever you want a formula in one cell to produce a target value arrived at by changing the value of another cell, try using Goal Seek to discover the answer.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 87416 Viewers
80 Video lessons · 136407 Viewers
59 Video lessons · 55057 Viewers
52 Video lessons · 68924 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.