Function fAmortization(dteDate As Date, strLoanID As String, lngTotalPayments As Long, dbAPR As Double, dbLoanAmount As Double)
On Error GoTo errHandler
'8.16.2019 Gina Whipp (https://www.access-diva.com/)
'?fAmortization(#1/1/2019#, "ABC", 60, 7.5, 20000)
'Payment Amount will equal 400.76
 
    Dim dbPayment, P, I As Double
    Dim lngPeriod As Long
    Dim varLoanID As String
    Dim strSQL As String
 
    If dbAPR > 1 Then dbAPR = dbAPR / 100 ' Ensure proper format
    dbPayment = Abs(-Pmt(dbAPR / 12, lngTotalPayments, dbLoanAmount, 0)) 'Payment
    dbPayment = (Int((dbPayment + 0.005) * 100) / 100) 'Round Payment
 
    For lngPeriod = 1 To lngTotalPayments
        P = PPmt(dbAPR / 12, lngPeriod, lngTotalPayments, -dbLoanAmount, 0) 'Principal
        P = (Int((P + 0.005) * 100) / 100) 'Round Principal
        I = dbPayment - P 'Interest
        I = (Int((I + 0.005) * 100) / 100) 'Round Interest
        varLoanID = strLoanID
 
            strSQL = "INSERT INTO tblLoanPayments( lpLoanID, lpPayment, lpInterest, lpPrincipal, lpPaymentDate ) " & _
                        "VALUES (""" & varLoanID & """, " & dbPayment & ", " & I & ", " & P & ", " & "#" & dteDate & "#" & ")"
                      CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
 
        dteDate = Format(DateAdd("m", 1, dteDate), "mm/dd/yyyy")
    Next lngPeriod
 
    MsgBox "Amortization table finished.", vbInformation + vbOKOnly, "Amortization"
 
exitHandler:
  Exit Function
 
errHandler:
  MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "fAmortization()"
  Resume exitHandler
End Function