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
- The 3 Worst Reasons to Buy a House -- Don't Fall for These Myths
- Are Extra Mortgage Payments Worth It? A Look at the Numbers
- How Your Mortgage Terms Could Make or Break Your Early Retirement
- 4 Money Mistakes in Your 20s That Will Haunt You When You Retire
- Recent IPOs: Where is OnDeck Capital Now?
- 7 Drawbacks of Refinancing Your Debt