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 of several.

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.

  1. Insert a new spreadsheet after all of the other spreadsheets we have worked with so far.  If necessary, drag it into the last spreadsheet position by dragging the name tag.

  2. Right click the name tab and select Rename on the popup menu.  Call it PMI.

  3. You may want to format the block of cells A1 ... B10 with some particular font and font properties.

  4. Type Computing the PMI on the Mortgage in cell A1.  Make it bold.

  5. Select cells A1 ... B1.  Format the cells to center across the selection.

  6. Type Mortgage Amount in cell A3.

  7. Type Down Payment in cell A5.

  8. Type PMI Monthly Payment in cell A7.

  9. Type 150000 in cell B3.

  10. Format cell B3 to be a number with 0 decimal places and use the thousands separator.  Make sure that column B is reasonably wide to hold the numbers it contains.

  11. Format cell B5 to be a percentage with 2 decimal places.

  12. Type .10 in cell B5.

  13. Format cell B7 as currency with 2 decimal places.

  14. In cell B7 type

=IF(B5<0.05,"You need to put more money down","More work is coming")

  1. Make sure to widen column B enough so that everything will fit.  I needed to use 32.00.

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.

=IF(B5<0.05,"You need to put more money down",IF(And(0.05<=B5,B5<0.10), 0.0078*B3/12,If(And(0.10<=B5,B5<0.15), 0.0052*B3/12,If(And(0.15<=B5,B5<0.20), 0.0032*B3/12,If(0.20<=B5,"PMI is unnecessary","")))))

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.