Search


Subscribe to AFM


Subscribe to AllFinancialMatters
by Email

All Financial Matters

Promote Your Page Too

The American's Creed

Site Sponsors

Books I Recommend


AFM in the Media


Money Magazine May 2008

Real Simple March 2008

Blogroll (Daily Reads)

« | Main | »

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:

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?

Topics: Financial Math Basics, Mortgages | 26 Comments »


26 Responses to “Ever Wonder What a Mortgage Amortization Looks Like?”

  1. The Unknown Professor Says:
    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).

  2. Foobarista Says:
    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.

  3. Bryan Says:
    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.

  4. daniel Says:
    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!

  5. Bryan Says:
    May 23rd, 2006 at 4:29 pm

    Thank you :-) ! I got it!

  6. Ed Magos Says:
    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

  7. sam Says:
    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.

  8. Scott Says:
    May 24th, 2006 at 11:04 am

    Please email me the Excel spreadsheet. Thanks, Scott

  9. Tyler Says:
    May 26th, 2006 at 2:52 pm

    Very interesting…Could you send me a copy of the spreadsheet

  10. Jamie Says:
    June 27th, 2006 at 10:43 am

    Hey, Great article. I’d like to have a copy as well. Thanks!

  11. Philippe Says:
    July 4th, 2006 at 1:26 pm

    Could you please send me a copy of the excel spreadsheet. Thanks for the info.

  12. Nagendra Says:
    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.

  13. Mark Says:
    July 20th, 2006 at 7:57 am

    Could you please send me a copy of the negative am spreadsheet?

    Thanks,

    Mark

  14. John W Says:
    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

  15. Matthew A. Says:
    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

  16. Dan S Says:
    August 18th, 2006 at 11:21 am

    If at all possible, I’d like a copy of the spreadsheet. Thanks.

  17. LAduru Says:
    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.

  18. Carlos Says:
    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)

  19. MEV Says:
    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.”

  20. AllFinancialMatters » Blog Archive » How an Interest-Only Mortgage Works Says:
    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. […]

  21. AllFinancialMatters » Blog Archive » The Top 5 Reasons to Pay Off Your Mortgage? Says:
    May 14th, 2007 at 10:28 am

    […] Ever Wonder What a Mortgage Amortization Looks Like? […]

  22. Tom Allen Says:
    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.

  23. Richard Benjafield Says:
    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.

  24. Steve Hanna Says:
    June 23rd, 2009 at 12:29 pm

    Do you know if there is a neg am calculator available that is compatible with excel?

  25. Noshin Khoja Says:
    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.

  26. amortization formula Says:
    December 27th, 2009 at 12:51 am

    This sample is very clear and easy to understand but I need to know how to write the formula in excel. Could you please explain more a bit? Thank you

Comments