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

 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.