VBA IPMT Function (Syntax + Example)

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.

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 practically understand how to use the 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 used the IPMT to get the interest amount from the payment of the first month and it returned -400 in the result when we used the 2 to get the interest from the second month it returned -394.56.

Notes

• 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.