« Calculator List | Main | Ramit’s Retirement Planning Guide »
Ever Wonder What a Mortgage Amortization Looks Like?
By JLP | May 23, 2006
For some of you this may be very basic. For others, you may be wondering what a mortgage amortization looks like. Well, today is your lucky day! Are you excited? Well, you should be! Just kidding. Seriously, a mortgage amortization is simply a schedule of when a mortgage is paid off. The example I’m going to show you is that of a 30-year fixed mortgage. I set up an Excel spreadsheet to do the calculation and added some notes to show you how the calculation is made. You can even download the Mortgage Amortization Excel file I used if you are interested.
Anyway, here’s what the spreadsheet looks like:
Pretty cool, heh? The information at the top is used to make the payment calculation and to figure the interest portion of each payment. The Period Rate (should be called “Periodic Rate” but I messed up) located in cell D2 is calculated by dividing 7 (cell D1) by (12 X 100). Or, expressed like this:
The only other column that might need explaining is the “Ending Balance” column. To calculate the ending balance after each payment, simply subtract the principal portion of the payment from the beginning mortgage balance. For example, on the first line of the amortization, the beginning balance is $100,000. The first payment of $665.30 was composed of $583.33 interest and $81.97 in principal. So, the ending balance for the first month was $99,918.03 ($100,000 – $81.97 = $99,918.03).
If you look, you’ll notice that the second payment has a slightly smaller portion of the payment going towards interest. That’s because the amount owed on the house is getting smaller. However, at the end of ten years, you will still owe over $85,000 on the mortgage.
See, finance is fun, isn’t it?
Topics: Financial Math Basics, Mortgages | 25 Comments »
May 23rd, 2006 at 10:51 am
Here’s a slightly less well know “fun fact”: the amount that goes towards principal goes up each period bya factor equal to the periodic interest rate (i.e the amount going towards principal in each month is equal to the amount towards principal in the previous month times 1.0058).
May 23rd, 2006 at 2:35 pm
Knowing how amortization schedules work is extremely important, because there’s lots of counterintuitive stuff that can fall out of them. For instance, if you are well into paying off a loan and are interested in paying the house off, you may be better off sticking with an existing higher-interest mortgage than refinancing into a lower-interest mtg. I knew many people who did non-cash-out refinances 20 years into a 30 year fixed mortgage thinking they’d “save money over time” and “have more tax deductions” because the new mortgage interest rate was lower…
Also, making additional principal payments early in a mortgage – when you’re otherwise mostly paying interest – helps much more than additional principal payments toward the end of the mortgage, when most of the interest has already been paid off.
May 23rd, 2006 at 2:45 pm
Wow! That’s some interest paid! Could you please send me a copy of the excel file? Much Thanks.
May 23rd, 2006 at 4:13 pm
that is awesome. i’d love a copy as well. any spreadsheet that works out the negative amortization? that would be interesting, and quite scary!
May 23rd, 2006 at 4:29 pm
Thank you
! I got it!
May 23rd, 2006 at 4:58 pm
Thanks for the great post. I would love a copy of the excel file if possible. Thanks.
-Ed
May 23rd, 2006 at 8:53 pm
Foobarista has a point. The last time we refinanced (we have done it three times in 9 years!), the mortgage guy told us that we would not save any money (even though the interest rate was about a percent lower) unless we paid it off in 10 years instead of 15 years. We are paying extra, and hope to meet that timetable before we retire.
May 24th, 2006 at 11:04 am
Please email me the Excel spreadsheet. Thanks, Scott
May 26th, 2006 at 2:52 pm
Very interesting…Could you send me a copy of the spreadsheet
June 27th, 2006 at 10:43 am
Hey, Great article. I’d like to have a copy as well. Thanks!
July 4th, 2006 at 1:26 pm
Could you please send me a copy of the excel spreadsheet. Thanks for the info.
July 15th, 2006 at 1:44 pm
Hi,
I have seen two of your postings and they are very good. I was wondering if you could tell me how you calculate the interest, the number in cell D9.
I would also really appreciate it if you could also send me the excel file.
Thanks
Nag.
July 20th, 2006 at 7:57 am
Could you please send me a copy of the negative am spreadsheet?
Thanks,
Mark
July 23rd, 2006 at 4:48 pm
Hi,
Looks good. Care to swap excel files?
I have a similar one for Canadian mortgages (interest is calculated slightly differently, semi-annually).
Cheers
August 2nd, 2006 at 2:28 pm
I’d like a copy of the spreadsheet, if you’d be kind enough to e-mail one.
Have a good one,
-Matthew
August 18th, 2006 at 11:21 am
If at all possible, I’d like a copy of the spreadsheet. Thanks.
November 3rd, 2006 at 3:55 pm
Could you please send me your spreadsheet. Thank you. I would also like to know how to calculate the negative amortization loan. Could you please help me.
November 7th, 2006 at 8:41 pm
Could you please send me a copy of the excel spreadsheet. I would like to apply to Interest Semiannual (Biweekly Canadian Mortgage)
February 17th, 2007 at 12:56 pm
Please send this to me. Thanks Ever Wonder What a Mortgage Amortization Looks Like? “If you are interested in a copy of the file, send me an email and I’ll send it to you that way.) I used if you are interested.”
March 29th, 2007 at 11:29 am
[...] Notice that the beginning balance for the second month is smaller than the beginning balance from the previous month. That’s because a portion of your payment is going towards the principal. As you continue to pay on your mortgage, the percentage of each payment that goes towards interest will decrease while the amount going towards the principal will increase. Towards the end of the mortgage term, most of the payment will go towards principal and very little will go towards interest. For more on how the math of a mortgage works, see this post I wrote last year. [...]
May 14th, 2007 at 10:28 am
[...] Ever Wonder What a Mortgage Amortization Looks Like? [...]
July 15th, 2007 at 10:31 pm
Very good tutorial. It always amazes me how a lot of people don’t seem to understand the mechanics of financial products. I run a mortgage information site myself and anything that can help to inform people and better equip them to understand how finance works is a good thing.
July 26th, 2007 at 6:25 am
I downloaded the amortization spreadsheet. Incredibly helpful and so useful for being able to see how things could pan out. It’s quite calming having this knowledge at one’s fingertips! Many thanks.
June 23rd, 2009 at 12:29 pm
Do you know if there is a neg am calculator available that is compatible with excel?
July 28th, 2009 at 11:25 am
Hi, I am trying to figure out how to do the payment amount per year vs every month? Can u please help? Doing a 30 yr loan, at 6% and trying to figure out how to put that formula in excel? please advice. Thank you.