fAmortization()...

This Function will calculate the Amortization schedule and put it in a Table with just few pieces of information.
The difficult I do immediately, the impossible takes a little bit longer.
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
VBA
Tips (Main)
Home
Creating a Multi-Value field using Alphabet
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Snippets for Exporting to Excel
Log Field Changes
Log Record Deletions
Check for Duplicate Values
ValidateData()
ClearClipboard()
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Automatically Send eMail Notifications
fFindBookmark()
Looping Records to Send eMail
fxlFindReplace()
fHighlightRequiredControls()
Check if Table Exists
fMouseOverCurrent()
Insert (or Remove) Blank Line
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.