IPmt Function

Returns a Double specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

IPmt(rate, per, nper, pv[, fv[, type]])

Parameters

• rate (Required): Double specifying interest rate per period. For example, if you get a car loan at an annual percentage rate (APR) of 10 percent and make monthly payments, the rate per period is 0.1/12, or 0.0083
• per (Required): Double specifying payment period in the range 1 through nper
• nper (Required): Double specifying total number of payment periods in the annuity. For example, if you make monthly payments on a four-year car loan, your loan has 4 * 12 (or 48) payment periods
• pv (Required): Double specifying present value, or value today, of a series of future payments or receipts. For example, when you borrow money to buy a car, the loan amount is the present value to the lender of the monthly car payments you will make
• fv (Optional): Variant specifying future value or cash balance you want after you've made the final payment. For example, the future value of a loan is $0 because that's its value after the final payment. However, if you want to save $50,000 over 18 years for your child's education, then $50,000 is the future value. If omitted, 0 is assumed
• type (Optional): Variant specifying when payments are due. Use 0 if payments are due at the end of the payment period, or use 1 if payments are due at the beginning of the period. If omitted, 0 is assumed

Return Value

Returns a Double representing the interest payment for the specified period: - Negative value indicates money paid out (such as loan interest payments) - Positive value indicates money received (such as investment interest earnings) - The sum of interest payments (IPmt) and principal payments (PPmt) equals the total payment for a period

Remarks

An annuity is a series of fixed cash payments made over a period of time: - Used to calculate the interest portion of a specific payment - Commonly used for loan amortization calculations - Interest payment decreases over the life of a loan (more principal paid later) - The per argument must be in the range 1 through nper - rate and nper must be calculated using payment periods in the same units - For monthly payments, divide annual rate by 12, multiply years by 12 - For quarterly payments, divide annual rate by 4, multiply years by 4 - All arguments referring to cash paid out are negative; cash received is positive - The interest payment varies by period (unlike fixed total payment) - Use PPmt to calculate the principal portion of a payment - Use Pmt to calculate the total payment amount

Typical Uses

1. Loan Amortization: Calculate interest portion of loan payments
2. Mortgage Analysis: Determine interest paid per payment period
3. Investment Analysis: Calculate interest earned per period
4. Financial Planning: Project interest expenses over time
5. Tax Calculations: Determine deductible interest for tax purposes
6. Budget Planning: Forecast interest costs
7. Refinancing Analysis: Compare interest costs between loans
8. Amortization Schedules: Build payment schedules showing interest breakdown

Basic Usage Examples

' Example 1: Calculate first month's interest on a car loan
Dim monthlyRate As Double
Dim totalMonths As Integer
Dim loanAmount As Double
Dim interestPayment As Double
monthlyRate = 0.08 / 12  ' 8% annual rate
totalMonths = 48         ' 4-year loan
loanAmount = -20000      ' $20,000 borrowed (negative = cash received)
interestPayment = IPmt(monthlyRate, 1, totalMonths, loanAmount)
Debug.Print "First month interest: " & Format$(interestPayment, "Currency")
' Prints approximately: -$133.33
' Example 2: Calculate last payment's interest
interestPayment = IPmt(monthlyRate, 48, totalMonths, loanAmount)
Debug.Print "Last month interest: " & Format$(interestPayment, "Currency")
' Prints approximately: -$3.26 (much less than first month)
' Example 3: Calculate total interest paid in first year
Dim totalInterest As Double
Dim i As Integer
totalInterest = 0
For i = 1 To 12
    totalInterest = totalInterest + IPmt(monthlyRate, i, totalMonths, loanAmount)
Next i
Debug.Print "First year interest: " & Format$(totalInterest, "Currency")
' Example 4: Monthly mortgage interest payment
Dim mortgageRate As Double
Dim mortgageMonths As Integer
Dim mortgageAmount As Double
mortgageRate = 0.06 / 12  ' 6% annual rate
mortgageMonths = 30 * 12  ' 30-year mortgage
mortgageAmount = -200000  ' $200,000 loan
interestPayment = IPmt(mortgageRate, 1, mortgageMonths, mortgageAmount)
Debug.Print "First mortgage payment interest: " & Format$(interestPayment, "Currency")

Common Patterns

' Pattern 1: Calculate interest for specific payment
Function CalculateInterestPayment(loanAmount As Double, annualRate As Double, _
                                  years As Integer, paymentNumber As Integer) As Double
    Dim monthlyRate As Double
    Dim totalPayments As Integer
    monthlyRate = annualRate / 12
    totalPayments = years * 12
    CalculateInterestPayment = IPmt(monthlyRate, paymentNumber, totalPayments, -loanAmount)
