• HOME
  • SHOP
  • ABOUT
  • ARCHIVES
  • SUBSCRIBE
  • CONTACT

Making Your Money Matter

Create a life you love, one dollar at a time

  • Changing Your Money Mindset
    • The Importance of Money
    • Adjusting Your View Of Money
    • Minimalism & Frugality
  • Creating a Financial Plan
    • Financial Plan Classes
    • Financial Plan Topics
  • Organizing Your Finances
    • Financial Tools & Resources
    • Managing Finances as a Family
    • Organizing Financial Paperwork
    • Paperless Financial Organization
    • Setting up Financial Tasks & Systems

Part I How To Use a Loan Amortization Schedule: A Detailed Tutorial

April 11, 2017

Do you REALLY know how much you're paying for that mortgage, student loan or auto loan? An amortization schedule will show you the total interest you'll pay over the life of the loan, as well as the impact of early payments. This is ESSENTIAL to know for everyone with a loan!

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.

  • Extra Payments Made to Date: $100 per month in January through April 2017

If you’re having a really difficult time finding this information, you can contact your loan company to send you an amortization schedule to start with. You’ll still want to use the spreadsheet, though to analyze the impact of future extra payments.

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:

Sample mortgage statement: You need to match up the unpaid principal balance and the total principal and interest to your loan amortization schedule.

The monthly principal and interest payments due add up to $1,073.64, which matches the loan amortization schedule.How to match up the loan balance and monthly payment in an amortization schedule.

The current unpaid principal balance matches the loan amortization schedule balance of $178,491.51 as well, so we’re ready to move onto the next step.

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.

Related Posts

  • Part II: Ways To Use Loan Amortization Schedules in Your Financial PlanPart II: Ways To Use Loan Amortization Schedules in Your Financial Plan
  • DM102: Debt ReductionDM102: Debt Reduction
  • The Basics of Credit Reports & ScoresThe Basics of Credit Reports & Scores
  • DM101: Debt Overview & Credit CardsDM101: Debt Overview & Credit Cards

8 Comments Filed Under: Creating a Financial Plan, Debt Management

Comments

  1. Daniel Palmer says

    April 11, 2017 at 7:13 AM

    Just curious- how common do you think bank errors are on applying extra payments? Lately we’ve definitely seen that banks are far from perfect, but bookkeeping errors- especially on payments made electronically?

    Having said that, understanding how your payment schedule and interest works is definitely a good idea.

    Reply
    • Kathryn says

      April 11, 2017 at 11:17 AM

      I would hope it’s not too common, I know from my experience that I clearly marked my extra payments as principal payments and never had issues, but I’ve heard that there can potentially be issues if you make payments that aren’t clearly marked as going toward principal (such as if you just make an extra payment, they may apply it to the next regular mortgage payment instead of extra principal).

      Reply
  2. Erik @ The Mastermind Within says

    April 11, 2017 at 10:12 AM

    Hey Kathryn,

    I just released a free debt destruction tool on my site http://www.themastermindwithin.com/debt-destruction-tool/

    It takes in your debts and calculates your savings using the snowball and avalanche method.

    Would love it if you took a look and shared with your friends/family. As a I mentioned before, it’s free.

    Erik

    Reply
  3. Amy @ Life Zemplified says

    April 11, 2017 at 11:28 AM

    This is so great Kathryn. I began using amortization schedules 4-5 years ago to eliminate debt. Playing around with them also convinced me to go to a 15-year mortgage from a 30-year. Now that we just have the mortgage left I’m often checking the schedule to see how extra payments will affect it. Good advice!

    Reply
    • Kathryn says

      April 12, 2017 at 8:02 PM

      I’m glad I’m not the only one obsessed with checking my spreadsheets, lol. Whenever I have extra money in my budget I go to my loan amortization schedule and look how using that extra money will impact my mortgage. It’s so exciting to me!

      Reply
  4. Syed says

    April 21, 2017 at 11:00 AM

    Very informative post thanks Kathryn. I agree that it’s important to make sure extra payments are being applied to the principal only. I had this problem years ago with extra student loan payments I was making and I had to call them to correct it. Banks look out for themselves first!

    Reply
    • Kathryn says

      April 21, 2017 at 1:26 PM

      It’s true – we all have to look out for ourselves first. I’m glad that you were able to catch and fix the problem!

      Reply

Trackbacks

  1. Part II: Ways to Use Loan Amortization Schedules in Your Financial Plan says:
    April 18, 2017 at 10:33 AM

    […] you’re not yet familiar with how a loan amortization schedule works, check out Part I: Using a Loan Amortization Schedule first. Then come back here and learn a little bit more about ways you’ll be using the loan […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Email
  • Facebook
  • Pinterest
  • RSS
  • Twitter
  • YouTube

ABOUT ME

I'm Kathryn Hanna-wife, mother of 3, a Christian and a Certified Public Accountant. I love to budget (really, I do!), build spreadsheets and spend money on travel, sewing supplies and good chocolate. Read More…

12 Month Financial Plan for 2018

Subscribe below to get my free 12 Month Financial Plan challenge with tasks for each week/month and to receive monthly updates, tips as well as other great resources for improving your finances!

Categories

Most Popular Posts

  • 5 Factors That Influence Financial Decision Making
    5 Factors That Influence Financial Decision Making
  • Tracking Your Medical Expenses & Claims
    Tracking Your Medical Expenses & Claims
  • Tracking Your Debt Goals
    Tracking Your Debt Goals
  • Why Spreadsheets are the Best Way to Handle Your Personal Finances (A Sneak Peek At Mine!)
    Why Spreadsheets are the Best Way to Handle Your Personal Finances (A Sneak Peek At Mine!)
  • Part I How To Use a Loan Amortization Schedule: A Detailed Tutorial
    Part I How To Use a Loan Amortization Schedule: A Detailed Tutorial

Let’s Connect

  • Email
  • Facebook
  • Pinterest
  • RSS
  • Twitter

More about the author

I'm Kathryn Hanna-wife, mother of 3, a Christian and a Certified Public Accountant. I love to budget (really, I do!), build spreadsheets and spend money on travel, sewing supplies and good chocolate. Read More…

My Latest Pins

COPYRIGHT

All images, text, spreadsheets and files are property of the Making Your Money Matter blog. All free printables and file downloads are offered for personal use only. Please link back to my website blog posts for any file downloads and not directly to the files. Thanks!

DISCLOSURE & PRIVACY POLICIES

Copyright © 2019 · Graceful theme by Restored 316