More on Mortgages
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 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.
=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.
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.
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
These will be used to recalculate the mortgage amounts across the top row of the comparisons. You should make the following modifications.
This takes care of the inputs and formatting. Now you want to modify how the mortgage amount is obtained.

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
We can modify this relationship later. At this point we are going to modify our spreadsheet quite a bit to reflect these new considerations.
Now we need to have a place to input the interest rate.

Cell  Formula 
B5  =$B$21 
B6  =$B$21  (0.00125*1) 
B7  =$B$21  (0.00125*2) 
B8  =$B$21  (0.00125*3) 

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
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.
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! 