Payment Function

Many times you will need to calculate the resulting payment, given the principal, interest rate, and term of a loan. This can be easily calculated in VBA by using the Pmt() function.

The syntax of this function is as follows:

Pmt (interest, term, principal, future value, type)

The future value and type are optional arguments. You would use these if you were calculating the future value of a stream of payments and if you needed to decide whether the payment was at the beginning or end of a period. The default is the beginning of the period.

In the following example, I have hard-coded the values into the procedure. However, most times, these numbers would be retrieved from an outside source, such as an input box or a database.

Sub loanPayment()
   Dim dblRate As Double
   Dim intTerm As Integer
   Dim dblPrincipal As Double
   Dim dblPayment As Double

   dblRate = 0.075
   intTerm = 5
   dblPrincipal = 75000

   dblPayment = Pmt(dblRate / 12, intTerm * 12, -dblPrincipal)

   MsgBox "The monthly payment is: " & dblPayment

End Sub

Notice that in this example, we had to convert the rate into a monthly rate by dividing by 12, and we had to convert the term to a monthly term by multiplying by 12. In addition, the principal must be entered as a negative number (this is standard mathematical practice).

Here is the result:

Notice that the output is not really formatted in proper currency format. You could fix that by using the Format function and making the following changes to the code:

Sub loanPayment()
   Dim dblRate As Double
   Dim intTerm As Integer
   Dim dblPrincipal As Double
   Dim dblPayment As Double
   Dim strFormat As String

   dblRate = 0.075
   intTerm = 5
   dblPrincipal = 75000
   strFormat = "$###.##"

   dblPayment = Pmt(dblRate / 12, intTerm * 12, -dblPrincipal)

   MsgBox "The monthly payment is: " & Format(dblPayment, strFormat)

End Sub

The Format function allows you to predefine a format; in this case, the pound symbol in the string that is assigned to the strFormat variable represents a placeholder. Because it is a string, you can also add whatever characters you want. In this case, the dollar sign was added.

You then use the Format function to connect the created format to the number you want to format in the MsgBox line. The Format takes two arguments: the first is the number to be formatted, the second is the variable containing the created format.

Here is the new result:

Notice that the format has the added advantage of rounding the number properly at the second decimal place.