Why You Need a Free Loan Tracking Spreadsheet in Your Life Tool Kit
Did you know that approximately 80% of Americans are in debt with a home mortgage, home equity loan, car loan, student loan, personal loan, or some other type of loan?
Yet many do not really understand the nuances of principal and interest, what is a pre-payment to principal, how to make extra payments, and why it can be good to make pre-payments.
As borrowers, we need to understand and forecast costs over time to make better decisions such as:
- “Can I afford the monthly payment?”
- “How many years earlier can I pay off the loan by making extra pre-payments?”
- “How much money do I save by making extra pre-payments?”
- “I just received a $5,000 bonus at work / or I just inherited $15,000. Should I use it to pay down my home loan?”
- “Should I choose 15-year mortgage or 30-year mortgage with pre-payments?”
- “What will my monthly payments need to be to pay off the loan 5 years early? 10 years early? 15 years early?”
- “Did my bank accidentally give themselves extra interest rather than putting all the pre-payment toward principal?”
To help answer these questions, consider the following six examples using a free loan amortization Excel spreadsheet allowing you to enter your future, present, and/or past loan terms and model different payment scenarios. You can analyze the various costs and benefits to arrive at the best decision for your unique circumstances.
Example #1: A Simple Loan Amortization Calculator
Imagine taking out a conventional 30-year fixed rate mortgage at 6.00% APR (annual percentage rate) from a bank to purchase your first home. The loan amount (principal) you want to borrow is $250,000.
Now let’s answer the following questions by plugging the loan terms into the spreadsheet…
- “How much will my monthly payment be?”
- “How much will it cost to borrow that money?”
The spreadsheet model yields two key take-aways.
The monthly payment is $1,498.88 (note that this includes principal and interest, but not escrow — the property taxes and insurance).
The total interest cost is $289,595.47. The interest exceeds the principal! More is paid to the bank in interest to “rent” the $250,000 than was originally borrowed. After 30 years of payments completed as originally scheduled, you would have paid back $539,595.47 ($250,000 original loan amount / principal + $289,595.47 in interest).
The good news is that you can reduce the total interest owed if you pre-pay the principal as described in Example #2.
Example #2: Reduce Loan Cost and Duration with Extra Pre-Payments
Imagine you want to reduce the total interest paid to the bank for the same 30-year loan described in Example #1. Perhaps you want to pay an extra $250 to the principal each month in addition to the regularly scheduled payments.
Now let’s answer the following questions by plugging the loan terms into the spreadsheet…
- “How many years earlier can I pay off the loan by making extra pre-payments?”
- “How much money do I save by making extra pre-payments?”
Running the numbers yields several key take-aways which the extra monthly $250 pre-payments will provide.
As shown in Figure 2, the loan length is reduced by 9 years and 108 payments, dropping from 30 years to 21 years, and from 360 payments to 252.
The total interest is reduced by $99,750.63, dropping from $289,595.47 to $189,844.84. You simply make small regular pre-payments to principal each month. The $250,000 originally borrowed will now cost $439,844.84 instead of $539,595.47 over the lifetime of the loan.
Lets’ try repeating the same pre-payment scenarios but use $500 instead of $250 extra per month. This will reduce the loan life by 13.58 years (from 360 payments to 197) and reduce the total interest to $143,467.10. That means the $250,000 we originally wanted to borrow will now cost us $393,467.10 instead of the originally scheduled $539,595.47 over the lifetime of the loan.
Example #3: Should I Use Unexpected Extra Money to Pay Off My Loan Early?
Imagine you just inherited $15,000 or you received a $5,000 annual bonus at work. What can you do with it? How about paying off your loan faster while reducing the total cost?
Let’s analyze both scenarios on a $250,000 loan to answer the following questions.
- “I just received a $5,000 bonus at work. Should I use it to pay down my home loan?”
- “I just inherited $15,000. Should I use it to pay down my home loan?”
Modeling the scenarios in the spreadsheet yields a couple of insights.
The one-time $5,000 bonus being put to pre-payment reduces loan length by 1.5 years (18 payments) and $23,223.46 in total interest as shown below in Figure 3.
The one-time $15,000 inheritance being put to pre-payment reduces the loan length by 4.25 years and $62,781.61 in total interest.
Example #4: 15-Year vs. 30-Year Fixed Mortgages
Imagine that you found the perfect home and are finalizing the financing. Now you must decide whether to go with a 15-year or a 30-year fixed rate loan. Sure, the 15-year is done in half the time, has a lower APR, and results in a significantly less total interest paid out in the end. However, the 30-year offers less risk via a lower monthly payment; and you always have the option to pre-pay more each month. But is that 30-year plus pre-payments enough to match the 15-year in months reduced and dollars saved?
Let’s start by looking up today’s 15- and 30-year APR’s using www.bankrate.com. We will use www.aimloan.com having a 15-year APR of 4.200%, and 30-year APR of 4.839%.
Let’s run the numbers for four scenarios (Figures 4A-D below) to answer the following questions.
- “How much better is a 15-year mortgage than a 30-year mortgage?”
- “How close is a 30-year mortgage paid out at the same rate (with extra principal) to a 15-year?”
- “How high would a 30-year mortgage’s monthly payments need to be to match a 15-year loan’s results?”
Running the numbers yields several key observations.
- The “base 30-year” loan has a monthly payment of $1,317.56 and total loan cost of $474,322.75 as shown in Figure 4A.
- The “base 15-year” loan has monthly payment of $1,874.38 and total loan cost of $337,387.65 as shown in Figure 4B.
- The “matching 30-year” loan monthly payment is $1,874.38 (“base 30-year” + extra $556.82 to principal = “base 15-year”) and total loan cost of $359,495.05 with 16 years to pay off as shown in Figure 4C.
- The “equivalent 30-year” loan monthly payment is $2,162.56 (“base 30-year” + $845 to principal) and total loan cost of $337,293.00 (same as “base 15-year”) with 13 years to pay off as shown in Figure 4D.
We can draw the following conclusions from those observations:
1. The “base 15-year” loan saves $136,935.10 and takes half as long to pay off versus the “base 30-year” loan.
2. The “base 30-year” loan costs $556.82 less per month than the “base 15-year” loan.
3. The “matching 30-year” loan costs $22,107 more and adds 1 extra year above the “base 15-year”. However, the peace of mind knowing you can fall back to the lower “base 30-year” monthly payment in times of financial emergency may justify the premium.
4. The “equivalent 30-year” loan costs the same as the “base 15-year” and is paid off 2 years earlier; but it costs $288.18 more per month. Again, if you can afford it, and you want the ability to fallback to a payment that is $845 a month cheaper in tough financial times, then maybe this option makes sense.
Example #5: Manually Tracking Payments
In the previous examples, we were testing out future scenarios by altering values in the loan parameter “Xtra Pmt/Period” (red box in Figure 5) and in the cells of column “Xtra Payment” (orange box in Figure 5). This gave us meaningful feedback about savings in cost and loan duration (blue box in Figure 5).
But what if we now have a loan and want to actively track our extra payments?
Using the FREE edition of My Loan Tracker, follow these steps:
1. First “zero-out” the “Xtra Pmt/Period” (red box below) with a value of “$0.00”.
2. Then, each month going forward manually enter the actual extra payments into column “Xtra Payment” (orange box in Figure 5). These are darker yellow cells down in the amortization table. It is okay to overwrite the formulas (just be sure to keep an original copy of the Excel template).
By following this practice, you can see exactly from the current payment row how much is done (cumulative interest and cumulative principal columns off screen to the right) and how much remains (beginning balance on next row). Notice the totals and savings are constantly updated (blue box in Figure 5) as you type in new values.
Use this spreadsheet not only to model future scenarios, but also to track the history of current and past loans for borrowers who are at any point in the life cycle of their loans. Just adjust the date of the first payment in the yellow cell titled “First Pmt Date” and then manually fill in any unique pre-payments you actually made as described above to model your own existing loans. Also take a look at the graph that is automatically generated in the Graphs tab to view the past, present, and future of your loan payments.
Example #6: Where Am I At Now?
Everything previously discussed up to this point can be accomplished with the FREE Edition of “My Loan Tracker”.
You can download it from GitHub at: https://github.com/DataResearchLabs/my_loan_tracker/tree/main/download
However, there is also a STANDARD Edition that provides additional functionality:
(1) Additional graph comparing payment type with vs. without extra payments to principal as shown in Figure 6A (left).
(2) Additional graph showing monthly payment breakdown by interest, early principal, and scheduled principal over the life of the loan as shown in Figure 6A (right)
(3) Additional orange box of calculated loan results enabling you to see a summary of “Paid Thus Far” vs. “Balance Remaining” at a current payment date as shown in Figure 6B.
(4) Additional yellow text box enabling you to assign a property name (or address) to each spreadsheet as shown in Figure 6B in case you track multiple properties.
You can purchase the STANDARD Edition from Etsy at: https://www.etsy.com/shop/DataResearchLabs/
Even if you are getting along great with the FREE Edition, please consider making a purchase to support me for the time and effort put into this project. My goal is to build a useful tool that remains viable for many years into the future with at least a few people per month making a purchase. It would be nice to have this turn into a small $25-$50 monthly income stream when I go into retirement a little over a decade from now. The plan is to produce more templates and applications following this dual edition model, *IF* it proves viable.
We hope the My Loan Tracker spreadsheet can be a source of excellent information that fosters peace of mind as users explore and track the costs and benefits of their loans and payment plans.
Why You Should Monitor Your Lender
Here are a couple of stories to establish why you should monitor your monthly mortgage statements.
Story #1: “Pre-Payment NOT to Principal” — About 15 years ago, my initial loan with small regional bank was going great. I had no problems making small and large prepayments to principal for the first 3 years.
Then, my mortgage was sold to a larger national bank. After a bumpy transition, everything eventually got setup and running. After that, I resumed making small monthly pre-payments to principal. I was careful to note “pre-payment to principal” in the check’s memo line (IMPORTANT!). The months rolled by without issue.
Later that year I wrote a large one-time lump-sum pre-payment-to-principal (about 3x the regularly scheduled payment amount). I did not realize it at the time, but the bank’s systems assumed a payment larger than the regularly scheduled payment must include that payment. So instead of 100% of my lump sum going to principal, an extra “regular payment” (part interest, part principal) was pulled first before the remainder went to principal. The bank got an extra $1,000+ of interest that month. Worse than that, the bank didn’t skip the next scheduled payment, they went ahead and pulled it too. So, all subsequent scheduled payments had higher interest than expected, working out in the bank’s favor. Not to mention ignoring my stipulation on the check memo “pre-payment to principal”.
I did not catch their mistake until doing taxes in February of the next year. I had to call up the bank, have them re-adjust their amortization table so the prior lump sum was 100% principal. Then they had to re-issue the prior years’ 1098 tax form to me and the IRS. It cost me time and stress and delayed my tax filing by a couple of weeks. Had I not caught this issue, it would have instead cost me more total interest over the life of the loan, and about an additional month to pay off.
The bottom line is to watch your mortgage statements each month and verify the bank’s remaining principal matches your expectations established in your amortization spreadsheet. Be vigilant.
Story #2: “Falsely Punished for a Rounding Error” — happened to a friend of my sister’s. About two years ago, he got a great re-finance rate for less than 2% (wonder if the world will ever see these low rates again?). As karma would have it though, that cheapest rate resulted in less-than-stellar service. For the first three months of the mortgage, the lender pulled one penny less than the amount due from his checking account. And then they fined him for a late payment! It could even have gone onto his credit history had he not acted fast.
He called the lender each month until it was resolved, pointing out that it was their system pulling from his checking (not him pushing via bill-pay), and that it was their system with a rounding error pulling one penny short. He had to spend time and effort and stress on the phone pushing the lender to correct their mistake, not punish him with fines.
The bottom line is that you must look out for yourself. Some businesses are short-on-quality and short-on-ethics until you call them out.
You can watch this as a video presentation on YouTube at: https://www.youtube.com/watch?v=r1r1as0m3GU
