IPmt Function Example

This example calculates how much of a payment is interest when all the payments are of equal value.  Given are the interest percentage rate per period (APR / 12), the payment period for which the interest portion is desired (Period), the total number of payments (TotPmts), the present value or principal of the loan (PVal), the future value of the loan (FVal), and a number that indicates whether the payment is due at the beginning or end of the payment period (PayType).

Const ENDPERIOD = 0, BEGINPERIOD = 1      ' When payments are made.
Const MB_YESNO = 4                        ' Define Yes/No buttons.
Const ID_NO = 7                           ' Define No as a response.
FVal = 0                                  ' Usually 0 for a loan.
Fmt = "###,###,##0.00"                    ' Define money format.
PVal = InputBox("How much do you want to borrow?")
APR = InputBox("What is the annual percentage rate of your loan?")
If APR > 1 Then APR = APR / 100           ' Ensure proper form.
TotPmts = InputBox("How many monthly payments?")
PayType = MsgBox("Do you make payments at end of the month?", MB_YESNO)
If PayType = ID_NO Then PayType = BEGINPERIOD Else PayType = ENDPERIOD
For Period = 1 To TotPmts                 ' Total 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                                ' Display results.