In this activity, you will model the impact of extra payments to principal on interest and the life of the loan.

 

Here, we model a borrower who would like to pay off his mortgage early to get out of debt. So, we will add an additional column to our amortization table for extra payments to principal.  These extra payments go straight to reducing the ending balance each month.  For this activity, we would like to know the number of payments that we save by paying extra each month.

 

Here are our assumptions for this activity:

 

Purchase Price	$470,000
20% Down	$94,000
Mortgage	$376,000
Term	30 years
APR	3.375%

 

PART A

The mortgage calls for monthly payments and monthly compounding.  The borrower plans on paying $400 extra each month to reduce principal. 

 

For PART A, we will find the number of periods that the mortgage saves.  Here are the steps for this activity:

 

STEPS:

1.         Plug in assumptions into the upload file and solve for the monthly payment.

2.         Using previous examples, build the amortization table for the 30-year mortgage.  The change here is that the ending balance will be the beginning balance less principal reduction less extra payments to principal.

3.         Examine your table. You should see the mortgage balance go to zero shortly after the 21^th year of payments.

4.         Now, verify this solution using the NPER formula. (HINT: Include the extra payment as part of your total PMT)

5.         Build a data table to show the number of payments (use NPER formula) as a function of the extra payment to principal.  (Use a range from $0 to $600).  This is a ONE-WAY DATA TABLE.

 

PART B (ADVANCED)

In Part B, we use the IF function to make clean up our spreadsheet.  Our goal here is to zero out the remaining months once we pay off the mortgage early.  This will allow us to total up interest saved with early payments.  Here are the steps for PART B:

 

STEPS:

1.         Copy your solution from part A over to another sheet.

2.         We start with the PMT column.  For the first month (cell C13), adjust the formula to the following:

 

 

This tells Excel to adjust the payment if the beginning balance falls below the monthly payment during the loan.  It will adjust the payment to the beginning balance plus monthly interest.

 

3.         Next, we adjust the interest for month 1.  We won't pay interest unless we owe money, so we replace our formula with the following IF statement:

 

 

4.         Finally, we adjust the ending balance column starting with cell G13. 

 

 

This prevents negative ending balances.

 

5.          cell (in cell F5) that sums up the interest paid over the life of the loan.

 

6.        data table (ONE-WAY) that shows the interest paid under different extra payment strategies.  What does this show?

 

 
 
 

= IF (B13 < $B$8,B13 *(1 + $B$7 12 ,$B$8)