Loan Payment / Overpayment Calculator

I like using free online mortgage calculators to get a quick idea of how much a new home will cost, or generally how much I can shorten the life of my existing mortgage by making overpayments.   I recently put this same functionality into an Excel spreadsheet, which offers several advantages over the online calculators:

1. Less Work: You never have to re-enter any information.
2. Privacy / Security: It’s all saved on your PC.
3. No internet connection required: Excel runs on your PC.
4. Convenience: No more google searches for: “mortgage calculator”.
5. Flexibility: Use this spreadsheet as a starting point, then customize or extend it yourself.
6. Accurate Overpayment Calculation: Enter specific overpayment amounts and dates.  Some online calculators allow you to enter one or more “one time” payments.   Others allow you to enter an overpayment amount, but they assume that this amount will be applied starting with the first payment.  Many homeowners will decide to start making overpayments after living in their home for several years.  This spreadsheet allows you to enter any number of “one time” payments, but there is also a cell for “Estimated Future Overpayments”.  This amount will be applied starting with the first payment after the current date, until the loan is paid off.  With the spreadsheet, you will see a more accurate amortization based on a combination of past and expected future overpayments.

 To download the mortgage calculator, click here, then keep reading for some basic instructions:

Step 1: Enter loan information.  Enter the Principal amount, Number of Payments (360 for a 30 year loan), etc.  Do not modify any field with a gray background (these are automatically calculated for you).  Note the “Estimated Future Overpayment” cell I mentioned earlier.  This amount will be added to any future payment amount.

 

                                                                               figure 1

Step 2: Go to the “Overpayments” worksheet and enter previous overpayment dates and amounts.  The spreadsheet will automatically figure out which payment date to apply these overpayments to.  Because most mortgage loans are simple interest, it’s very important that overpayments are accounted for in the amortization on the specific date of the overpayment.  The principal is reduced further by the overpayment amount, which reduces the amount of interest you owe in the next payment. 

                                           figure 2

Step 3:  That’s it.  Enjoy!   Your amortization should look something like the below image.  Note the gray background, which means these values were calculated for you.  Note that the last payment date is also calculated for you (see figure 1), and takes into account any overpayments (actual and estimated).

                                                                                                                                                                            figure 3