Excel VBA code to compute when a mortgage will be free of PMI (Private Mortgage Insurance)

13 Feb 2020 🔖 excel
💬 EN

A friend is trying to come up with a clever way to use Excel to figure out in what month a given mortgage’s principal would have dropped below 80% of the purchase price of the home (at which point they would know longer pay a supplemental fee known as private morgtage insurance, a.k.a. PMI). I didn’t think Excel could do it, but it can.

If you took out a 30-year mortgage at 3.625%, borrowing $225,000 to pay for a $250,000 house, you pay PMI until your principal drops below 80% of $250,000 (that is, until you’ve paid $25,000 of principal).

• (Correction: looks like it’s more like until it drops below 78%. Replace that number in your mind in all my examples below.)

Make a demo custom function

I’m still not sure it can be done in “pure Excel,” but after reading this StackExchange answer, I learned that you can use the programming language built into Excel, VBA, to code your own “functions” that can be used in Excel cells.

It’s pretty slick. Let’s see how to build them and how to use them in spreadsheets.

Build a spreadsheet

First, make a new Excel file, with the number 0.03625 (that is, 3.625%) in cell B3, and save it in the “.xlsm” filetype (“Excel macro-enabled workbook”).

I saved mine at C:\\example\\Mortgage.xlsm.

Add a demo function

Next, if I don’t already have a “Developer” tab on my “ribbon,” I’ll need to customize the ribbon to add it.

Right-click in the whitespace at the far right edge of the ribbon and click “Customize the Ribbon.”

Then check the box next to “Developer” at right if it’s not checked.

Now click on the “Developer” tab of the ribbon.

Click the “Visual Basic” button at the far left side of the ribbon.

A new window will pop up. It might look a bit intimidating and programmer-oriented, but this will be easy – I promise.

Click on “ThisWorkbook” to select it. It’s under “Microsoft Excel Objects” in the “Project - VBAProject” box in the upper left corner of the screen.

Then right-click in the whitespace below it, still in the “Project - VBAProject” box, and click Insert, followed by Module

Public Function GetFinalPMIMonth(interestRate As Double) As Variant
    GetFinalPMIMonth = interestRate * 100
End Function

For now, we’ve written the code to insist that anyone who tries to use =GetFinalPMIMonth(...) puts a number (that’s what Double means) into the parentheses. Or a cell reference pointing to a cell that contains a number, anyway.

The last line of the body of our formula definition (which, right now, is the only line), is a bit special.

In repeating the name of the formula itself and putting it on the left-hand side of an equals sign (=), we’re saying that the “output” of the formula should be whatever comes to the right-hand side of the equals sign.

So far, we’re just going to multiply the interest rate by 100 as a proof of concept to demonstrate that we can actually make a custom formula and use it in our spreadsheets.

Hence interestRate * 100 to the right of the =.

Use the demo function

So … let’s give it a try.

You’ll see that as soon as you get as far as =Get, Excel is suggesting our function. Neat!

Sure enough, the value of cell B4 works out to 3.625.

Make a real custom function

Now we have to make our function a bit more complex.

Build a spreadsheet

But before we do, let’s put realistic data into our spreadsheet. We’ll fill it out cells A3 through B8 as follows and save our file:

(There’s no particular reason I start at A3, besides paranoia that I might later need room for “more stuff” at the top. Start at A1 if you’d like; just adjust your formulas accordingly later on.)

Code the real function

Now we’ll go back to our window with the code editor.

If you accidentally closed it, just click the Developer tab on the ribbon, click Visual Basic, and in the “Modules” folder of the “Project - VBAProject” panel at the top left, double-click “Module1.”

Public Function GetFinalPMIMonth( _
    yearlyInterestRate As Double, _
    loanDurationInYears As Integer, _
    paymentsPerYear As Integer, _
    purchasePrice As Double, _
    amountBorrowed As Double, _
    percentAtWhichPMIDisappears As Double _
) As Variant
    'COMMENT: Set up 4 helper variables
    monthlyInterestRate = yearlyInterestRate / paymentsPerYear
    totalPayments = loanDurationInYears * paymentsPerYear
    principalBalanceAtWhichPMIDisappears = purchasePrice * percentAtWhichPMIDisappears
    principalToPayToEliminatePMI = amountBorrowed - principalBalanceAtWhichPMIDisappears
    'COMMENT: Set up loop variables
    whichMonthAreWeAttempting = 1
    principalPaidByEndOfAttemptedMonth = 0
    'COMMENT: Do loop
    While whichMonthAreWeAttempting <= totalPayments And principalPaidByEndOfAttemptedMonth < principalToPayToEliminatePMI
        principalPaidByEndOfAttemptedMonth = -Application.WorksheetFunction.CumPrinc( _
            monthlyInterestRate, _
            totalPayments, _
            amountBorrowed, _
            1, _
            whichMonthAreWeAttempting, _
            0 _
        )
        whichMonthAreWeAttempting = whichMonthAreWeAttempting + 1
    Wend
    'COMMENT: Return output value
    GetFinalPMIMonth = whichMonthAreWeAttempting
End Function

