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.
IPmt(Rate, Per, Nper, Pv, [Fv], [Due])
- 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
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.
- 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.