Amortization means “to kill”. Follow the etymology far enough, and you end up with the Latin root of “mort”, meaning death. What are you killing? Your debt, of course.
You can use an amortization schedule (or amortization table) for any type of loan, but such a schedule is most commonly used with mortgages due to their long term nature. You can find mortgage calculators on plenty of sites, but if you want to understand what’s really going on, you can get yours hands dirty and set up your own loan amortization schedule.
Setting Up A Loan or Mortgage Amortization Schedule
Let’s start with a sample that I set up for Excel. I’ll walk you through the contents of each cell. Let’s say you’re borrowing $100,000 to purchase a home.
| A | B | C | D | E | F | |
| 1 | Amount | $100,000 | ||||
| 2 | Interest Rate | 4% | ||||
| 3 | Years | 30 | ||||
| 4 | Payment | =PMT((B2/12),(B3*12),(B1*-1)) | ||||
| 5 | ||||||
| 6 | Month | Payment | Interest | Principal | Extra | Balance |
| 7 | =B1 | |||||
| 8 | Jan-12 | =$B$4 | =F7*($B$2/12) | =B8-C8 | =F7-(D8+E8) |
You could tighten this up a bit if you prefer, but I like this format.
1. B1, B2, B3, and A8 are sample values. You’ll need to plug in your own values to make your amortization schedule work. Note that B2 should be formatted as a percent.
2. B4 shows the formula to calculate a payment. It uses the PMT function of Excel. Obviously, using the PMT function of Excel is a bit of a cop-out. You can also calculate the payment by hand — here’s how.
I’ll assume that you’ll use the Excel function. It has three required parameters.
- Interest rate — We’re dividing the interest rate in B2 by 12 to give the monthly interest rate.
- Number of periods — Essentially, this figure references the number of payments. For mortgages, this will be the number of months, so we multiply the number of years in B3 to get the number of months.
- Present value — We’re multiplying the loan amount by -1. Why? Because the present value of the loan to you is -$100,000, since it’s $100,000 of debt.
3. B7 is just the loan value from B1.
4. B8 is the payment amount calculated in B4. Note that this is an absolute reference (denoted by the $s). You’ll be filling down later, and all the cells need to reference B4.
5. C8 calculates the interest. This references B2 (again, an absolute reference) and divides it by 12 to use the monthly interest rate.
6. D8 calculates how much of the payment goes toward principal, simply be substracting C8 from B8.
7. If you’re paying extra toward the principal, you can put this into E8.
8. F8 calculates the new balance by taking the previous balance (F7, NOT an absolute reference) and subtracting the amount of principal paid off (D8) and any extra principal payment (E8).
OK, now just fill down. You’ll want 180 rows of numbers for a 15 year mortgage or 360 for a 30 year. I have an Excel spreadsheet with a 30 year on one tab and a 15 year on another, so that I can easily flip back and forth.
You may notice a very small ending balance (either positive or negative). This can happen if the actual payment amount is a fractional amount that has been rounded. The fractions of a penny can add up and create a small balance (no more than $1.80 on a 30 year or $0.90 on a 15 year loan schedule). This does not indicate a problem with your spreadsheet.
Calculating 15 Year vs 30 Year Mortgage Payments
OK, I know. You want some scenarios. Let’s compare a 30 year and a 15 year schedule.
Homer, Moe, and Apu buy homes in the same housing development. The homes are valued at $150,000 and each of them take out a mortgage for 80% of the home’s value, or $120,000.
- Homer’s 30 year note has an interest rate of 4.5%. His payments will be $608.02. (Note that this is just principal and interest. Escrow payments for insurance and property taxes are not included). Over the 30 year life of the loan, Homer will pay $98,888.05 in interest.
- Moe’s 15 year mortgage has an identical rate of 4.5%. His payments are $917.99. Over the life of the loan, Moe will pay $45,238.55, which is $53,649.50 less than the amount of interest that Homer will pay.
- Nobody ever accused Moe of being a brilliant negotiator. He really should have been able to get a better interest rate on a 15 year loan than Homer did on a 30 year loan. Somebody else got that better deal: Apu secured a 15 year mortgage at 3.75%. His payments are $872.67 and his total interest payments are $37,080.05. He’s paying just 37.5% as much in interest as Homer — a staggering $61,808 less!
If you can avoid ending up like Homer, you can save a lot of money in interest.
Paying Extra Principal To Shorten The Life Of Your Loan
Even if Homer has that 30 year loan, there are steps he can take to shorten the length of the loan and reduce the amount of interest he pays.
- Scenario 1: Homer cuts down on his consumption of Twinkies and pays an extra $100 toward the principal every month. We plop $100 into cell F8 and fill down. This shortens the length of the mortgage by more than 7 years and saves Homer nearly $28,000 in interest!
- Scenario 2: Marge lands the lead role in the newest Troy McClure movie. She takes her $10,000 payment for the role and adds it to the first mortgage payment. This cuts more than 4 years off the life of the mortgage and saves the Simpsons $24,000 in interest!
We’re currently at historically low interest rates. If (when) rates bounce back to more “normal” levels, the impact of making extra payments will be even greater.
About the Author: Kosmo runs The Soap Boxers.
Created January 6, 2008. Updated October 9, 2011. Copyright © 2011 The Digerati Life. All Rights Reserved.
{ 3 comments… read them below or add one }
I have a similar spreadsheet I use all the time, and would propose a couple minor improvements to yours:
1. In cell B4 I would put “round(” after the equal sign, and “,2)” after your formula. It would read “=round(PMT((B2/12),(B3*12),(B1*-1)),2)” This rounds your payment to two decimal places, which is what happens in reality. You can’t pay a fraction of a cent!
2. I would make the same change to column C (Interest) as interest is also only calculated to two decimal places.
This doesn’t make a huge difference, but it does help with some of the rounding problems one sometimes runs into.
One idea you have used that I might steal is having the regular payment in one column and the “extra” in a separate column. In my spreadsheet I just have one column with the amount I actually pay, but I like your idea of splitting the payment into “required” and “extra.”
Thanks Finance Nerd. The round function does the job on this one. I have to hand it to Kosmo for sharing his formulas with us!
@ Finance Nerd: Good catch. I’m lazy and generally just format the columns to round, but your formula is better.
I like having the “extra” column as it’s more obvious what the additional amount is. Your spouse (or anyone else) can quickly look at the sheet and know what you’re doing.