A Mortgage Calculator Spreadsheet

Last week we discussed the advantages of prepaying your mortgage. Hopefully, you're considering how you might make that a part of your household budget. Some very logical questions are: how much should I prepay and what effect will it have on the life of the mortgage.

This week we'll show you how to create a spreadsheet that will do the math for you. Unfortunately, the topic is a bit boring. So anytime you start to drift off, make sure you think about how you're going to use all that money that you save at the end of the mortgage! That should be enough motivation to bring you back to the 'how to' portion of our program!

We'll begin by telling you how to construct the spreadsheet on a cell by cell basis. I'm working in Excel, but there's no reason that the formulas won't work in Lotus or any other program. You could even use a calculator and paper but it would take a quite a bit of time.

For each cell we'll show you the entry in parentheses ( ). Enter this exactly as is. Then there will be a brief explanation so you know what the cell is telling you.

OK, let's get started. Begin with Row 3. We'll put our headers in this row. If you're familiar with 'format/cells/alignment/wrap text' you'll want to turn it on for row 3. Otherwise, either use abbreviations or make your columns wider so you can see all of the header.

In cell A3 enter (Month #). This will represent a counter for the number of months on your mortgage and allow you to see when things happen as we use the spreadsheet.

In cell B3 enter (Beginning Principal). This is the amount of money that you owe today on the mortgage. In our example we used $100,000. If you have an existing mortgage check your statement or call the mortgage company to find out what this amount is. Remember, this is the principal amount only.

In cell C3 enter (plus Additional Interest). This column will show us how much additional interest we owe this month.

In cell D3 enter (minus Payment). This will be the column where you enter how much money you'll pay towards your mortgage each month. It will also be the column that you'll use to make entries to show how prepayments will effect the life of your mortgage.

In cell E3 enter (equals Ending Monthly Principal). This column will show you how much money you owe at the end of the month after adding the additional interest and monthly payment. In the month that this column goes negative (i.e. has a negative answer) you have finished paying your mortgage.

In cell F3 enter (Monthly Reduction in Principal). In this column you'll be able to see how much of the mortgage was actually paid off each month.

In cell G3 enter (Cumulative Reduction in Principal). Here you will see how much principal you have paid off since the beginning of the spreadsheet. This column should equal the starting amount of your mortgage in the month that the "Ending Monthly Principal" reaches zero.

In cell B1 enter (Annual Interest Rate). In cell A1 enter your interest rate. Put in .10 for a 10% annual rate or .075 for a 7 1/2% rate. In our sample we'll use a 9% to illustrate. By changing cell A1 you'll be able to see what happens to your mortgage at different interest rates. Unfortunately the spreadsheet is designed for a fixed rate mortgage. In the interest of simplicity we'll keep it that way. If you have a variable rate mortgage you can go in and modify column "C" to show the effects of different interest rates for specific years.

OK, so now we've got our skeleton. We're halfway through the boring part. Let's put some formulas in our spreadsheet and let the PC do some math for us!

In cell A5 enter ( 1 ). In A6 enter ( =A5+1 ). Now copy this row down until you have the number of months that your mortgage runs. If you have a 30 year mortgage you'll need 30 years X 12 months per year = 360 months. Once you're done the column should read down 1, 2, 3, etc. until you get to 360.

In cell B5 enter (=E4). In cell B6 enter (=E5). Now highlight both cells B5 & B6 and copy down for the number of months that your spread sheet runs. You should have a column that reads =E4, =E5, =E6, etc.

In cell C5 enter (=$A$1/12*B5). Then copy this cell down for the number of months of your spread sheet. The dollar signs should 'anchor' cell A1 so that it doesn't increment as you copy down. You should see a column that reads =$A$1/12*B5, =$A$1/12*B6, etc.

In cell D5 enter the monthly payment that you make. In our sample we entered $804.63 because that would pay off our mortgage in 30 years. Enter your monthly mortgage payment here. Some of you will need to make an adjustment for taxes and insurance. If those charges are included in your monthly payment subtract them before you enter the amount in cell D5. Remember, this spreadsheet is only set up to show the effects on principal and interest of prepayments. Now copy this cell down for the working area of our spreadsheet.

In cell E4 enter the principal amount that you owe on your mortgage today. Notice that this cell is one row higher than the others. In our example we entered $100,000.

In cell E5 enter (+B5+C5-D5). Then copy this cell the length of the spreadsheet. In cell F5 enter (+E4-E5) and copy the cell the length of the spreadsheet. In cell G5 enter (+G4+F5) and copy that cell the length of the spreadsheet.

You might want to format columns B through G for 2 decimal places to make it easier to read. Adjust your column width so you can read the cells.

If your spreadsheet is working like mine and you've entered 100,000 principal and 9% interest cell E365 should read -13.52 and cell G364 will display 100,013.52.

Now, let's use our spreadsheets. First a word about the interest rate. Don't forget to put it in decimal form. In other words, 10% = .10 or 8 1/2% = .085. You might need to do some division to figure the exact decimal. For instance, 8 3/8 % = .08375. Remember that interest is stated in percentages or parts of 100. So 8% is really .08 or 8 parts of 100. You can get the fractional part by dividing 3 by 8 for .375. Then put that on the end of the whole percentage.

OK, enough about fractions. Now let's take advantage of our work. You might want to save a copy of the original at this point before we start playing with the numbers.

Let's see what happens when we add $100 to our first month's payment. In cell D5 change the payment from $804.63 to $904.63. If you go to the end of your spreadsheet you'll find that column E goes 'negative' in month 359. It should read -665.98. That means that a simple addition of $100 to the first monthly payment will eliminate your last payment completely and reduce the previous month's payment by $665.98. That's a return of $804.63 plus $665.98 or $1,470.61 on your $100 investment.

But suppose you don't have that much. Let's say you can only afford $25 per month for the next year. After that you'll wait and see how your job looks. Change your monthly payment to $829.63 in Column D for the first 12 months. Leave the rest of the spreadsheet alone.

Now go down to the end of Column E. You'll see that your mortgage is paid off in month 355. That means that you've eliminated 5 months of payments at $804.63 plus the $135.48 'extra' from month 355. The total saving on your mortgage is $4,158.63 (or 5 times $804.63 plus $135.48). That's what your $25 each month for the next year provided.

How about a real modest plan. You've decided to bring lunch once or twice a week and figure that this should save about $10 each month. How much difference will that make in your mortgage? Well, let's put in $814.63 in cell D5 and copy it down to the end of our spreadsheet. Now check the ending numbers.

You'll find that the mortgage is paid off in month #340 with $700.63 to spare. So you've saved $804.63 times 20 plus the last month's overage. That comes to $16,793.23 on our $10 monthly investment.

You get the idea of how this works. Just customize column D with whatever you want your increased payments to be. Go to the end of column E and see when the mortgage is paid off.

Now there are some things that you need to be aware of. First, the spreadsheet is only for principal and interest. Many of you will have escrow accounts for taxes and insurance. You'll need to adjust for that. You might want to add an extra column that would adjust column D for taxes and insurance to show the size of the check that you'll actually write to the mortgage company.

Also, the spreadsheet assumes that your interest is charged monthly. Some mortgages charge weekly or even daily. This will make some difference, but the end result will still be pretty close and should be good enough for you to use to make decisions.

One last word is to make sure that your mortgage company is crediting any extra money as prepayment of principal. It's safest to either check your statement or call to make sure that you were credited properly each month that you send 'extra' principal. Also make sure that your mortgage allows prepayments without penalty. Believe it or not, there are some mortgages that penalize you for prepaying.

The idea of prepaying your mortgage is a powerful one. Once you've paid off any credit card debt and car loans it's time to begin accelerating your mortgage.

Due to the wonders of compounding it takes very little sacrifice today to earn big dividends later. It's simple. The return on your investment is guaranteed. You're adding security to one of your most important assets. Why not begin working on a plan for your future today?

Gary Foreman is a former financial planner and purchasing manager who currently edits The Dollar Stretcher.com website and newsletters. You can also follow Gary on Twitter or on his blog.

Do you have a time or money saving idea that wasn't included in this article? Please send it to tips @stretcher.com. We get the best ideas from our readers!

If you liked this article sign up for our free eNewsletter Surviving Tough Times Do it today and we'll give you our ebook featuring over 200 ways to save on groceries (a $19.95 value).

Follow The Dollar Stretcher on Twitter.

I Would like to:

• Read other articles on this topic
  
• See what else is in this issue
  
• Subscribe to "The Dollar Stretcher" free weekly email newsletter
  
• visit the Problem Solving Center
  
• visit the Reference Library
  
• browse the 'author' index
  
• search 'by date' index
  
• check out Past Issues of Newsletter
  
• go see 'What's New'
  
• "Can You Help This Reader?"
  
• drop by 'Tips, Quips & Quotes'
  
• send email to the Editor of "The Dollar Stretcher"