This example calculates how much of a payment for a specific period is principal when all the payments are of equal value. Given are the interest percentage rate per period (APR / 12), the payment period for which the principal 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.
NL = Chr(13) & Chr(10) ' Define newline.
TB = Chr(9) ' Define tab.
Fmt = "###,###,##0.00" ' Define money format.
FVal = 0 ' Usually 0 for a loan.
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 do you have to make?")
PayType = MsgBox("Do you make payments at the end of month?", MB_YESNO)
If PayType = ID_NO Then PayType = BEGINPERIOD Else PayType = ENDPERIOD
Payment = Abs(-Pmt(APR / 12, TotPmts, PVal, FVal, PayType))
Msg = "Your monthly payment is " & Format(Payment, Fmt) & "."
Msg = Msg & "Would you like a breakdown of your principal and "
Msg = Msg & "interest per period?"
MakeChart = MsgBox(Msg, MB_YESNO) ' See if chart is desired.
If MakeChart <> ID_NO Then
If TotPmts > 25 Then MsgBox "Only first 25 periods will be shown."
Msg = "Month Payment Principal Interest" & NL
For Period = 1 To TotPmts
If Period > 25 Then Exit For ' Show only first 25.
P = PPmt(APR / 12, Period, TotPmts, -PVal, FVal, PayType)
P = (Int((P + .005) * 100) / 100) ' Round principal.
I = Payment - P
I = (Int((I + .005) * 100) / 100) ' Round interest.
Msg = Msg & Period & TB & Format(Payment, Fmt)
Msg = Msg & TB & Format(P, Fmt) & TB & Format(I, Fmt) & NL
Next Period
MsgBox Msg ' Display amortization table.
End If