PMT Function Using VBA

PMT Function as Macro in Excel
Calculate Equal Monthly Installments on a Loan

This example demonstrates the concept of Macros using one of the most popular worksheet function PMT to calculate the equal (equated) monthly payments or installments on a home mortgage loan payment. To make the calculations interactive we introduce the method of the ‘InputBox’. You can copy and paste the code below into your Visual Basic editor or attach it to a command button and you’ll have an EMI or monthly payments calculator!
The Macro Code:
Sub PMT()
loanAmt = Application.InputBox (Prompt:=”Loan amount (100,000 for example)”, Default:=loanAmt, Type:=1)
Range(“A1”).Value = “Loan Amount”
Range(“B1″).Value = loanAmt
loanInt = Application.InputBox (Prompt:=”Annual interest rate (8.75 for example)”, Default:=loanInt, Type:=1)
Range(“A2”).Value = “Annual Interest”
Range(“B2″).Value = loanInt
loanTerm = Application.InputBox (Prompt:=”Term in years (30 for example)”, Default:=loanTerm, Type:=1)
Range(“A3”).Value = “Loan Term”
Range(“B3”).Value = loanTerm
payment = Application.WorksheetFunction.PMT(loanInt / 1200, loanTerm * 12, loanAmt)
Range(“A4”).Value = “Payment”
Range(“B4”).Value = payment
MsgBox “Monthly payment is ” & Format(payment, “Currency”)
End Sub
The training video describes the process.

Further reading:
MS Excel: PMT Function (WS, VBA)

Leave a Reply

Your email address will not be published. Required fields are marked *