Introduction.  There are many other issues of interest when selecting a mortgage.  We will use these issues to modify and improve the functionality of our spreadsheet/workbook.  Some of the broadest issues are

• How much do you actually pay over the duration of the loan?

• How does your down payment affect the loan?

• How does the down payment affect your PMI?

• Should you pay for points to decrease the interest rate?

• How does the duration you plan to stay in your home influence your decisions?

• What sort of tax break are you going to get?

How Much Are You Paying?  While you have probably never thought about it before, you should now use your MortgageBasics spreadsheet to figure out how much money you'd be paying to the loan company over the duration of the loan.

1. Highlight the cells A1 ... K23 on the Payments spreadsheet.

2. Copy them into Sheet2 by clicking on cell A1 and then pasting.  Notice these copies everything completely and maintains the integrity of the underlying cell formulas and relationships.

3. Now you want to modify the contents of cell C5 to multiple the payment by the number of times the payment will be made.  This will computer the total amount of money you will eventually pay to the mortgage holder.

4. The modified cell formula is

=PMT($B5/12,12*$B$23,C$4)*$B$23*12

Notice that the only modification is to multiply the PMT function by $B$23*12.  This takes the loan duration in years and multiplies it by the number of months in each year.

5. Now you want to copy this formula into the other cells.
6. You also want to modify the contents of cell B2 to read Total Payout Over the Duration of the Loan.

Hopefully you have now recovered from the shock of seeing how much money you will actually pay for the advantages of "owning" a home!  Just think, you can easily end up paying more than half a million dollars to own a $180,000 home.  You should change the duration of the mortgage to see how this improves these payouts.

7. Now you want to right click on the tab at the bottom of this sheet
8. Select Rename in the pop up menu that appears
9. Type in Total Payout for the name of this sheet and save the workbook.

Influence of the Down Payment.  So far we have "confused" the amount of the mortgage with the price of the house.  Now we will slightly modify our Payments spreadsheet to include entries for

• Price of house and lot
• Down payment percentage

These will be used to recalculate the mortgage amounts across the top row of the comparisons.  You should make the following modifications.

10. Copy the payments sheet onto Sheet3.
11. Change the name of Sheet3 to Specific Property.
12. In cell A25 you should type Price of the Property.
13. Now you should make it bold, change the font size to 12 and right justify the text.
14. In cell A27 you should type Down Payment %.
15. Now you should make it bold, change the font size to 12 and right justify the text.
16. You want to appropriately format the cells that will contain the data to reflect the percentage and large dollar amount.
17. Delete the entries in cells D4 ... K21.
18. Now you want to realign the column headers in the first three rows to center over columns A ... D.

This takes care of the inputs and formatting.  Now you want to modify how the mortgage amount is obtained.

19. In cell C4 type   =$B$25 *(1-$B$27)   which reduces the Price of the Property by the Down Payment.  The dollar signs insure that absolute addresses are used when the formula is copied.
20. Hit the enter key to recalculate the mortgage payments.  You should get a spreadsheet that looks like the following.

This new spreadsheet allows you to vary both the property price and the down payment percentage to see the influence on the mortgage payment.  Typical down payments are 5, 10 and 20%.  In practice, using a 20% down payment usually helps you get better financing and avoid the PMI monthly charge.

Considering Points.  When acquiring a mortgage it is usually possible to pay what are called points to reduce your interest rate.  While it is not always this precise, at present we will describe buying points as 

• Paying an additional 1% of your mortgage amount will allow you to reduce your interest by 0.00125 or .125%.  This is 1/8 of a percent.

• You can purchase up to 3 points.

We can modify this relationship later.

At this point we are going to modify our spreadsheet quite a bit to reflect these new considerations.

21. Delete the entries in cells B9 ... C21.
22. Delete the entry in cell A12.
23. In cell A5 type with No Points.
24. Right justify it, make the text bold and change the font size to 12.
25. In cell A6 type with One Point.
26. Right justify it, make the text bold and change the font size to 12.
27. In cell A7 type with Two Points.
28. Right justify it, make the text bold and change the font size to 12.
29. In cell A8 type with Three Points.
30. Right justify it, make the text bold and change the font size to 12.

Now we need to have a place to input the interest rate.

31. In cell A21 type Interest Rate.  
32. Right justify it, make the text bold and change the font size to 12.  Now the interest rate will appear in B21.
33. Change the format in B21 to Percentage with 3 decimal places.
34. Enter 8.5 in B21.
35. Place the following cell formulas in the appropriate cells.

36. In cell D5 you want to type Cost of the Points.
37. Place the following cell formulas in the appropriate cells.  I have used the $ signs in case you want to do some copying.

Now you should vary some of the inputs in order to see their influence on the mortgage.

Time Until Break Even on Points.  If you look at 

• the monthly payment with No Points it is $799.67.  

• The monthly payment after paying one point is $790.48.  

• The monthly savings is 799.67 - 790.48 = $9.19, not a lot of money.  

• But remember you paid $1040 to achieve this savings

• The number of months until you break even on this investment is     (1040/9.19) = 113.17 months or 9.475 years

UGH!  So before paying for points you want to make sure you plan on owning your new purchase for quite some time.  Overall the equation to determine the time to break even is

So we want to put these into the appropriate cells to estimate the time until break even for buying points.

38. Type Time Until Break Even in cell G5.

39.  Make the text bold and change the font size to 12.

40. Type years in cell H6.

41. Format cells G6 ... G8 as Number with two decimal places.

42. Type  =D6/((C$5-C6)*12) in cell G6.

43. Copy it down to cells G7 ... G8.

You can see that you would want to make sure you are going to own this property for at least 10 years to make it even a little bit worthwhile to pay for the points!

While there are many other analyses that we could carry out, these should give you some insights into the value of using spreadsheets and thinking before you spend your hard earned money!