Computing Private Mortgage Insurance Using If( ) Functions
Introduction. Whenever someone takes out a loan, particularly for something as large as a mortgage, the lender assumes some risk that the loan will not be repaid. Obviously, they require the person trying to get the mortgage to give considerable evidence about their ability to repay, but this is often not enough. One of the major considerations associated with mortgages is the down payment percentage. If this down payment percentage isn't large enough then the borrower is required to purchase PMI, Private Mortgage Insurance. This insurance backs up the lender.
PMI is setup so that paying it amounts to adding a relatively small amount to each monthly payment. The following table summarizes the determination of the PMI payment. Each row is determined by the down payment percentage.
|Down Payment Range||Monthly Payment for PMI|
|5% < DP < 10%||.0078(Mortgage Amount)/12|
|10% < DP < 15%||.0052(Mortgage Amount)/12|
|15% < DP < 20%||.0032(Mortgage Amount)/12|
|20% < DP||Don't need PMI|
|Obviously there are some advantages to putting down 20%
of a mortgage amount. The avoidance of the PMI payment is just one
The next thing we need to consider is how we can set up a spreadsheet to help us determine the PMI monthly payment. Obviously it is important to consider the down payment percentage. Unfortunately, when working with cell formulas we do not have a Select . . . Case statement like we do in VisualBasic. We need to make use of some sort of conditional criteria in order to compute the PMI payment based on the down payment percentage.
The If( ) Function. We have already made use of the PMT( ) function to compute the monthly payments on mortgages. There also exists a function that will compute different values based on some criteria. The function is identified by If( ).
If( criteria to be evaluated, value_if_ true, value_if_false )
This cell function can be used in a large number of ways. We will first use it in a relatively simple way and then we will elaborate it by using nested functions. You should open up the MortgageBasics.xls workbook that we have been using and work through the following steps. We are going to use a new spreadsheet in the workbook to make our work slightly easier at this point.
=IF(B5<0.05,"You need to put more money down","More work is coming")
Now your spreadsheet should look like the following.
|So we have barely started on computing these PMI
payments. At present we have only considered the case to make sure
they are planning to put at least 5% down on the mortgage. If they
have put down at least 5% then we need to nest another If( ) function
within the value_if_false. We also need to make use of the fact
that we must be at 0.05 and not more than 0.10 so that we need to also
use the And( ) function in our
criterion. This function will look like the following.
=If(AND(0.05<=B5,B5<0.10), 0.0078*B3/12,"More work is coming")
when this is appropriately nested in the preceding If( ) function the cell formula in B7 should be
=IF(B5<0.05,"You need to put more money down",IF(AND(0.05<=B5,B5<0.10), 0.0078*B3/12,"More work is coming"))
This will compute the appropriate PMI monthly payment when the down payment is at least 5%, but less than 10%. We need to iterate this process so that we nest the following If( ) functions for each range.
|Down Payment Range||If( ) Function for Monthly Payment for PMI or this Range|
|5% < DP < 10%||=If(And(0.05<=B5,B5<0.10), 0.0078*B3/12,"More work is coming")|
|10% < DP < 15%||=If(And(0.10<=B5,B5<0.15), 0.0052*B3/12,"More work is coming")|
|15% < DP < 20%||=If(And(0.15<=B5,B5<0.20), 0.0032*B3/12,"More work is coming")|
|20% < DP||=If(0.20<=B5,"PMI is unnecessary","")|
|Nesting these If( ) functions appropriately will give
the following If( ) function or cell B7.
You should copy this cell formula into cell B7 and see how it works. The following is a section of the spreadsheet after copying this formula.
|We have just seen a very complicated, yet very realistic, use of the If( ) cell function. It is easy to see how it would be much cleaner to make use of VisualBasic and use a Select . . . Case sort of construct. Even using a nested set of If . . . Then . . . Else statements would be likely to be easier to debug than such a complicated cell formula.|