Français
Presentations About Resources

Salesforce, Python, SQL, & other ways to put your data where you need it -- a bilingual blog in English & French

Door Prize: Excel VBA code for turning your ex-PMI into principal payments

13 Feb 2020 🔖 excel
💬 EN

Table of Contents

Excited to see, with Excel, when your mortgage would be free of PMI? Let’s add a little more code showing how much you’d save if you kept paying your old “PMI bill” as “extra principal” payments every month for the rest of your loan.

Inputs

We’ll use the same input numbers as last time:

  A B
3 Yearly Interest Rate 0.03625
4 Loan Duration (years) 30
5 Payments/year 12
6 Purchase Price 250000
7 Amount Borrowed 225000
8 Percent of principal at which PMI disappears 0.8

screenshot


Code to copy and paste

I’m not going to go into exactly how this works, but let’s edit our code one more time so that now Module 1 reads like this (which gives us new custom functions called GetNumberOfPaymentsMadeAfterPMIOver() and GetInterestPaidAfterPMIOver()):

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 = _
            -1 * _
            Application.WorksheetFunction.CumPrinc( _
                monthlyInterestRate, _
                totalPayments, _
                amountBorrowed, _
                1, _
                whichMonthAreWeAttempting, _
                0 _
            )
        whichMonthAreWeAttempting = whichMonthAreWeAttempting + 1
    Wend
    'COMMENT: Return output value
    GetFinalPMIMonth = whichMonthAreWeAttempting
End Function



Public Function GetNumberOfPaymentsMadeAfterPMIOver( _
    yearlyInterestRate As Double, _
    loanDurationInYears As Integer, _
    paymentsPerYear As Integer, _
    amountBorrowed As Double, _
    paymentsAlreadyMadeWhenPMIOver As Integer, _
    principalRemainingWhenPMIOver As Double, _
    monthlyExtraPrincipalPayment As Double _
) As Variant
    GetNumberOfPaymentsMadeAfterPMIOver = GetDetails( _
        yearlyInterestRate, _
        loanDurationInYears, _
        paymentsPerYear, _
        amountBorrowed, _
        paymentsAlreadyMadeWhenPMIOver, _
        principalRemainingWhenPMIOver, _
        monthlyExtraPrincipalPayment _
    )(0)
End Function



Public Function GetInterestPaidAfterPMIOver( _
    yearlyInterestRate As Double, _
    loanDurationInYears As Integer, _
    paymentsPerYear As Integer, _
    amountBorrowed As Double, _
    paymentsAlreadyMadeWhenPMIOver As Integer, _
    principalRemainingWhenPMIOver As Double, _
    monthlyExtraPrincipalPayment As Double _
) As Variant
    GetInterestPaidAfterPMIOver = GetDetails( _
        yearlyInterestRate, _
        loanDurationInYears, _
        paymentsPerYear, _
        amountBorrowed, _
        paymentsAlreadyMadeWhenPMIOver, _
        principalRemainingWhenPMIOver, _
        monthlyExtraPrincipalPayment _
    )(1)
End Function



Private Function GetDetails( _
    yearlyInterestRate As Double, _
    loanDurationInYears As Integer, _
    paymentsPerYear As Integer, _
    amountBorrowed As Double, _
    paymentsAlreadyMadeWhenPMIOver As Integer, _
    principalRemainingWhenPMIOver As Double, _
    monthlyExtraPrincipalPayment As Double _
) As Variant
    monthlyInterestRate = yearlyInterestRate / paymentsPerYear
    totalPayments = loanDurationInYears * paymentsPerYear
    principalRemaining = principalRemainingWhenPMIOver
    totalInterestPaidDuringRemainingTime = 0
    paymentsRemaining = totalPayments - paymentsAlreadyMadeWhenPMIOver
    paymentsMadeAfterPMIOver = 0
    monthlyMortgagePayment = Round(-Application.WorksheetFunction.Pmt( _
        yearlyInterestRate / paymentsPerYear, _
        loanDurationInYears * paymentsPerYear, _
        amountBorrowed _
    ), 2)
    While paymentsRemaining > 0 And principalRemaining > 0:
        positiveInterestOwedThisPayment = principalRemaining * monthlyInterestRate
        totalInterestPaidDuringRemainingTime = totalInterestPaidDuringRemainingTime + positiveInterestOwedThisPayment
        monthlyPaymentAmountAvailableTowardPrincipal = monthlyMortgagePayment - positiveInterestOwedThisPayment
        payThisPrincipal = monthlyPaymentAmountAvailableTowardPrincipal + monthlyExtraPrincipalPayment
        If principalRemaining - payThisPrincipal < 0 Then
            principalRemaining = 0
        Else
            principalRemaining = principalRemaining - payThisPrincipal
        End If
        paymentsRemaining = paymentsRemaining - 1
        paymentsMadeAfterPMIOver = paymentsMadeAfterPMIOver + 1
    Wend
    Dim resultsArray(2) As Variant
    resultsArray(0) = paymentsMadeAfterPMIOver
    resultsArray(1) = totalInterestPaidDuringRemainingTime
    GetDetails = resultsArray