End Function
' Pattern 2: Calculate total interest for a year
Function CalculateAnnualInterest(loanAmount As Double, annualRate As Double, _
                                 years As Integer, year As Integer) As Double
    Dim monthlyRate As Double
    Dim totalPayments As Integer
    Dim startMonth As Integer
    Dim endMonth As Integer
    Dim totalInterest As Double
    Dim i As Integer
    monthlyRate = annualRate / 12
    totalPayments = years * 12
    startMonth = (year - 1) * 12 + 1
    endMonth = year * 12
    totalInterest = 0
    For i = startMonth To endMonth
        If i <= totalPayments Then
            totalInterest = totalInterest + IPmt(monthlyRate, i, totalPayments, -loanAmount)
        End If
    Next i
    CalculateAnnualInterest = totalInterest
End Function
' Pattern 3: Generate amortization schedule entry
Type AmortizationEntry
    PaymentNumber As Integer
    TotalPayment As Double
    InterestPayment As Double
    PrincipalPayment As Double
    Balance As Double
End Type
Function GetAmortizationEntry(loanAmount As Double, annualRate As Double, _
                              years As Integer, paymentNumber As Integer) As AmortizationEntry
    Dim monthlyRate As Double
    Dim totalPayments As Integer
    Dim entry As AmortizationEntry
    monthlyRate = annualRate / 12
    totalPayments = years * 12
    entry.PaymentNumber = paymentNumber
    entry.TotalPayment = Pmt(monthlyRate, totalPayments, -loanAmount)
    entry.InterestPayment = IPmt(monthlyRate, paymentNumber, totalPayments, -loanAmount)
    entry.PrincipalPayment = PPmt(monthlyRate, paymentNumber, totalPayments, -loanAmount)
    GetAmortizationEntry = entry
End Function
' Pattern 4: Compare interest costs between loans
Function CompareInterestCosts(loan1Amount As Double, loan1Rate As Double, loan1Years As Integer, _
                              loan2Amount As Double, loan2Rate As Double, loan2Years As Integer) As String
    Dim loan1Interest As Double
    Dim loan2Interest As Double
    Dim i As Integer
    ' Calculate total interest for loan 1
    For i = 1 To loan1Years * 12
        loan1Interest = loan1Interest + IPmt(loan1Rate / 12, i, loan1Years * 12, -loan1Amount)
    Next i
    ' Calculate total interest for loan 2
    For i = 1 To loan2Years * 12
        loan2Interest = loan2Interest + IPmt(loan2Rate / 12, i, loan2Years * 12, -loan2Amount)
    Next i
    CompareInterestCosts = "Loan 1 total interest: " & Format$(loan1Interest, "Currency") & vbCrLf & _
                          "Loan 2 total interest: " & Format$(loan2Interest, "Currency")
End Function
' Pattern 5: Calculate deductible interest for tax year
Function CalculateTaxDeductibleInterest(loanAmount As Double, annualRate As Double, _
                                       totalYears As Integer, taxYear As Integer) As Double
    ' Assumes loan started at beginning of year 1
    CalculateTaxDeductibleInterest = CalculateAnnualInterest(loanAmount, annualRate, totalYears, taxYear)
End Function
' Pattern 6: Determine when interest drops below threshold
Function FindPaymentWhenInterestBelow(loanAmount As Double, annualRate As Double, _
                                      years As Integer, threshold As Double) As Integer
    Dim monthlyRate As Double
    Dim totalPayments As Integer
    Dim i As Integer
    Dim interestPayment As Double
    monthlyRate = annualRate / 12
    totalPayments = years * 12
    For i = 1 To totalPayments
        interestPayment = Abs(IPmt(monthlyRate, i, totalPayments, -loanAmount))
        If interestPayment < threshold Then
            FindPaymentWhenInterestBelow = i
            Exit Function
        End If
    Next i
    FindPaymentWhenInterestBelow = -1  ' Never drops below threshold
End Function
' Pattern 7: Calculate average monthly interest
Function CalculateAverageMonthlyInterest(loanAmount As Double, annualRate As Double, _
                                         years As Integer) As Double
    Dim totalInterest As Double
    Dim totalPayments As Integer
    Dim i As Integer
    totalPayments = years * 12
    For i = 1 To totalPayments
        totalInterest = totalInterest + IPmt(annualRate / 12, i, totalPayments, -loanAmount)
    Next i
    CalculateAverageMonthlyInterest = totalInterest / totalPayments
End Function
' Pattern 8: Interest payment with balloon payment
Function CalculateInterestWithBalloon(loanAmount As Double, annualRate As Double, _
                                      years As Integer, paymentNumber As Integer, _
                                      balloonAmount As Double) As Double
    Dim monthlyRate As Double
    Dim totalPayments As Integer
    monthlyRate = annualRate / 12
    totalPayments = years * 12
    CalculateInterestWithBalloon = IPmt(monthlyRate, paymentNumber, totalPayments, _
                                        -loanAmount, -balloonAmount)