As you can see, our outermost structure still starts with Public function GetFinalPMIMonth( and ends with EndFunction.

Use the real function

The value of cell B9 works out to 67 months, or about 5 and a half years, just like in my previous example!

Does it work? Hooray!

With B3 set back to 0.03625, our final spreadsheet output looks like this now:

Further refinements

If you’d like one more treat for readability, give yourself an additional formula in B10 that reads ="Year " & INT(B9/12) & ", Month " & MOD(B9,12)

One more thing that might be nice is a quick reference about the monthly mortgage payment we owe the bank in B11: =PMT(B3/B5, B4*B5, -B7) (works out to $1,026.12)

Door prize - bonus custom functions

See how much money you would save if you continued to pay your former PMI bill alongside your monthly payment as “extra principal.”

You’l get bonus code for custom functions, plus you’ll see Excel’s CUMIPMT function in action.

Sneak peek:

Explanation of the code

As promised, for people who like to truly understand what they’re doing, here’s how the VBA I gave you to copy and paste works.

Line breaks

You might note that there are a lot of mysterious places where I ended a line with a single space and an underscore: “ _”

The reason for this is that VBA is a line-break-dependent language. You put one command on each line.

If you are typing a command that is so long you’d have to scroll left and right to read it, you can clarify that adjacent lines should be treated as if they were part of the same line by ending a line with a single space and an underscore.

That’s all that’s going on there.

Input parameters

Note that the number of “input parameters” we’ve written our code to insist users enter got longer. Instead of just 1 called interestRate, we now have 6:

1. yearlyInterestRate, a number (e.g. “0.03625” as in “3.625%”).
2. loanDurationInYears, an integer
3. paymentsPerYear, an integer
4. purchasePrice, a number
5. amountBorrowed, a number
6. percentAtWhichPMIDisappears, a number (e.g. “0.8” as in “80%”)

Helper variables

The meat of our code is in the middle, between As Variant and GetFinalPMIMonth = whichMonthAreWeAttempting.

First, store fixed values into 4 nicknames, or “variable names,” that will be handy in our calculations:

1. monthlyInterestRate
   • We’ll set it to the user-provided value of yearlyInterestRate divided by the user-provided value of paymentsPerYear
   • Here, that’s 0.03625 / 12, or 0.0030208333...
2. totalPayments
   • We’ll set it to the user-provided value of loanDurationInYears multiplied by the user-provided value of paymentsPerYear
   • Here, that’s 360
3. principalBalanceAtWhichPMIDisappears
   • We’ll set it to the user-provided value of purchasePrice multiplied by the user-provided value of percentAtWhichPMIDisappears
   • Here, that’s 250,000 * 0.8, or 200,000
4. principalToPayToEliminatePMI
   • We’ll set it to the value we just computed for principalBalanceAtWhichPMIDisappears subtracted from the user-provided value of amountBorrowed
   • Here, that’s 225,000 - 200,000, or 25,000

Loop until you get an answer

Next, we set up two more nicknames, or “variable names,” which we will use as scratch-pads, constantly changing their values as we check to see “if we’re there yet”:

1. whichMonthAreWeAttempting
   • We’ll start it out saying we’d like to see how much principal we’ll have paid the bank after we finish paying month number 1
   • Sneak preview: We’ll constantly increase it by 1 until we’re happy.
2. principalPaidByEndOfAttemptedMonth
   • We’ll start it out saying we haven’t yet paid the bank any principal at all (0)
   • Sneak preview: We’ll constantly overwrite principalPaidByEndOfAttemptedMonth with the output from =CUMPRINC(0.03625/12, 360, 225000, 1, principalPaidByEndOfAttemptedMonth, 0) until we’re happy.

Math

I mentioned in my previous post on the subject that mathematically, we’re dealing with a bit of a “calculus problem” – one that involves computing an unknown number based off of known numbers even though “things are changing”.

After all, if you’ve ever looked at an amortization table for a mortgage, you’ve seen that the principal paid is different from one month to the next.

As I recently heard on the radio:

• Algebra is for answering, “If your car is already going 30 miles per hour and holding steady, in how many seconds will you have gone 30 feet?”
  • (0.68 seconds)
• Calculus is for answering, “If your car can accelerate ‘zero miles per hour to sixty miles per hour in 10 seconds,’ in how many seconds will you have gone 30 feet if you ‘floor it’ from a dead stop?”
  • (2.611 seconds)

Cheating

Normally, it’d take a bit more math to calculate the second answer than the first.

But we have an advantage – we don’t pay mortgages in a continuous flow the way a car accelerates. We pay them in monthly lump-sums.

That means we can cheat and avoid a lot of math.

That’s what we’re doing to do with our code here.

1. We’re going to try Excel’s CUMPRINC formula as =CUMPRINC(0.03625/12, 360, 225000, 1, 1, 0) and see whether its value is greater than, or equal to, 25,000.
   • Note that we have to put a minus sign (-) in front of it because CUMPRINC produces a negative number, and we want a positive number.
2. If not, we’ll try the same thing with =CUMPRINC(0.03625/12, 360, 225000, 1, 2, 0)
3. If not, we’ll try the same thing with =CUMPRINC(0.03625/12, 360, 225000, 1, 3, 0)
4. If not, we’ll try the same thing with =CUMPRINC(0.03625/12, 360, 225000, 1, 4, 0)
5. And so on and so forth, quitting as soon as the value is >= $25,000.

When we get there, we’ll check which number we just punched into the second-to-last parameter of CUMPRINC.

If you’re following along in the code, we’ve been keeping track of this number under the variable name whichMonthAreWeAttempting.

Output value

The value of whichMonthAreWeAttempting when we exit our loop (bounded by While and Wend) is our “magic month” – the final month in which we’ll owe the bank private mortgage insurance on top of our ordinary mortgage payments.

That’s what we want as output for the function, so the final line of the custom function is GetFinalPMIMonth = whichMonthAreWeAttempting.