Syntax

IPmt(Rate, Per, Nper, Pv, Fv, Due)

Description

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

Comments

An annuity is a series of fixed payments made to an insurance company or other investment company over a period of time. Examples of annuities are mortgages, monthly savings plans, and retirement plans.

The following table describes the different parameters:

Parameter

Description

Rate

Double representing the interest rate per period. If the payment periods are monthly, be sure to divide the annual interest rate by 12 to get the monthly rate.

Per

Double representing the payment period for which you are calculating the interest payment. If you want to know the interest paid or received during period 20 of an annuity, this value would be 20.

Nper

Double representing the total number of payments in the annuity. This is usually expressed in months, and you should be sure that the interest rate given above is for the same period that you enter here.

Pv

Double representing the present value of your annuity. In the case of a loan, the present value would be the amount of the loan because that is the amount of cash you have in the present. In the case of a retirement plan, this value would be the current value of the fund because you have a set amount of principal in the plan.

Fv

Double representing the future value of your annuity. In the case of a loan, the future value would be zero because you will have paid it off. In the case of a savings plan, the future value would be the balance of the account after all payments are made.

Due

Integer indicating when payments are due. If this parameter is 0, then payments are due at the end of each period (usually, the end of the month). If this value is 1, then payments are due at the start of each period (the beginning of the month).

Rate and Nper must be in expressed in the same units. If Rate is expressed in percentage paid per month, then Nper must also be expressed in months. If Rate is an annual rate, then the period given in Nper should also be in years or the annual Rate should be divided by 12 to obtain a monthly rate.

If the function returns a negative value, it represents interest you are paying out, whereas a positive value represents interest paid to you.

Example

This example calculates the amount of interest paid on a $1,000.00 loan financed over 36 months with an annual interest rate of 10%. Payments are due at the beginning of the month. The interest paid during the first 10 months is displayed in a table.

Const crlf = Chr$(13) + Chr$(10)

Sub Main()
  msg1 = ""
  For x = 1 to 10
    ipm# = IPmt((.10/12),x,36,1000,0,1)
    msg1 = msg1 & Format(x,"00") & " : " & Format(ipm#," 0,0.00") & crlf
  Next x
  MsgBox msg1
End Sub

See Also

NPer (function); Pmt (function); PPmt (function); Rate (function).

I