Ever Wonder What a Mortgage Amortization Looks Like?

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:

Mortgage Spreadsheet

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:

7 ÷ (12 X 100)

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?

26 thoughts on “Ever Wonder What a Mortgage Amortization Looks Like?”

  1. 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).

  2. 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.

  3. Wow! That’s some interest paid! Could you please send me a copy of the excel file? Much Thanks.

  4. 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!

  5. Thanks for the great post. I would love a copy of the excel file if possible. Thanks.


  6. 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.

  7. 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.


  8. Hi,
    Looks good. Care to swap excel files?
    I have a similar one for Canadian mortgages (interest is calculated slightly differently, semi-annually).


  9. I’d like a copy of the spreadsheet, if you’d be kind enough to e-mail one.

    Have a good one,

  10. 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.

  11. Could you please send me a copy of the excel spreadsheet. I would like to apply to Interest Semiannual (Biweekly Canadian Mortgage)

  12. 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.”

  13. 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.

  14. 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.

  15. 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.

Comments are closed.