IPmt function usage in Excel

First, IPMT grammar

Returns a Double specifying the value of the interest paid for an annuity with fixed periodic payments and interest rates for a period of time.

Grammar

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

The IPMT function has the following named arguments:

Parameter description

Rate necessary. DOUBLE Specifies the interest rate for each issue. For example, if there is an annual percentage of loans (APR) of 10% and a monthly car loan, the interest rate for each issue is 0.1/12, or 0.0083.

Per necessary. Double Specifies the payment period in range 1 between nper.

NPer necessary. Double Specifies the total number of payment periods for an annuity. For example, if you choose a monthly payment for a four-year car loan, the loan has a total of 4 * 12 (or 48) payment periods.

PV necessary. Double, specifying the present value of a future series of payments or receipts. For example, when borrowing money to buy a car, the amount lent to the lender is the present value for future monthly payments to the lender.

FV Optional. A Variant specifies the future value or cash balance that is desired after a loan is paid. For example, the future value of a loan is 0 dollars after the loan is paid off. However, if you want to save 50,000 dollars for the Children's Education Fund in 18 years, then 50,000 dollars will be the future value. If omitted, the default value is 0.

Type is optional. Variant Specifies the loan expiration time. If the loan expires at the end of the loan cycle, please use 0. If the loan expires at the beginning of the cycle, use 1. If omitted, the default value is 0.

Description

An annuity is a series of fixed cash payments over a period of time. An annuity can be a loan (such as a home mortgage) or an investment (such as a monthly savings plan).

Rate and NPer parameters must be computed in the same unit during payment. For example, if rate is calculated in months, the nper must also be calculated in months.

For all parameters, cash expenditures (such as savings deposits) are represented by negative numbers, and cash receipts (such as dividend cheques) are expressed in positive numbers.

Second, IPmt function usage

This example uses the IPMT function to calculate how much of the payment in each issue is interest, with the same amount for each period of payment. The calculation requires a given rate of interest (APR/12), the payment period of interest (Period), the total number of payments (TOTPMTS), the present value of the loan or principal (PVal), the future value of the loan (FVal), and the method of payment to indicate the beginning or end of payment (PayType).

Dim fval, FMT, PVal, APR, TotPmts, PayType, Period, INTPMT, Totint, MSG

Const endperiod = 0, Beginperiod = 1 ' payment method.

fval = 0 ' is usually zero for a loan.

FMT = "###,###,# #0.00" defines the amount format.

PVal = InputBox ("How much does you want to borrow?")

APR = InputBox ("What is the annual percentage rate of your loan?")

If Apr > 1 Then APR = apr/100 ' Ensure the format is correct.

TotPmts = InputBox ("How many monthly payments?")

PayType = MsgBox ("Do I make payments in end of the month?", vbYesNo)

If PayType = vbno Then paytype = beginperiod Else PayType = endperiod

For Period = 1 to TotPmts ' sums all interest.

INTPMT = IPMT (APR/12, Period, TotPmts,-pval, fval, PayType)

Totint = Totint + INTPMT

Next Period

MSG = "You ll pay a total of" & Format (Totint, FMT)

msg = msg & "in interest for this loan."

MsgBox Msg ' displays results.

The above code is used in VBA state.