How to use IPmt function in VBA?

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.

facebooklinkedin