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.