VBA IPMT Function

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

What is VBA IPMT Function

The VBA IPMT function is listed under the financial category of VBA functions. When you use it in a VBA code, it calculates the interest of the payment, during a specific period for a loan or investment that is paid by constant periodic payments and using a constant interest rate.

how to use it

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

Syntax

IPmt(Rate, Per, Nper, Pv, [Fv], [Due])

Arguments

  • Rate: The rate of interest for the period.
  • Per: The number of the periods for which you want to calculate the interest [It must be within 1 to Nper].
  • Nper: The number of periods over which the loan or investment is to be paid.
  • 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 [This is an optional argument and if omitted VBA takes the end of the period by default].
    • Use 0 for the end of the period and 1 for the start of the period

Example to use IPMT Function in VBA

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

Sub example_IPMT()
Range("A10") = IPmt(0.08 / 12, 1, 5 * 12, 60000)
End Sub

In the above code, we have used the IPMT to get the interest amount from the payment of the first month and it has returned -400 in the result and when we have used the 2 to get the interest from the second month it has returned -394.56.

Notes

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

  • While using IPMT any cash paid out is represented by a negative number and any cash received is represented by a positive number.
  • If we supply Per which is 0 > or > NPer then VBA will return a run-time 5 error.

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.