How To Create Your Own Home Mortgage Amortization Schedule and Calculate Your Monthly Payments

As some of you may know, I am planning on purchasing a condominium or townhouse during the summer/fall timeframe of this year.

In order to prepare for this undertaking, I have been digging out old personal finance/mortgage books from the shelves and from under my bed to make sure I have the appropriate information needed to make an informed decision regarding the mortgage that I take out to buy the property.

Recently, when I was reading one of these titles, I noticed that the author devoted a lot of time to discussing the various costs associated with homeownership and how to determine the price of a house you can comfortably afford.

However, several things that the book did not fully address were 1) how I could personally determine what my monthly payments would be, given a certain loan quantity, and 2) how to put together an amortization (or home mortgage loan payoff) schedule.

These two related topics will be the subject of today’s post.

First, just how are these two topics related? Well, it turns out that by putting together an amortization schedule in a format that is friendly to Excel’s Solver or Goal Seek, functions, we can then use that to solve for what the appropriate monthly payments will be.

So, let’s get started building an amortization schedule! I have put together an example Excel spreadsheet template, which can be found at the link below, that you can use as reference throughout this tutorial. To obtain a copy that you can edit, just click the file menu and select the “download as an Excel file” option.

Goolge Docs – Mortgage Amortization Schedule

Note: this amortization schedule assumes a fixed interest rate and monthly period payments. ARMS (or adjustable rate mortgages) and biweekly mortgage payment plans will be discussed in a future post.

Loan Specifics
First, you will need to input the specifics of the loan in to cells in Excel so that equations can be properly set up to calculate the amortization schedule. The specific pieces of information you need are shown in the purple highlighted cells of the spreadsheet, and are as follows:

• Loan Amount (please note: this is not the property value, just the amount that you borrow for the house. This can be calculated by subtracting your down payment from the price you paid for the property.)
• Interest Rate on Loan Amount
• Term, In Years, of the Loan (the default here is 30 years, but typical loan periods can also be 15 and 20 years).

Setting up the Amortization Schedule
To set up the amortization schedule, you will need a minimum of 5 columns set up in Excel, titled as outlined below:

• Column C = “Period.” – This is the designation of the monthly period in which your payment occurs. Since we are dealing with monthly payments, in this column, you will need Periods from the number “1″ through n years x 12 months/year, where n represents the term in years of the loan as specified above.
• Column D = “Monthly Payment.” – This represents the fixed monthly period payment that you owe on the loan. If you already have been given your fixed monthly payment, just enter that in the first row of Column D after the column title. If you desire to calculate what your monthly payment will be, leave the first row blank, and then in each cell from period 2 through period n x loan term in years, input the equation to draw the monthly payment value from the cell directly above it.
• Column E = “Interest Payment Amount.” – This represents the portion of the monthly payment that consists of interest on the loan amount borrowed. It can be calculated by multiplying the fixed annual interest rate times the amount of the principal loan balance remaining in that specific period, and then dividing by 12 to determine the portion of interest owed in that specific month.
• Column F = “Principal Payment Amount.” – This represents the amount of the monthly payment that counts towards paying off the principal amount of money borrowed with the mortgage loan. It can be calculated by subtracting the interest payment amount in Column E from the total monthly payment in Column D.
• Column G = “Principal Amount Remaining After Payment.” – This represents the amount remaining on the principal loan balance after the monthly payment has been made. It is calculated by subtracting the Principal Payment Amount in Column F from the principal amount from the previous period.
• *Extra Column, Column H* = “% of Monthly Payment as Interest.” – While this column is not technically a part of the amortization schedule, it is interesting to see the magnitude that you are paying towards the interest on the home loan, especially in the first few years. This can be calculated by dividing the Interest Payment Amount in Column E by the Monthly payment amount in Column D.

Once you have created these formulas in the first few rows of cells in each of the columns, just copy the formulas down the entire column until you fill up the table as needed for your loan term.

Great job! You have just built the framework for your amortization schedule. The hard part is done!

Calculating Your Monthly Payment
While in many cases, the monthly payment quantity will be calculated for you, it is nice to be able to know how this number is generated. And, once you have created all of the formulas as explained above, Excel does the rest of the work for you! Here’s how.

To calculate your monthly payment, complete the following steps:

1. In the spreadsheet you have created, click on the cell in Column G that represents the final loan payment on your mortgage. This will be either Period 180 or Period 360, depending on if you have a 15 or 30 year home loan term, respectively. In the spreadsheet I created at the link above, this would be cell G361 for a 30 year mortgage.
2. Next, in Excel 2003, on the top menu, click Tools –> Solver
3. What you will now do is use Excel’s iterative calculation Solver function to “solve” (similar to solving a complex algebraic equation) for the appropriate monthly payment amount that will cause the principal loan balance to go to $0 at the end of the mortgage loan term.

• To do this, set the “target cell” discussed in step 1 to a “Value of” “0″ by changing the cell that represents the first monthly payment in Column D. In the spreadsheet I created, you would “Set target cell G361 to a value of 0 by changing cell D2.”
• Next, click solve. Excel will automatically change all of the values in Column D to the appropriate monthly payment value that makes your principal loan balance go to 0 after the loan term.

There you go! You have created your own mortgage loan amortization schedule and are now able to calculate your monthly payment accordingly.

Other Uses of This Amortization Schedule
After you have created the schedule and solved for your monthly payment, you should not abandon this extremely useful tool. By entering the purchase price and down payment you paid for the home in the purple cell area in Column A, you can use this tool for the following purposes:

• Keeping track of the total value of home equity (ownership) you have built up in the house. This can be calculated by adding up the down payment in Column A with the sum all of the principal payments you have made to date.
• Keeping track of the percentage of ownership you currently have in the house (this is also called percent equity).

• Why would you want to keep track of this? Easy! If you’re like most of us and didn’t have the cash for a 20% down payment on the house, you were required to purchase Private Mortgage Insurance, or PMI, in order to insure that the lender would be covered if you default on the loan. However, this PMI insurance is no longer required once you have built up greater than 20% equity in your house. So, this is a handy way to keep track of this and will save you money in the long run!

• Also, in Column D, as you pay off your monthly payments, record the monthly payment amounts in this spreadsheet. You’ll be able to play around with the effects and savings you can incur if you decide to overpay each month on your required monthly payments (sometimes called pre-paying the loan – more on this subject later).
Please let me know if you have any questions. And, as always, keep on learning!

Jacob

To receive updates on topics such as this one as soon as they are published, click on the link below to subscribe to My Money Blog:

Subscribe to My Money Blog via Email