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. 
