I’ve talked to many people who were surprised by the amount of interest they were paying out on their loans. These ranged from student loans to house mortgages. It’s not very obvious how much total interest you’ll be paying when you first get the loan. That’s where an amortization schedule will come in handy.
Let’s say you’re buying a nice condo or a house and need some money for the down payment. You feel pretty good when you leave the bank. You just signed a 25 year loan for $100,000 with 5% annual interest rate, which sounds like a very good number. However, by the time you’re done paying off that loan, your interest will add up to about 75% of the original $100,000. That’s$75,377.01 to be exact. This is how the bank makes its money.
How is that possible? The answer lies in compounding and an amortization schedule can show you the nitty and gritty details. These schedules can be a bit of a pain to build from scratch, so we’ve created a working sample schedule for you to use. You’ll need to change the numbers to reflect your own scenario. The link to the excel file is at the bottom of this post.
A quick how-to:
1. Enter the loan amount, annual interest rate, number of years, and number of payments per year:
Excel will calculate your monthly payment automatically.
2. Adjust the amortization table if required. The default table includes 300 payments (25 years of monthly payments so 25 * 12). You may have to add or subtract rows. If you have a 30 year loan with quarterly payments, you’ll need 30*4=120 rows.
3. Once the rows are adjusted properly, you can view the statistics about your loan. You’ll see how much you’ll pay in total interest, in total principal, and the grand total for your loan.
Another useful feature of an amortization schedule is that for every payment period, you can see the exact breakdown of interest versus principle. For example, in the 20th payment on our default spreadsheet, you’ll pay: $402.86 in interest and $181.73 in principal.
When you take a look at this loan, you’ll notice that the majority of your initial payments will be covering interest. Only after the 134th payment will you be paying more towards the principle of your loan rather than its interest.
And that’s about it. These amortization schedules will come in handy when you’re shopping around for loans. Use them to your advantage and see how much interest you’re really paying. The excel file is below:
The Rentables Sample Amortization Schedule (Microsoft Excel Spreadsheet)
If you’d like to build your own amortization schedule from scratch, take a look at this guide:
University of South Dakota: Step By Step Example of PMT Function
| $75,377.01 |
I wanted to add that Canadian mortgages compound twice per year, and this is not reflected in the sample amortization schedule. However, the sample provided still does a great job of illustrating the amount of interest paid on a loan, especially in the early days.
The compound period could be annual, semi-annual, quarterly, every two months, monthly, semi-monthly, every two weeks, or payment schedule for a fixed-rate loan, with optional extra payments The last payment is adjusted to the nearest cent, or the rounding can be set based on US or Canadian mortgages.
[...] Amortization Schedule: How Much Interest You're Really Paying … [...]
[...] Amortization Schedule: How Much Interest You’re Really Paying | The Rentables Apartment Rental Blo… [...]
[...] got into the topic of compounding in the Amortization Schedule article, which talked about how much interest you’re really paying on your loans. This article [...]