VBA PMT Function

HomeVBA Functions LIST (Category Wise)How to use the VBA PMT Function (Syntax + Example)

What is VBA PMT Function

The VBA PMT function is listed under the financial category of VBA functions. When you use it in a VBA code, it calculates the periodic payments of the loan or an investment that are constant throughout the entire period with a constant rate of interest.

how to use it

To use VBA's PMT function you need to understand its syntax and arguments:

Syntax

Pmt(Rate, Nper, Pv, [Fv], [Due])

Arguments

  • Rate: The rate of interest for the period.
  • Nper: The number of periods to pay the loan or investment.
  • PV: The present value of investment or loan.
  • [FV]: The future value of the loan/investment [This is an optional argument and if omitted VBA takes 0 by default].
  • [Due]: Defines whether the payment is due at the start or the end of the period, use 0 for the end of the period and 1 for the start of the period [This is an optional argument and if omitted VBA takes the end of the period by default].

Example to use PMT Function in VBA

To practically understand how to use VBA PMT function, you need to go through the below example where we have written a vba code by using it:

Sub example_PMT()
Range("A9").Value = Pmt(0.08 / 12, 5 * 12, 98000)
End Sub

In the above code, we have used PMT to calculate the constant periodic payment and in the result, it has returned 1987.09, which we need to pay. We have divided the rate of interest with 12 to convert into the monthly rate and multiply the years with 12 to convert them into months.

Notes

Below are some important points which you need to take care while using PMT function in VBA.

  • If the nper is equal to zero then VBA will return a run-time error 5.

Related Functions

About the Author

puneet one point one

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.