More on Mortgages

 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. Delete the entries in cells B9 ... C21. Delete the entry in cell A12. In cell A5 type with No Points. Right justify it, make the text bold and change the font size to 12. In cell A6 type with One Point. Right justify it, make the text bold and change the font size to 12. In cell A7 type with Two Points. Right justify it, make the text bold and change the font size to 12. In cell A8 type with Three Points. 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. In cell A21 type Interest Rate.   Right justify it, make the text bold and change the font size to 12.  Now the interest rate will appear in B21. Change the format in B21 to Percentage with 3 decimal places. Enter 8.5 in B21. Place the following cell formulas in the appropriate cells.

 Cell Formula B5 =\$B\$21 B6 =\$B\$21 - (0.00125*1) B7 =\$B\$21 - (0.00125*2) B8 =\$B\$21 - (0.00125*3)
 In cell D5 you want to type Cost of the Points. Place the following cell formulas in the appropriate cells.  I have used the \$ signs in case you want to do some copying.
 Cell Formula D6 =\$C\$4*0.01 D7 =\$C\$4*0.02 D8 =\$C\$4*0.03

 Now the spreadsheet should look something like the following.

 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. Type Time Until Break Even in cell G5.  Make the text bold and change the font size to 12. Type years in cell H6. Format cells G6 ... G8 as Number with two decimal places. Type  =D6/((C\$5-C6)*12) in cell G6. 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!