how i saved $37,000 in two minutes: my loan amortization calculator tool

When I worked at the CPA firm, we would use this tool to analyze the business loans of our clients.  For some reason, I was led to believe that one of my co-workers (who seriously is an Excel genius – you should see the spreadsheet he has for his Fantasy Golf tournaments) came up with this little masterpiece, but I later found that it was just a downloadable form from the internet.  Seriously, all you have to do is type “Microsoft Loan Amortization Calculator Template” into Google, and you will get this one plus dozens of others that are ready to download.  Amazing how many tools are at our fingertips that we don’t know about, isn’t it?

So why is this tool better than a mortgage calculator found out on the World Wide Web?  I’ll tell you.

  1. Shows the portion of your payment that is going towards interest for every single month of your mortgage.

    This spreadsheet creates a chart with a line for every month of your loan, and each of those lines shows how much of your total monthly payment is going towards principal, how much is going towards interest, the balance of your loan after each payment, and how much interest you’ve paid to date.  This is a little disheartening at the beginning of any loan, as a higher percentage of your payment goes towards interest and your mortgage balance decreases at a snail’s pace. However, it’s great information to have and definitely motivates you to pay a little above and beyond your monthly payment in order to see that balance decline a little faster.

  2. Allows you to play with the factors

    I love how easy it is to play around with this spreadsheet.  You can change the interest rates.  You can change the term of the loan.  You can change the amount of the loan, depending on what size down payment you want to put down (which I think should always be 20%).  This spreadsheet gives you the flexibility to change every factor of the loan and see how that affects your monthly payment and total interest, and that is indispensable information to have when you’re about to spend a nice chunk of change.

  3. Includes the possibility of extra payments

    Here’s the real kicker that no other tool has really offered:  the ability to plan for extra payments.  And the best part?  You can do it one of two ways.  First, if you know you are going to pay more than what your lender requires each month (and if you do, props to you, my friend), you have the option to plug that into the “Enter Values” table at the top of the spreadsheet.  In addition, let’s say you know you get a Christmas bonus each December that you want to apply to your loan.  You can plug that number in the “Extra Payment” column within the bottom chart for the month you will apply that, and it will recognize that as an additional principal payment, recalculating the balance and length of your loan likewise. 

When I was buying my home, I used this to help me decide the length of the loan I wanted and, consequently, how much home I could afford.  Yes, I could have bought a much more expensive home if I would have went with a 30-year mortgage, but it would have cost me a lot of dolla dolla bills to do so.  In fact, if I would have went with a 30-year mortgage on my current home, I would have ended up paying $37,000 more in interest.  And the CPA in me just couldn’t justify giving all that extra hard earned cash to the bank when it wasn’t necessary.

Anyhoo, let’s get into the nitty, gritty here and do an example.

Let’s say you want to buy a home for $200,000 with a 20% down payment, and you’re contemplating a 30-year mortgage at a fixed interest rate of 3.625% (the current rate as of 1/25/15).  You will fill out the top table as follows:


I’m a writer, new mom and foodie. I love sharing what I know while making others feel beautiful. On this blog, I share my healthy lifestyle, simple meals, fitness tips and experiences.

Leave a Reply

Your email address will not be published.

Kara Bout It