Free MS Excel Training

# 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 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.

Reference
PMT function as macro in Excel