Loan Amortization with Extra Principal Payments Using Excel

In the original amortization schedule tutorial I left out a feature that is of interest to a lot of people: adding extra principal payments in order to pay off the loan earlier than the loan contract calls for. In this tutorial we will add this feature.

Before we get started let me mention one important thing: You can almost always (actually as far as I know it is always) just go ahead and add more money to the check that you send to the mortgage servicing company. They will often try to get you to sign up and pay for a program that allows you to pay extra principal, but this is not necessary. Their software will automatically apply any extra amount to the remaining principal. I have done this for years, and the mortgage statement always shows the extra principal payment even though I have done nothing more than pay extra – there is no need for a separate check or the mortgage company’s approval. In fact, I have refinanced my mortgage several times over the years and every mortgage servicer has done this. Don’t ask them, just do it and see what happens.

If you haven’t yet read the previous tutorial, I suggest that you do it now. We will use the same basic layout and numbers here. Obviously, there will need to be some changes, and we will add some new features. However, the basic idea is the same with the exception that we can no longer use Excel’s built-in IPmt and PPmt functions.

Setting Up the Worksheet

Note that we have all of the information that we need in the upper-left corner of the spreadsheet. We have a \$200,000 mortgage for 30 years with monthly payments at a 6.75% APR. In B6 I have calculated the normal mortgage payment using the PMT function:

=PMT(B\$4/B\$5, B\$3*B\$5, -B\$2)

As always, I have adjusted the interest rate and number of payments to a monthly basis. Note that I have entered the payments per year in B5. This is just in case you may want to amortize something that has other than monthly payments.

You will also notice that I have entered the extra principal that will be paid into B7. I have set it to \$300 per month, but you can change that. Note that in this tutorial I assume that you will make the same extra payment each month, and that it will start with the first payment.

Here is a screenshot that shows the beginning portion of our spreadsheet:

Calculating the Interest, Principal, and Full Payment Amount

Because we can’t use the built-in functions, we will have to do the math. Fortunately, it is pretty basic. The interest payment must always be calculated first, and it is simply the per period (here monthly) interest rate times the remaining principal:

$$\text{Monthly Interest Payment} = \text{Monthly Rate} \times \text{Remaining Principal}$$

and the principal portion of the payment is:

$$\text{Monthly Principal Payment} = \text{Full Payment Amount} \,- \text{Monthly Interest Payment}$$

For example, if we have the payment amount in B13, then we can calculate the first interest payment in cell C13 as: \$B\$4/\$B\$5*F12, and the first principal payment in D14 as: B13-C13.

It isn’t quite that simple, though. Because we are going to add extra payments, we want to be sure that we don’t overpay the mortgage.

Before we can calculate the interest and principal we must calculate the payment. It turns out that we cannot use the built-in PMT function for the last payment because it will be a different amount. So, we have to calculate that last payment based on the interest for the last month and the remaining principal. This makes our payment calculation slightly more complicated. In B13 enter the formula:

IF(\$B\$6<=F12,\$B\$6,F12+F12*\$B\$4/\$B\$5)

That simply uses the regular payment amount if the remaining balance is more than the payment. Otherwise, it adds the remaining balance and interest for the last month.
Finally, we want to wrap all of those functions in the Round function, and specify two decimal places as noted above. So, the final formulas are:

Cell	Formula
B13	=IF(\$B\$6<=F12,\$B\$6,F12+F12*\$B\$4/\$B\$5)
C13	=IF(B13>0,\$B\$4/\$B\$5*F12,0)
D13	=IF(B13>0,MIN(B13-C13,F12),0)

Note that for the principal in D13, I also added a Min function. This makes sure that you never pay more than the remaining principal amount. We now copy those formulas down to row 372, which will allow us to have up to 360 payments. You can extend it further if you need a longer amortization period.

Calculating the Remaining Balance

We can now add a column for calculating the remaining balance. In F12 enter the original balance with the formula =B2. Note that we have skipped over column E because we are going to enter the extra payment there. Now in F13, we calculate the remaining balance by subtracting the principal payment from the previous balance:

=IF(ROUND(F12, 5) > 0, F12-D13-E13, 0)

Note that I am testing to see if the previous balance is greater than zero (to five decimal places). If so, then we simply take the remaining balance and subtract the principal payment for the month and also any extra payment amount. Otherwise, we set the balance to zero. This is important so that any further calculations of the payment, principal, and interest are all zero as well (remember that we may pay off the loan early).

Adding the Extra Principal Payment

