House Hunting & Mortgage Payments & Spreadsheets, Oh My!

House-icon

As my husband and I search for a home to buy, I’ve noticed that I have at least a handful of friends who are also looking at houses.  The whole process can be incredibly fun and exciting, as you see your dreams materialize before your eyes (Walk-in closet! Renovated kitchen!  Fireplace!) It can also be daunting as some not-so-great aspects of your potential new home rear their ugly heads (Creepy door under the stairs that horror movies are based on! Unfinished basement with inexplicable locked room with a mattress in it! (The home we currently have our eye on actually has that…) Bathroom that only a child could fit in!)

But nothing can be more excruciating that the mortgage process. It’s not just about getting all of your documents together to get approved, it’s about considering all the costs associated with buying a house, like the property taxes, potential HOA fees, gas bills, renovation nightmares, and anything else that will certainly drain your bank account.  Every house that you consider will have a different asking price and property tax, so it can be tricky to keep track of how much of a monthly nut you’ll need to keep up with the payments.

Prior to business school, Excel formulae scared me.  Since business school, I’ve developed a potentially unhealthy relationship with spreadsheets.  I make one for every occasion.  I’m kind of obsessed. The home-buying process is no exception, but this time my efforts have proven incredibly helpful to my husband and me.  I’ve simplified my system and shared it below.  It can really keep all of your information organized as you find your dream home, and it handles that pesky amortization formula for you. Even if you stink at Excel, you will be able to do this.

Here’s what your end result will look like:

Let’s zoom in and see what helpful info you’ll have handy.  Note that the cost of your monthly mortgage payment is calculated, and then combined with the  taxes to give you a more realistic idea of what your bill will be each month:

Here’s how you get there:

Open a new Microsoft Excel workbook, and several rows down (I chose Row 20, but you can go further if you think you’ll be listing a lot of houses), put the titles of the following information in Column A, and in Column B put the numbers associated:  Annual Interest Rate (either the amount you were pre-approved for, or a realistic estimate), the Life of the Loan (in years), the number of payments per year (typically 12), and total number of payments per year (multiply # of years by # of payments per year–in this case 30 years x 12 per year = 360):

Next, set up the top of the sheet.  In Row 1, put the headings of all your columns: A1: Address; B1: Asking Price; C1: Monthly Taxes; D1: Loan Amount; E1: Monthly Mortgage; F1: Total Monthly Cost.  [You can add other columns for your own personal use.  On my spreadsheet, here’s what I did: G1: # of Beds; H1: # of Baths; I1: Garage; J1: Notes.

Next, fill in cells for Address and Asking Price according to your sources.  In C1 (Monthly Taxes), you’ll put a little formula.  Let’s imagine the annual property tax is $12,060 for the house in you listed A2.  In C2, type:  =12060/12  and hit enter.  This will calculate the monthly cost of the taxes.  Repeat this process for every house with its unique tax amount.

In Column D, fill in the loan amount for each house.  In my example, I decided to put a down payment of $200,000 no matter what the asking price.  So you’ll notice that the loan amount is $200,000 less than asking for each of the properties listed.  But of course many people choose this number based on % (typically a down payment of 20%, and therefore a loan amount of 80% of asking).

Column E (Monthly Mortgage) is where the real effort will come in.  In E2, type this exactly:

=PMT($B$20/$B$22,$B$23,$D2,0)

And hit Enter.  This calculates your monthly mortgage payment according to the interest rate and length of the loan that you indicated in B20-B23.

After you’ve hit Enter, click on E2 and hover your cursor over the bottom right of the cell.  A “+” will appear.  When it does, click it, hold, and drag it down several rows (to at least as many houses as you have listed, but preferably more).  This will extend that formula to every row you select below it (so you don’t have to retype and adjust for every house in your list).

In F2, you will calculate your total monthly cost by combining your mortgage and taxes.  Type: =E2-C2   and hit Enter.  Once again, click on that cell, hover until you see “+”,  and drag it down for several rows.  You now have the monthly cost for all the homes on your list.

For your final geeky detail, you will add Conditional Formatting, which will colorize the cells based on the values within them (the darker ones will be more expensive and the lighter ones will be less expensive.)  Select all the populated cells in Column C (Monthly Taxes), then click on “Conditional Formatting” in the Format section.  Select “Color Scales”, and choose the scale you prefer.  I chose “White-Green Color Scale” for mine.  Repeat the process for Column F (Total Monthly Cost), and any other column that you would like.

Now the cool thing about your new spreadsheet, is that you can easily adjust all of the information.  If you want to see the costs for everything with a different interest rate, just change the information in that cell (B20).  If you get a 15 year loan instead of 30, just change that info.  It’s very easy to manipulate all of the information.  And now you too can have an unhealthy relationship with a spreadsheet throughout your search.  Happy hunting!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s