What is Foolsaurus?


Amortization schedule

Amortization schedule is a schedule that provides a breakdown of the principal and interest payments, and the amount outstanding at any given point during the amortization period.

Expanded Definition

When you take out a mortgage from the bank, they'll give you a set of pages that lays out exactly how your monthly payments are split up between paying the bank interest and paying down the principal of the loan. It will show something like 360 lines (for a typical 30-year mortgage). That's the amortization schedule.

Most people file that away and never look at it again. But suppose you want to find out exactly how much of each monthly payment is going to the bank as interest and how much is going toward the loan. Well you can set up your own amortization schedule using a spreadsheet program like Excel. Here's how.

First, you need to know exactly how much of your monthly payment is just for principal (P) and interest (I). Many people pay escrow which includes an extra amount for property tax and home owner's insurance. We're not interested in that. So how do you figure out how much is P & I? Simple:

Your monthly payment is <math>M = P * \frac{J}{1 - (1 + J)^{-N}}</math> where P = principal amount, J = interest rate divided by 12, and N = # of months (360 for a 30-year loan).

On a calculator for a $100,000 mortgage at 5%, here it is, key stroke by key stroke, starting with the J term in the denominator and working out from there:

0 . 0 5 / 1 2 = + 1 = <math>y^x</math> 3 6 0 +/- = +/- + 1 = 1/x * 0 . 0 5 / 1 2 = * 1 0 0 0 0 0 =

You should get 536.82.

Once you have that, open up your spreadsheet and fill it in as follows, assuming you start in the upper left cell of A1:

  A B C D E
1 Month Balance Principal Interest Equity
2 0 $100,000.00      
3 1 =B2-C3 =536.82-D3 =B2*0.05/12 =100000-B3

Once that is done, highlight cells A2 and A3. Click-and-hold the black box in the lower right corner and drag down until you reach 360. Then, highlight cells B3 through E3 and double-click the black box in the lower right corner to copy those formulae down through that 360th row. The first few rows should look like this:

  A B C D E
1 Month Balance Principal Interest Equity
2 0 $100,000.00      
3 1 $99,879.85 $120.15 $416.67 $120.15
4 2 $99,759.19 $120.65 $416.17 $240.81
5 3 $99,638.04 $121.16 $415.66 $361.96

That is your amortization table. It won't quite reach zero at the end due to rounding of that monthly payment. But it is certainly close enough.

Related Terms

Recent Mentions on Fool.com