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

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