End Function
' Pattern 9: Interest for payment due at beginning
Function CalculateInterestPaymentBeginning(loanAmount As Double, annualRate As Double, _
                                           years As Integer, paymentNumber As Integer) As Double
    Dim monthlyRate As Double
    Dim totalPayments As Integer
    monthlyRate = annualRate / 12
    totalPayments = years * 12
    ' Type = 1 means payment at beginning of period
    CalculateInterestPaymentBeginning = IPmt(monthlyRate, paymentNumber, totalPayments, _
                                             -loanAmount, 0, 1)
End Function
' Pattern 10: Validate interest payment calculation
Function ValidateInterestPayment(loanAmount As Double, annualRate As Double, _
                                 years As Integer, paymentNumber As Integer) As Boolean
    Dim totalPayment As Double
    Dim interestPayment As Double
    Dim principalPayment As Double
    Dim monthlyRate As Double
    Dim totalPayments As Integer
    monthlyRate = annualRate / 12
    totalPayments = years * 12
    totalPayment = Pmt(monthlyRate, totalPayments, -loanAmount)
    interestPayment = IPmt(monthlyRate, paymentNumber, totalPayments, -loanAmount)
    principalPayment = PPmt(monthlyRate, paymentNumber, totalPayments, -loanAmount)
    ' Verify that interest + principal = total payment (within rounding tolerance)
    ValidateInterestPayment = (Abs(totalPayment - (interestPayment + principalPayment)) < 0.01)
End Function

Advanced Usage Examples

' Example 1: Complete amortization schedule generator
Public Class AmortizationSchedule
    Private m_loanAmount As Double
    Private m_annualRate As Double
    Private m_years As Integer
    Private m_schedule As Collection
    Public Sub Initialize(loanAmount As Double, annualRate As Double, years As Integer)
        m_loanAmount = loanAmount
        m_annualRate = annualRate
        m_years = years
        GenerateSchedule
    End Sub
    Private Sub GenerateSchedule()
        Dim monthlyRate As Double
        Dim totalPayments As Integer
        Dim i As Integer
        Dim entry As AmortizationEntry
        Dim balance As Double
        Set m_schedule = New Collection
        monthlyRate = m_annualRate / 12
        totalPayments = m_years * 12
        balance = m_loanAmount
        For i = 1 To totalPayments
            entry.PaymentNumber = i
            entry.TotalPayment = Pmt(monthlyRate, totalPayments, -m_loanAmount)
            entry.InterestPayment = IPmt(monthlyRate, i, totalPayments, -m_loanAmount)
            entry.PrincipalPayment = PPmt(monthlyRate, i, totalPayments, -m_loanAmount)
            balance = balance - entry.PrincipalPayment
            entry.Balance = balance
            m_schedule.Add entry
        Next i
    End Sub
    Public Function GetPayment(paymentNumber As Integer) As AmortizationEntry
        If paymentNumber >= 1 And paymentNumber <= m_schedule.Count Then
            GetPayment = m_schedule(paymentNumber)
        End If
    End Function
    Public Function GetTotalInterest() As Double
        Dim i As Integer
        Dim total As Double
        Dim entry As AmortizationEntry
        For i = 1 To m_schedule.Count
            entry = m_schedule(i)
            total = total + entry.InterestPayment
        Next i
        GetTotalInterest = total
    End Function
End Class
' Example 2: Loan comparison calculator
Public Class LoanComparer
    Public Function CompareLoanOptions(loanAmount As Double) As String
        Dim result As String
        Dim option1Interest As Double
        Dim option2Interest As Double
        Dim option3Interest As Double
        Dim i As Integer
        result = "Loan Amount: " & Format$(loanAmount, "Currency") & vbCrLf & vbCrLf
        ' Option 1: 15-year at 5.5%
        For i = 1 To 15 * 12
            option1Interest = option1Interest + IPmt(0.055 / 12, i, 15 * 12, -loanAmount)
        Next i
        result = result & "15-year at 5.5%: " & Format$(option1Interest, "Currency") & vbCrLf
        ' Option 2: 20-year at 6.0%
        For i = 1 To 20 * 12
            option2Interest = option2Interest + IPmt(0.06 / 12, i, 20 * 12, -loanAmount)
        Next i
        result = result & "20-year at 6.0%: " & Format$(option2Interest, "Currency") & vbCrLf
        ' Option 3: 30-year at 6.5%
        For i = 1 To 30 * 12
            option3Interest = option3Interest + IPmt(0.065 / 12, i, 30 * 12, -loanAmount)
        Next i
        result = result & "30-year at 6.5%: " & Format$(option3Interest, "Currency")
        CompareLoanOptions = result
    End Function
