Simple Mortgage Calculator Function | Excel, Access VBA

There are a lot of ways to calculate a mortgage payment in Excel. I honestly haven’t made any attempts to calculate a mortgage payment in Access using standard functions. There has to be a way, but for fun, I thought that I would share a simple function.

To use this function, insert it into a module. For Excel, make sure you save your Excel document as a “Macro-Enabled” workbook(.xlsm)

VBA Mortgage Calculator Function

The inputs of calcMortgage(Loan Amount, Interest(whole number: 5 for 5%), Length(in years). So for example: $480,000 mortgage at 5% over 30 years equals a Payment of $2576.74. This would be expressed in the function as: calcMortgage(480000,5,30).

Public Function calcMortgage(iPrinc As Double, iInt As Double, iLen As Integer)
    Dim iPayment As Double, j As Double, n As Integer
    'Calculate Monthly Interest
    'in Decimal Form
    j = iInt / (12 * 100)
    'Convert years to monthly periods
    n = iLen * 12
    
    'Calc Payment Amount
    iPayment = iPrinc * (j / (1 - (1 + j) ^ -n))
    
    'Return Payment Amount rounded to
    'two decimal points
    calcMortgage = Round(iPayment, 2)
End Function

As always, please comments with questions, suggestions and improvements.