Part I How to Use A Loan Amortization Schedule: A Detailed Tutorial (2024)

One of the most confusing things in personal finance is calculating debt payoffs, specifically those for long-term loans. I’m convinced that the majority of people with mortgages don’t even know how their loan actually works. I certainly didn’t the first time I bought a home (I was only 21!).

Consider the following:

41.2% of Americans have a mortgage and the average balance owed is $176,222.
39.5% of Americans have auto loans and the average balance owed is $28,948.
71% of students graduating from 4-year colleges have student loan debt and the average balance after graduation is $37,172.

Can you imagine making a purchase of over $175,000 and not really understanding how much that purchase is actually costing you? Yet, that’s often the case with a mortgage. The recent student loan debt crisis also has shown that people are getting over their head with debt that they don’t understand or can’t pay.

WHAT IS A LOAN AMORTIZATION SCHEDULE?

A loan amortization schedule will show you how your monthly debt payments are broken out between the amount reducing the loan balance and the amount being paid interest. It’s vital to understand the real cost of long-term debt, but there are also some additional reasons to learn how to use this schedule:

To determine your current net worth or project your net worth into the future, as you need to calculate your long-term liabilities that offset your assets.
To project your future estimated tax liability if you take mortgage interest deductions on your tax return.
To ensure that all payments are being credited to your account properly by keeping track yourself of all extra principal payments made and how they impact the loan balance.
To determine the impact of early payments on both the overall life of the loan and total amount of interest paid.
To analyze the effect of refinancing your loan and compare loan options.

Loan amortization schedules work for mortgage loans, auto loans, student loans and any other loan with a fixed monthly payment. It is not the same calculation used for credit cards, interest-only loans or balloon loans.

Let’s go through a step-by-step example so that you can calculate and analyze your own loan. For this example, we’ll assume that you have a mortgage loan on a home you purchased several years ago.

STEP 1: GATHER INFORMATION ABOUT YOUR LOAN

To find the information you need regarding your loan, you should obtain the original terms of the loan and start from the beginning. Alternately, you could start with your current balance and remaining terms and go from this point in time on but that method will not work properly if you’ve made extra payments on your loan in the past.

I prefer to start with the original loan, in which case you’ll need to gather the following from your original loan agreement: original loan amount, the start date of the loan, interest rate, loan term (in months) and extra payments made with dates of each. For our mortgage example, we’ll assume the following original loan terms:

Loan Amount: $200,000
Start Date of Loan: 1/1/2011
Interest Rate: 5.00%
Loan Term: 30 years (360 months)

In addition, this person has recently decided to make early payments to be able to pay off their loan more quickly, which is the reason that they want to create a loan amortization schedule.

STEP 2: DOWNLOAD THE SPREADSHEET & INPUT DATA

You can download my Loan Amortization template for Excel (by clicking the download icon below the preview) or Google Docs.

Once you’ve done that, you’ll simply enter your loan terms, which you just gathered, in the box provided at the top of the Excel worksheet.

In addition, if you’ve made any extra principal payments, you should enter those as well, making sure they are allocated to the correct month in which they were paid.

After you’ve entered all of the data, you will want to check the following:

Does the monthly payment as calculated in the loan amortization schedule match the monthly payment for interest & principal (not including escrow) shown on your monthly loan statement?
Does the current balance owed on the loan per the amortization schedule match the current unpaid principal balance on your monthly loan statement?

If the monthly payment and loan balance match up, you’re ready to move on! If they don’t match, don’t get discouraged and give up.

If the monthly payment is off, you’ve likely entered incorrect information and should verify your original loan terms.

If your current loan balance differs, you should check your transaction history with your lender. Virtually all reputable lenders these days allow you to access your account online and see how they’ve allocated your debt payments each month (you’re looking for amounts allocated to the principal balance). If the balance the lender shows is lower than the balance you’ve calculated, they may have extra principal payments that you don’t currently have included. If the balance the lender shows is higher than the balance you show, there may be additional charges to your account or payments that weren’t properly applied.

Again, if you are struggling to match these up, you can request a loan amortization schedule from your lender.

In our example, here is the monthly mortgage statement:

STEP 3: ANALYZE HOW EVERYTHING IS BEING CALCULATED

Even though you make the same monthly payment each month, your loan balance isn’t decreasing by the same amount each month. In fact, it’s decreasing very slowly when you’re just beginning to pay off your mortgage since most of your initial payments are going to interest.

You’ll see that part of your monthly payment goes toward interest and part of your payment goes toward decreasing the actual loan (the principal).

The total interest you pay over the life of the loan is shown in the box at the top of the loan amortization schedule. In this example, assuming no additional extra payments are made, the total amount of interest to be paid on the 30-year loan would be $185,596. If you add this to the $200,000 balance borrowed, you would see that you actually paid $385,596 in mortgage principal and interest payments. That’s almost twice the original loan amount!

Interest is calculated on a monthly basis by taking the current loan balance and multiplying it by the annual rate divided by 12 months.

Monthly Interest = Loan Balance x Annual Interest Rate/12

Example 2/1/2011 Interest = $833.33 = $200,000 x (.05/12)

The amount of principal paid during the month is calculated using the residual payment over the amount that was applied to interest.

Monthly Principal = Monthly Payment – Monthly Interest

Example 2/1/2011 Principal = $240.31 = $1,073.64 – $833.33

Each month, the principal balance decreases, resulting in more and more of your monthly payment going toward paying off the loan balance and less to interest charges (because interest is calculated based on the current loan balance).

Loan Balance = Original Loan Balance – Monthly Principal Payments – Extra Principal Payments

Example 3/1/2011 Loan Balance = $199,759.69 = $200,000 – $240.31 – $0

Take the time to make sure you really understand how the bank is calculating your payments and how much you are really paying when you incur debt.

With a mortgage, anything that goes toward principal will increase your equity in the home, which benefits you as long as your home value is not less than the loan. Any interest payments are essentially fees paid to the bank for the privilege of borrowing their money.

3 1/2-MINUTE VIDEO TUTORIAL

Sometimes, it’s simply easier to see something done. Check out this video I’ve created to walk you through the process if you still have any questions (note: you may need to adjust the video quality by clicking the gear icon in the lower right corner).

FINAL THOUGHTS

Knowledge is power. Stop focusing on the monthly payment and learn what your debt is really costing you.

Stay tuned for Part II later this week where we’ll go through some of the many uses of loan amortization schedules in financial planning.