For more Excel tips and tricks go to my static page: Excel Tutorials and Tricks Series.
The different results can then be compared to find out which one best suits your requirements.
Example Using Excel 2007's Goal Seek feature:
Note: For help with this example, see the image to the right.
- This example first uses the PMT function to calculate the monthly payments for a loan.
- It then uses Goal Seek to lower the monthly payment by altering the loan period.
Enter the following data into the cells indicated:
Cell - Data
D1 - Loan Repayment
D2 - Rate:
D3 - # of Payments:
D4 - Principal:
D5 - Payment:
E2 - 6%
E3 - 60
E4 - $225,000
- Click on cell E5 and type the following formula:
= pmt ( e2 / 12 , e3 , -e4 )
and press the ENTER key on the keyboard
- The value $4,349.88 should appear in cell E5. This is the current monthly payment for the loan.
Altering the monthly payment using Goal Seek
- Click on the Data tab.
- Choose What-If Analysis from the ribbon to open the drop down list.
- Click on Goal Seek... in the list to bring up the Goal Seek dialog box.
- In the dialog box, click on the Set cell: line.
- Click on cell E5 in the spreadsheet since we are interested in altering the monthly payments for this loan.
- In the dialog box, click on the T0 value: line.
- Type 3000 since we would like to lower the monthly payment to $3000.00.
- In the dialog box, click on the By changing cell: line.
- Click on cell E3 in the spreadsheet since we want to change the
monthly payment by altering the total number of payments to be made.
- Click OK.
- At this point, Goal Seek should begin searching for a solution. If it finds one, the Goal Seek dialog box will inform you that a solution has been found.
- In this case, the solution is to change the number of payments in cell E3 to 95.25.
- To accept this solution, click OK in the Goal Seek dialog box and Goal Seek will alter the data in cell E3.
- To find a different solution, click Cancel in the Goal Seek dialog box. Goal Seek returns the value in cell E3 to 60. You are now ready to run Goal Seek again.