Microsoft Excel: Back Into An Answer Using Goal Seek

Problem: You’ve determined that you want to obtain a 48-month loan for a car. The interest rate is 5 percent. You want to find out what loan amount would result in a $490 monthly payment. As shown in Fig. 604, the sticker price of the car results in a $575 payment.

See all Microsoft Excel tips

Strategy: Although there is a financial function to determine the Present Value (PV), it is easier to use the Goal Seek command on the Tools menu.

1) From the Tools menu, select Goal Seek. This will bring up the dialog shown in Fig. 605.

2) In Fig. 604, you want to set cell B6 to $490 by changing cell B2. Enter these values and choose OK, as shown in Fig. 606.

In a simple case like this one, Goal Seek will almost always succeed. It will report back that it found a solution, as shown in Fig. 607.

3) To accept the solution, choose OK. To revert to the original value, select Cancel.

Result: You can afford to borrow $21,277.25.

Summary: The Goal Seek command on the tools menu is great for solving certain equations backwards.