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