How to Build Your Own Amortization Schedule

how to calculate amortization schedule

Looking for a dull, but financially eye-opening home project? Great! Today, we're going to build an amortization schedule. All the tools you need are: 1) an electronic spreadsheet with PMT (payment calculation), addition, subtraction, division, and multiplication capabilities (I use Excel) and 2) this guide.

Let me go ahead and answer the questions you may have after reading this guide and completing your project:

Hey, I have a 30-year fixed rate (6%) mortgage loan of $200,000, but why is my payment so much higher?

This schedule does not include amounts paid to escrow, such as homeowner's insurance and property taxes.

Won't I have more equity built up over time?

For the purposes of this demonstration, equity is based on principal paid down; in real life, equity is calculated as the value of your home less the amount of outstanding loans (payoff amounts).

Can I put my numbers (mortgage loan amount, interest rate, etc). in this spreadsheet and figure out my own mortgage amortization?

Yes, though you will need to make adjustments if you do not have a fixed rate loan.

Where the heck is the PMT function?

In Excel, go to "Insert," click on "Function," select "Financial" from the categories, and then click on "PMT." You can also click on that E-looking

key on your tool bar.

Why bother?

This guide is the first step in a series to show you how to pay down your mortgage faster than the original schedule (with a discussion of whether you should pay it down to follow in a subsequent post). It also mirrors an explanation given by UFirst in regard to its MMA (Money Merge Account) program (again, with further explanations in subsequent, equally intriguing posts), which a reader has asked about.

So. (the following information is also in the attached spreadsheet, which has the full schedule to Year 30, Month 360 -- )

Given the following:

Primary Mortgage $200,000

Term in Years 30 (360 months)

Annual Interest Rate 6.00% (.50%/month or .005/month)

Payment $1,199.10 PMT(6%/12,360,-200000,0,0)

Annual Interest Rate/Number of Months in a Year, Number of Payments, Mortgage Balance (note: don't use commas to separate thousands)

Value at the End of the Term, Payments Are Made at the End of the Month

Build the Spreadsheet

  • Balance= Primary Mortgage. 1
  • Payment= Payment (PMT). 2
  • Principal= Payment - Interest. 4
  • Interest= Balance x (Annual Interest Rate / Number of Months in a Year). 3
  • Equity= Principal. 5
  • Total Interest= Interest. 6
  • Total Payments= Payment. 7


Category: Bank

Similar articles: