Custom Search
|
This example demonstrates the concept of Macros using one of the most popular worksheet function PMT to calculate a home mortgage loan payment. The Static statements cause Visual Basic to retain the values of the three variables; these are displayed as default values the next time you run the program. Sub PMT() Static loanAmt Static loanInt Static loanTerm 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. |
Custom Search
|