Contents
The IPmt function in VBA (Visual Basic for Applications) is used to calculate the interest payment for a given period for an investment or loan based on periodic, constant payments and a constant interest rate. It’s part of the financial functions provided within the VBA environment.
Basic syntax
VBA
IPmt(Rate, Per, NPer, PV, [FV], [Type])
- Rate is the interest rate per period.
- Per is the period for which you want to find the interest and must be in the range 1 to NPer.
- NPer is the total number of payment periods in the investment.
- PV is the present value, or the total amount that a series of future payments is worth now; also known as the principal.
- FV is optional. It is the future value, or a cash balance you want to attain after the last payment is made. If omitted, it is assumed to be 0 (zero), which means the loan is to be paid off.
- Type is optional. It indicates when payments are due: 0 or omitted means payments are due at the end of the period; 1 means payments are due at the beginning of the period.
Example
VBA
Sub CalculateInterestPayment()
Dim InterestRate As Double
Dim Period As Integer
Dim TotalPeriods As Integer
Dim PresentValue As Double
Dim InterestPayment As Double
' Example loan details
InterestRate = 0.05 ' Annual interest rate of 5%
Period = 1 ' Calculate interest for the first period
TotalPeriods = 12 ' Total of 12 monthly payments
PresentValue = -1000 ' The present value of the loan (negative because it's an outflow)
' Calculate the interest payment using the IPmt function
InterestPayment = IPmt(InterestRate / 12, Period, TotalPeriods, PresentValue)
' Print the result to the Immediate Window (press Ctrl+G to view the Immediate Window in the VBA editor)
Debug.Print "Interest Payment for Period 1: " & InterestPayment
End Sub
Note that InterestRate is divided by 12 because InterestRate is assumed to be an annual rate, but payments are monthly. Also, PresentValue is entered as a negative number since it’s an outflow of funds when you take out a loan.
Run this subroutine to calculate the interest payment for the first period and output the result to the Immediate Window. Adjust the parameters as needed for your specific scenario.