**Computing Private Mortgage
Insurance Using If( ) Functions**

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
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
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.
when this is appropriately nested in the preceding If( ) function the cell formula in B7 should be
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. |