Amortization schedule excel template with extra payments

Q: What happens when you pay extra on an amortized loan?

When you make an extra payment or a payment thats larger than the required payment, you can designate that the extra funds be applied to principal. Because interest is calculated against the principal balance, paying down the principal in less time on a fixed-rate loan reduces the interest youll pay.

Some of the questions I have had regarding mortgages and just loans in general have had to do with whether it is a good idea to make extra payments on the principal. If you have had similar questions, please feel free to download the Extra Mortgage Payments Calculator for Excel and read the rest of this page.

Why Make Extra Mortgage Payments?

There may be many reasons a person wants to make additional payments on the principal of a loan, but the most common reasons seem to be:

To payoff a home, auto, or consumer loan more quickly.
To reduce the amount of total interest paid.
To take advantage of high mortgage interest rates when other savings plans have a lower interest rate.

All of these may be good reasons to make additional payments, but make sure you understand what you are doing, first. See below for more information about each of these reasons.

Download

⤓ Excel (.xlsx)

For: Excel 2007 or later & Excel for iPad/iPhone

License: Personal Use (not for distribution or resale)

"No installation, no macros - just a simple spreadsheet" - by

Description

Calculate the difference in total interest paid on a mortgage loan when making additional monthly payments.

Since creating this spreadsheet, I've created many other calculators that let you include extra mortgage payments. The most advanced and flexible one is my Home Mortgage Calculator.

* For Excel 2003: The CUMIPMT function requires the Analysis ToolPak, which comes with Excel but is often not installed automatically. To install the add-in, open up Excel and go to the Tools menu > Add-Ins... and check the box next to "Analysis ToolPak".

Reasons to Make Extra Payments

Paying off a Mortgage Early

This approach is pretty easy to understand. If you make additional payments, you'd expect the loan to paid sooner. The spreadsheet assumes that the extra mortgage payments are made every month.

Pay Less Total Interest

Each month, your payment consists of both interest and principal. The amount of interest that is paid depends upon the amount of principal still owed (i.e. the current balance). That means that if you pay down the principal, you will end up paying less interest. This is the so-called "interest savings" that is calculated in the worksheet (i.e. the reduction in the interest expense).

Extra Payments vs. Savings

It may be weird to think of making additional payments as an "investment", but it turns out that making extra payments is nearly identical to placing the money in a savings plan. If the mortgage interest rate is the same as for the savings plan, then the amount of reduced interest expense from making extra payments is identical to the amount of interest "gained" in the savings plan (assuming both rates are fixed and compound monthly). The main difference is that with a savings plan (or other similar investment), the cash is more readily available.

The bottom line has to do with three main questions (which you will have to answer for yourself):

What approach provides a better interest rate?
Where should the investment be tied up? (Home equity or a savings/investment account?)

More Mortgage and Loan Calculators

Amortization Calculator (web-based)
Simple Interest Amortization
Auto Loan Payment Calculator
Balloon Payment Loan Calculator
Home Ownership Expense Calculator
Home Equity Loan Calculator
Adjustable Rate Mortgage Calculator
Amortization Schedule (with optional extra payments)
Amortization Formulas in Excel

Disclaimer: This spreadsheet and documentation on this page are meant for educational purposes only. We believe the calculations to be correct, but do not guarantee the results. Please consult your financial advisor or lending institution before making any final financial decisions. Some mortgage loans may have a prepayment penalty.

Believe it or not, a loan amortization spreadsheet was the very first Excel template I downloaded from the internet. Since then, I've discovered the great boost in productivity that can come from not having to start from scratch, and hopefully this page will help you get a head start. This page lists the best places to find an Excel amortization spreadsheet for creating your own amortization table or schedule.

Loan Amortization Table - Templates

If you want a spreadsheet for creating an amortization table for a loan or mortgage, try one of the calculators listed below. There are some of my most powerful and flexible templates. A feature that makes most of the Vertex42 amortization calculators more flexible and useful than most online calculators is the ability to include optional extra payments. And of course with a spreadsheet, you can save your results.

Amortization Schedule ▶

This spreadsheet lets you choose from a variety of payment frequencies, including Annual, Quarterly, Semi-annual, Bi-Monthly, Monthly, Bi-Weekly, or Weekly Payments. It only works for fixed-rate loans and mortgages, but it is very clean, professional, and accurate.

Home Mortgage Calculator ▶

Creates an amortization table for BOTH fixed-rate and adjustable rate mortgages. This one is by far the most feature-packed of all my amortization calculators. It has has been refined and improved over years of use and feedback received from both professionals and every-day home buyers.

Simple Interest Loan Amortization ▶

This may seem similar to the regular loan amortization schedule, but it is actually very different. This spreadsheet is for creating an amortization table for a so-called "simple interest loan" in which interest accrues daily instead of monthly, bi-weekly, etc.

Creating an Amortization Table

My article "Amortization Calculation" explains the basics of how loan amortization works and how an amortization table or "schedule" is created. You can delve deep into the formulas used in my Loan Amortization Schedule template listed above, but you may get lost, because that template has a lot of features and the formulas can be complicated.

To get started, I would recommend downloading the Simple Amortization Chart template.

If you are wanting to create your own amortization table, or even if you just want to understand how amortization works, I'd recommend you also read about Negative Amortization. In that article, I explain what happens when a payment is missed or the payment is not enough to cover the interest due.

More Free Loan Amortization Spreadsheets

Listed below are other spreadsheets by Vertex42.com that use an amortization table to both display results and perform calculations.

Balloon Payment Loan Calculator ▶

This template is unique in that the amortization table ends after a specified number of payments. The final payment, or balloon payment, is the amount required to pay off in full.

Line of Credit ▶

For a revolving line of credit (such as a credit card or HELOC), interest normally accrues daily, so this spreadsheet is like the "simple interest loan" calculator except that it allows you to include additional draws besides the initial loan amount.

Extra Payment Mortgage Calculator - Compares making extra payments to investing.
Home Equity Loan Amortization Calculator
Auto Loan Amortization Calculator
Amortization Calculator (web-based)

You can also find a free excel loan amortization spreadsheet by doing a search in Excel after going to File > New. Some of them use creative Excel formulas for making the amortization table and a couple allow you to manipulate the schedule by including extra payments. The new online Microsoft template gallery doesn't have as many loan-related templates as the old gallery, but you can still find a few in the Financial Management category.

Does paying extra on mortgage change amortization?

You do have the option to pay extra toward your mortgage, which will alter your amortization schedule. Paying extra can be a good way to save money in the long run, because the money will go toward your principal, not the interest.

What happens when you pay extra on an amortized loan?

When you make an extra payment or a payment that's larger than the required payment, you can designate that the extra funds be applied to principal. Because interest is calculated against the principal balance, paying down the principal in less time on a fixed-rate loan reduces the interest you'll pay.

How do I create a loan amortization schedule with extra payments in Excel?

How to make a loan amortization schedule with extra payments in Excel.

Define input cells. As usual, begin with setting up the input cells. ... .

Calculate a scheduled payment. ... .

Set up the amortization table. ... .

Build formulas for amortization schedule with extra payments. ... .

Hide extra periods. ... .

Make a loan summary..

How do you add extra payments to amortization schedule?

Amortization Calculator If you would like to make one additional payment each year, multiply the amount by the duration of the loan. For example, for an additional $600 a year on a 30-year loan, enter $18,000.