Enter the Tutorial Data
This tutorial uses the PMT function to calculate the monthly payments for a loan. After the loan payments are calculated, Goal Seek is used to lower the monthly payment by altering the loan period. To follow along with the tutorial, enter the following data into the cells indicated:
Cell D1: Loan RepaymentCell D2: RateCell D3: # of PaymentsCell D4: PrincipalCell D5: PaymentCell E2: 6%Cell E3: 60Cell E4: 225,000Cell E5: Leave this cell blank.
Here’s what the tutorial data looks like in the worksheet: Follow the steps below to implement the PMT function within your worksheet:
Alter the Monthly Payment Using Goal Seek
After you enter the PMT formula, use Goal Seek to peek into different data options. In this tutorial, you’ll see how Goal Seek is used to show the change in the total number of payments to be made when the monthly payment is lowered. We’ll reduce the monthly payment to $3000.00 to see the difference in the number of payments.
