How to use the VBA IPMT Function (Syntax + Example)

HomeVBA Functions LIST (Category Wise)How to use the 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 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

  • 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

DDB | FV | IRR | MIRR | NPER | NPV | PMT | PV | RATE | SLN | SYD