End Function

Spreadsheet rows to add

Fill out A12 through B23 as follows (note that this presumes our PMI rate is 0.3%, or 0.003 – yours may be different, so adjust accordingly):

  A B
12 Yearly PMI Rate 0.003
13 Monthly PMI payment (while applicable) =(B7*B12)/B5
14 Monthly total payment while on PMI =B11+B13
15 Principal remaining after PMI paid =B7 + CUMPRINC(B3/B5, B4*B5, B7, 1, B9, 0)
16 Months remaining after PMI paid, if only pay monthly mortgage after PMI =B4*B5-B9
17 Expected interest paid to bank, long-term, if only pay monthly mortgage after PMI =-CUMIPMT(B3/B5, B4B5, B7, 1, B4B5, 0)
18 Expected total paid to bank, long-term, if only pay monthly mortgage after PMI =B7 + B17
19 Expected interest paid to bank, long-term, if keep paying elevated rate after PMI =-CUMIPMT(B3/B5, B4*B5, B7, 1, B9, 0) + GetInterestPaidAfterPMIOver(B3, B4, B5, B7, B9, B15, B13)
20 Expected total paid to bank, long-term, if keep paying elevated rate after PMI =B7 + B19
21 Savings, long-term, if keep paying elevated rate after PMI =B17 - B19
22 Months to pay off, if keep paying elevated rate after PMI =B9 + GetNumberOfPaymentsMadeAfterPMIOver(B3, B4, B5, B7, B9, B15, B13)
23 Years+months to pay off, if keep paying elevated rate after PMI =INT(B22/12) & " Years, " & MOD(B22,12) & " Months"

Results

Our final spreadsheet output looks like this now:

  A B
3 Yearly Interest Rate 0.03625
4 Loan Duration (years) 30
5 Payments/year 12
6 Purchase Price 250000
7 Amount Borrowed 225000
8 Percent of principal at which PMI disappears 0.8
9 Month number of final PMI payment 67
10 Year+month number of final payment Year 5, Month 7
11 Monthly mortgage payment $1,026.12
12 Yearly PMI Rate 0.003
13 Monthly PMI payment (while applicable) 56.25
14 Monthly total payment while on PMI $1,082.37
15 Principal remaining after PMI paid $199,316.47
16 Months remaining after PMI paid, if only pay monthly mortgage after PMI 293
17 Expected interest paid to bank, long-term, if only pay monthly mortgage after PMI $144,401.55
18 Expected total paid to bank, long-term, if only pay monthly mortgage after PMI $369,401.55
19 Expected interest paid to bank, long-term, if keep paying elevated rate after PMI $135,332.68
20 Expected total paid to bank, long-term, if keep paying elevated rate after PMI $360,332.68
21 Savings, long-term, if keep paying elevated rate after PMI $9,068.87
22 Months to pay off, if keep paying elevated rate after PMI 337
23 Years+months to pay off, if keep paying elevated rate after PMI 28 Years, 1 Months

screenshot

Although it may be hard to keep paying an extra $56.25/month on top of the $1,026.12 still owed to the bank after clearing PMI, in the long run, you save enough to buy a used car – so think about it.


Personal finance tips

  • In addition to paying down principal whenever you have extra money, also be sure to look into refinancing your mortgage if rates drop about half a percentage point or more.
  • If you refinance, don’t let yourself pay the new “balance due” from the bank – you pay the bank more in the long run because you pay interest longer into the future, having “restarted the 30-year clock.”
    • The simple way to remember this is: The longer you hold a loan, the longer you’re paying a bank interest for the privilege of using their money instead of yours, and they always make sure they charge you a pretty penny for that privilege.
  • What you want to do after a refinance is ask the bank what “monthly payment” you would need to make to pay off your mortgage in the same calendar month/year you were already on track to pay it off in.
    • If you were in “year 3” of a 30-year, ask how much you’d have to pay per month to be done in another 27 years, not 30.
    • If you were in “year 22,” ask how much you’d have to pay per month to be done in another 8 years, not 15/30.
    • If you don’t do this, the increased interest paid from paying the bank longer will more than outweigh the decreased interest paid from lowering your rate.
  • Don’t get tempted by lower rates on 15-year mortgages unless you can really afford to be locked into paying the mortgage down incredibly quickly for the next 15 years.
    • But if you were already at “year 22” of a 30-year mortgage, since you’re already planning to follow my advice to avoid “restarting the clock” and paying for any more than another 8 years, you might as well see if you can take advantage of 15-year mortgages’ good rates!
    • And remember – pay it off in 8 even though the bank technically gives you 15 – lest you pay more instead of less in the long run.
--- ---