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

Photo Credit: Dai KE

Photo Credit: Dai KE

I’m just going to start off with this:  if you have a loan or expect to take out one for any reason – student, car, home – within the future, you will want to read this post.  Trust me.

When I first started my home buying process way back in 2013, I was like a deer in the headlights:  staring at this large responsibility coming my way but completely stunned as to which way to turn.  I knew roughly how much I wanted to pay per month on my mortgage, and with the help of some mortgage payment calculators found through a simple Google search, I knew how that translated into a home price.  However, what I didn’t know is how much it was REALLY going to cost me.  In other words, how much interest would I really be paying over the course of the loan?  And what portion of my payment would be applied to my loan balance each month?  And how would a change in my interest rate or length of my loan would affect either of those things?  Luckily I had this little tool up my sleeve, which I now share with you.  Open it, download it, save it.  It’s a gem.

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:

Plug that info in, and here is what your monthly payment and total interest will be, along with your first year of payments (the spreadsheet shows every payment until the loan balance is distinguished but I know you guys didn’t want to stare at all that right now):

Yikes, right? That’s a lot of interest.  In the first year alone, over 65% of your annual payments are going straight into the bank’s pocket in the form of interest.  Now, change that to 15 years at a 2.875% interest rate (shorter loans have lower rates), and your total interest nosedives over $65 grand to a little over $37,000.  Quite a nice bit of savings, isn’t it?  The only trade-off is that your monthly payment increases by $365 to $1,095, and that is the reason why most people go with the 30-year mortgage. Because even though in the long run they are shelling out way more cash to pay for the same amount of house, it still gets them into that bigger home with the game room and outdoor pool you just HAD to have.  Kind of makes you cringe just thinking about all that lost moolah, doesn’t it?

Go ahead and take the time now to download and play around with this spreadsheet.  Once you get comfortable with it, I can assure you will be praising the Big Guy above that you have it.  This tool alone gives you the capability to come to the table with the best information possible, and when you’re talking about your money, isn’t that what you really want?

As always, if you have questions about anything in this post, please feel free to ask in the comment section below or email me directly using the icons at the bottom of the page or by filling out the form on the “Contact Me” page. 

Here’s to making informed decisions!

Photo Credit: Josh Wilburne

Photo Credit: Josh Wilburne