IPmt Function |
Yes
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.
A Double representing the interest payment.
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.
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.
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
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.