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.
great info! thanks for posting