IPmt Function

---

Named Arguments

Yes

Syntax

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

rate

Use: Required

Data Type: Double

The interest rate per period.

per

Use: Double

Data Type: Any valid numeric expression

The period for which a payment is to be computed.

nper

Use: Double

Data Type: Any valid numeric expression

The total number of payment periods.

pv

Use: Double

Data Type: Any valid numeric expression

The present value of a series of future payments.

fv

Use: Optional

Data Type: Variant

The future value or cash balance after the final payment. If omitted, the default value is 0.

type

Use: Optional

Data Type: Variant

A value indicating when payments are due. indicates that payments are due at the beginning of the payment period; 1 indicates that payments are due at the end of the period. If omitted, the default value is 0.

Return Value

A Double representing the interest payment.

Description

Computes the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate. An annuity is a series of fixed cash payments made over a period of time. It can be either a loan payment or an investment.

Rules at a Glance

• The value of per can range from 1 to nper.

• If pv and fv represent liabilities, their value is negative; if assets, their value is positive.

Example

The ComputeSchedule function accepts a loan amount, an annual percentage rate, and a number of payment periods. It uses the Pmt function to calculate the payment per period, then returns a two-dimensional array in which each subarray contains the number of the period, the interest paid for that period, and the principal paid for that period.

Private Function ComputeSchedule(dblAmount As Double, _
                 dblRate As Double, lngNPer As Long) _
                 As Variant

Dim dblIPmt As Double, dblPmt As Double
Dim dblPrincipal As Double
Dim lngPer As Long
Dim strFmt As String
Dim varArray() As Variant
ReDim varArray(lngNPer, 2)

strFmt = "###,###,##0.00"
dblPmt = Pmt(dblRate / 12, lngNPer, -dblAmount, 0, 0)

For lngPer = 1 To lngNPer
   dblIPmt = IPmt(dblRate / 12, lngPer, lngNPer, -dblAmount)
   dblPrincipal = PPmt(dblRate / 12, lngPer, lngNPer, _
                  -dblAmount)
   dblAmount = dblAmount - dblPrincipal
   varArray(lngPer, 0) = lngPer & "."
   varArray(lngPer, 1) = Format(dblIPmt, strFmt)
   varArray(lngPer, 2) = Format(dblPrincipal, strFmt)
Next

ComputeSchedule = varArray

End Function

Programming Tips and Gotchas

• rate and nper must be expressed in the same time unit. That is, if nper reflects the number of monthly payments, rate must be the monthly interest rate.

• The interest rate is a percentage expressed as a decimal. For example, if nper is the total number of monthly payments, an annual percentage rate (APR) of 12% is equivalent to a monthly percentage rate of 1%. The value of rate is therefore .01.

See Also

NPer Function, Pmt Function, PPmt Function, Rate Function