End Class
' Example 3: Interest payment tracker
Public Class InterestTracker
    Private m_loanAmount As Double
    Private m_annualRate As Double
    Private m_years As Integer
    Private m_currentPayment As Integer
    Public Sub Initialize(loanAmount As Double, annualRate As Double, years As Integer)
        m_loanAmount = loanAmount
        m_annualRate = annualRate
        m_years = years
        m_currentPayment = 0
    End Sub
    Public Function GetNextInterestPayment() As Double
        m_currentPayment = m_currentPayment + 1
        If m_currentPayment <= m_years * 12 Then
            GetNextInterestPayment = IPmt(m_annualRate / 12, m_currentPayment, _
                                         m_years * 12, -m_loanAmount)
        Else
            GetNextInterestPayment = 0
        End If
    End Function
    Public Function GetInterestForPayment(paymentNumber As Integer) As Double
        If paymentNumber >= 1 And paymentNumber <= m_years * 12 Then
            GetInterestForPayment = IPmt(m_annualRate / 12, paymentNumber, _
                                        m_years * 12, -m_loanAmount)
        Else
            GetInterestForPayment = 0
        End If
    End Function
    Public Function GetYearToDateInterest() As Double
        Dim currentYear As Integer
        Dim startMonth As Integer
        Dim endMonth As Integer
        Dim total As Double
        Dim i As Integer
        currentYear = Int((m_currentPayment - 1) / 12) + 1
        startMonth = (currentYear - 1) * 12 + 1
        endMonth = m_currentPayment
        For i = startMonth To endMonth
            total = total + IPmt(m_annualRate / 12, i, m_years * 12, -m_loanAmount)
        Next i
        GetYearToDateInterest = total
    End Function
End Class
' Example 4: Refinancing analyzer
Function AnalyzeRefinancing(currentLoanBalance As Double, currentRate As Double, _
                           remainingYears As Integer, newRate As Double, _
                           newYears As Integer, closingCosts As Double) As String
    Dim currentTotalInterest As Double
    Dim newTotalInterest As Double
    Dim i As Integer
    Dim result As String
    ' Calculate remaining interest on current loan
    For i = 1 To remainingYears * 12
        currentTotalInterest = currentTotalInterest + _
            IPmt(currentRate / 12, i, remainingYears * 12, -currentLoanBalance)
    Next i
    ' Calculate total interest on new loan
    For i = 1 To newYears * 12
        newTotalInterest = newTotalInterest + _
            IPmt(newRate / 12, i, newYears * 12, -currentLoanBalance)
    Next i
    result = "Current loan interest: " & Format$(currentTotalInterest, "Currency") & vbCrLf
    result = result & "New loan interest: " & Format$(newTotalInterest, "Currency") & vbCrLf
    result = result & "Closing costs: " & Format$(closingCosts, "Currency") & vbCrLf
    result = result & "Net savings: " & _
             Format$(currentTotalInterest - newTotalInterest - closingCosts, "Currency")
    AnalyzeRefinancing = result
End Function

Error Handling

The IPmt function can raise errors: - Invalid procedure call (Error 5): If per is less than 1 or greater than nper - Type Mismatch (Error 13): If arguments are not numeric - Overflow (Error 6): If result exceeds Double range

On Error GoTo ErrorHandler
Dim interestPayment As Double
interestPayment = IPmt(0.08 / 12, 1, 48, -20000)
Debug.Print "Interest payment: " & Format$(interestPayment, "Currency")
Exit Sub
ErrorHandler:
    MsgBox "Error calculating interest: " & Err.Description, vbCritical

Performance Considerations

• Calculation Intensity: IPmt involves complex financial calculations
• Loop Performance: Calculating all payments can be slow for long-term loans
• Caching: Consider caching amortization schedules rather than recalculating
• Precision: Uses Double precision for accurate financial calculations

Best Practices

1. Consistent Periods: Ensure rate and nper use same time units (monthly, quarterly, etc.)
2. Sign Convention: Use negative for cash paid, positive for cash received
3. Validate Period: Check that per is between 1 and nper
4. Error Handling: Wrap financial calculations in error handlers
5. Rounding: Round currency values appropriately for display
6. Documentation: Document assumptions about payment timing (beginning/end of period)
7. Testing: Verify that IPmt + PPmt = Pmt for each period

Platform and Version Notes

• Available in all VB6 versions
• Part of VBA financial functions
• Uses Double precision (not Currency type)
• Consistent with Excel's IPMT function
• Sign convention follows financial standards

Limitations

• Does not handle variable interest rates
• Assumes constant payment amounts
• Does not account for fees or other charges
• Limited to fixed annuity calculations
• No built-in support for skip payments or extra payments

Related Functions

• PPmt: Principal payment for a period
• Pmt: Total payment for a period
• Rate: Interest rate per period
• NPer: Number of periods
• PV: Present value
• FV: Future value