Computing Private Mortgage Insurance Using a Select Case Statement

 

Introduction.  As you might remember from earlier in the semester, 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.  Having introduced some basic Visual Basic we can now create our own user-defined function based on the  a Select . . . Case statement in Visual Basic.  

The Select Case Statement.  The general syntax of a Select Case statement is

Select Case (expression)

Case expression_value1

statement(s)

Case expression_value2

statement(s)

. . .

Case Else

statement(s)

End Select

So a test expression is evaluated and then depending on the value that it evaluates at, different cases will be selected.  There can be some trickery involved in setting up the expression and specifying the case, but hte overall concept should make sense.

The =PMI( ) User Defined Function.  Now you should go back into your MortgageBasics.xls workbook and open a new module.  You are most likely to want to save this to your Personal macro workbook.  Notice that the computation for the PMI depends on both the down payment percentage and the mortgage amount.  You want to insert a new module in your Personal.xls and name it MortgageFunctions.

The down payment percentage alone determines the rate of payment.  Thus the following function will work.

Function PMI(DownPaymentPercentage, MortgageAmount) As Single

Select Case DownPaymentPercentage


Case Is < 0.05

MsgBox "You need to put at least 5% down", vbCritical, "Inadequate Down Payment"

Case 0.05 To 0.09999

PMI = 0.0078 * MortgageAmount / 12

Case 0.1 To 0.14999

PMI = 0.0052 * MortgageAmount / 12

Case 0.15 To 0.19999

PMI = 0.0032 * MortgageAmount / 12

Case Else

PMI = 0

End Select

End Function

See  how much easier this is than the nested If functions???