At this point, we can simply enter any extra payments in column E and they will automatically be deducted from the loan balance. In my example, I am assuming that the extra payment is a constant \$300 each month until the loan is paid off, and zero thereafter. So, the formula for E13 is:

=IF(F12-D13 >= \$B\$7, \$B\$7, F12-D13)

Recall that B7 holds the amount of the extra payment. All of the formulas in B13:F13 should be copied down the columns to row 372.

If you wanted, you could individually enter additional payments in column E so that they don’t all have to be the same. Or you could create some function to enter an extra payment. For example, maybe you only want to make extra payments in even numbered months. You could certainly do that, or any other extra payment schedule.

About that Last Payment

When you make extra principal payments, the last payment will in general be less than a full payment. It can be significantly different. In this example, the last payment is only \$399.43 versus the regular payment of \$1,297.20.

Some Extra Features

In order to make the formulas in this section a little clearer, I’m going to create a defined name (named range) that refers to the cells in column F that contain the remaining balance, but you could just use the range F12:F372. To do so, go to the Formulas tab and create a name, BalanceRange, with the following formula:

=’Sheet Name’!\$F\$12:OFFSET(’Sheet Name’!\$F\$12, ’Sheet Name’!\$B\$3*’Sheet Name’!\$B\$5,0)

In the formula, I have used a worksheet named “Sheet Name”, but in the actual workbook it is called “Amort Sched with Extra Payments.” If you create the name in your own worksheet by clicking the cells, then the correct sheet name will automatically be inserted.

Finding the Payoff Period

Because we are paying extra principal, the loan will be paid off early and we would probably like to know when. This is easily done by using the Match function to find the row in which the balance equals 0. In D2 enter the label “Payoff” and then in E2 enter the formula:

=MATCH(0, BalanceRange, 0) – 1

In this case, the loan will be paid off in period 218 (a bit over 18 years instead of 30 years with no extra payments).

An alternative method that will usually (not always) work if you make the same extra payment in every month is to use the NPer and RoundUp functions. RoundUp is required to ensure that the result is an integer value, since you must make a whole number of payments. The alternative function is:

=ROUNDUP(NPER(B4/B5, B6+B7, -B2), 0)

In this case we simply report the monthly payment as the sum of the regular payment amount and the extra payment. Again, this will not work if your extra payments are different in any period. Sometimes they are because the balance may fall low enough before the payoff so that making an extra payment would result in overpaying the loan.

Calculating the Total Interest

Calculating the total interest paid is a simple matter of summing the values in column C. However, we will make use of our defined name, and offset that range by 3 columns to the left:

=SUM(OFFSET(BalanceRange, 0, -3))

For this example, you should get \$146,991.83. Of course, you could get the same answer with =SUM(C13:C372), but that wouldn’t be as much fun.

Calculating the Total of the Regular Principal

Using the same logic as we did for calculating the total interest, we can calculate the total of the regular principal payments with:

=SUM(OFFSET(BalanceRange, 0, -2))

That will show that your regular principal payments total to \$134,900. The rest of the \$200,000 is comprised of extra principal payments.

Calculating the Total of the Extra Principal Payments

Again, using the same logic, we can calculate the total of the extra payments with:

=SUM(OFFSET(BalanceRange, 0, -1))

which will give you \$65,100. Note that we cannot simply multiply the number of payments (218) by the amount of the extra payment. This is because we do not make an extra payment in the last period. Furthermore, you must do it with a Sum function if you plan to pay a different amount in each period.

Calculating the Total Savings in Interest

Obviously, the purpose of making the extra payments is to reduce the total interest paid over the life of the loan. We can easily calculate this. Realize that the total interest paid is simply:

$$\text{Total Interest Paid} = \text{Number of Payments} \times \text{Per Period Payment} \,- \text{Principal Amount}$$

So, we can calculate the original amount of interest that would be paid if no extra payments are made, and we can calculate the interest paid with the extra payments. The difference is the amount saved. Use this formula in E8:

=(B3*B5*B6 – B2) – (E7 – E6)

For this example, the savings amounts to \$119,997.97. That is nearly twice the total of the extra payments that were made, and you end up without a mortgage about 12 years earlier. Not too bad.

The Final Amortization Schedule

The image below shows the beginning and end of the example amortization schedule.

Note that I have used the same conditional formatting as is described in the previous tutorial to hide the unused portion of the amortization schedule. I hope that you have found this tutorial to be useful. If you have any questions, please feel free to contact me.