Creating an Amortisation Schedule in Access

[SAM256]

Hi arnelgp,

I have been able to add the first 2 columns i.e. Interest Paid and Principal Paid to the SQL script.

However, the final column i.e. the Outstanding_Balance has failed me. Can you kindly assist with the final column i.e.

Column Name: Outstanding_Balance
Column Logic: For Schedule_ID = 1, Beginning Balance - Principal_Paid
(Schedule ID = 1)
For Subsequent Schedule_IDs = Previous Schedule_ID
Outstanding_Balance - Principal_Paid (for respective Schedule
ID)

Also, from the earlier SQL scripts within the DB, in the Total_Payment field is it possible to add a zero (0) where no record of payment is obtained for the respective schedule_ID and Loan_ID, so that the resulting computations do not return and error in the table.

 

[Pat Hartman]

Arne,
I found several bugs
1. I changed the period to 36 months and got a scaling error
2. I changed some other number and the scaling error went away but total payment, Payment Date, Interest Allocation, Principal Allocation, Outstanding balance don't calculate
3. I don't know what Over payment and Days Past Due mean in this context.

Thanks

 

[Pat Hartman]

I have an amortization schedule I built for one of my apps but it is integrated and would take a while to extricate from the rest of the app. But I found this code that I used to test my version. I didn't write this but it does calculate correctly. I checked it against several spreadsheets I had and I used it to validate my calculations. It should help you to validate the more complex code.

Public Function testcalc()
Dim NL, TB, Fmt, FVal, PVal, APR, TotPmts, PayType, Payment, Msg, MakeChart, Period, P, i
Const ENDPERIOD = 0, BEGINPERIOD = 1    ' When payments are made.
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?", vbYesNo)
If PayType = vbNo Then PayType = BEGINPERIOD Else PayType = ENDPERIOD
'Payment = Abs(-Pmt(APR / 12, TotPmts, PVal, FVal, PayType))
Payment = 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, vbYesNo)    ' See if chart is desired.
If MakeChart <> vbNo Then
    If TotPmts > 12 Then MsgBox "Only first year will be shown."
    Msg = "Month  Payment  Principal  Interest" & NL
    For Period = 1 To TotPmts
        If Period > 12 Then Exit For    ' Show only first 12.
        P = PPmt(APR / 12, Period, TotPmts, -PVal, FVal, PayType)
        P = (Int((P + 0.005) * 100) / 100)   ' Round principal.
        i = Payment - P
        i = (Int((i + 0.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

End Function

 

[arnelgp]

thanx ms.pat for the advice.
right now, all calculations are provided by the op in excel format (w/formula).
he is not using any access built-in formala (ie. pmt()).
I then translate the formula in access code.
I might hijack you're code, for future.

 

[Pat Hartman]

You're welcome Arne,
Here's the code my app actually uses. I didn't want to post it because it isn't stand alone like the procedure I posted previously but you are capable of working out the issues and determining if I left out anything important.

Call it with:

Call CreateAmortization(Me.txtPropertyId, Me.txtStartMMYY)

Then this I think is all the code. If something is missing let me know. I define the DAO objects in the form header so that I can reference them in several procedures without having to pass in arguments. The app this comes from is one that evaluates rental properties for purchase. The loop is because the property may have more than one mortgage that needs to be calculated. I'm sure there is also code somewhere that deletes any existing amortization records for this property before this procedure starts and I don't see it so it must be back where the call is.

Option Compare Database
Option Explicit

Private db As DAO.Database
Private rs As DAO.Recordset
Private qd As DAO.QueryDef
Private td As DAO.TableDef
Private rsMtg As DAO.Recordset
Private qdMtg As DAO.QueryDef
Public Sub CreateAmortization(PropertyId As Variant, StartDate As Variant)
    
On Error GoTo Err_Proc
    If IsNull(PropertyId) Then
        MsgBox "Plese select a propertyID", vbOKOnly
        Exit Sub
    End If
    If Not IsDate(StartDate) Then
        MsgBox "Please select a start date.", vbOKOnly
        Exit Sub
    End If
    
    Set db = CurrentDb()
    Set qdMtg = db.QueryDefs!qMtgForProperty
        qdMtg.Parameters![EnterPropertyID] = PropertyId
    Set rsMtg = qdMtg.OpenRecordset
    Do Until rsMtg.EOF = True
        Call WriteRecs(CDate(StartDate))
        rsMtg.MoveNext
    Loop
    
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume Exit_Proc
    End Select
End Sub
Public Sub WriteRecs(StartDate As Date)

    Dim Mths As Integer
    Dim PmtNum As Integer
    Dim BeginningBal As Currency
    Dim EndingBal As Currency
    Dim PmtDate As Date
    Dim CumInt As Currency
    Dim SchedPmt As Currency
    Dim IntRate As Double
    Dim PmtsPerYear As Integer
    Dim MtgAmt As Currency
    Dim MtgTerm As Integer
    Dim PmtYear As Integer
    Dim PmtMonth As Integer

On Error GoTo Exit_Proc

    Set td = db.TableDefs!tblAmortization
    Set rs = td.OpenRecordset
    
    Mths = rsMtg!MortgageTermYears * 12
    BeginningBal = rsMtg!MortgageAmt
    PmtDate = StartDate
    CumInt = 0
    IntRate = rsMtg!InterestRate
    PmtsPerYear = Nz(rsMtg!PmtsPerYear, 12)
    MtgAmt = rsMtg!MortgageAmt
    MtgTerm = rsMtg!MortgageTermYears
    SchedPmt = -Pmt(IntRate / PmtsPerYear, MtgTerm * PmtsPerYear, MtgAmt, 0, 0)
    PmtYear = 1
    PmtMonth = 1
    
    PmtNum = 1
    Do Until PmtNum > Mths
        rs.AddNew
        rs!MortgageID = rsMtg!MortgageID
        rs!PmtNum = PmtNum
        rs!PmtYear = PmtYear
        rs!PmtMonth = PmtMonth
        rs!PmtDate = PmtDate
        rs!BeginningBal = BeginningBal
        rs!SchedPmt = SchedPmt
        rs!ExtraPmt = Nz(rsMtg!ExtraPmt, 0)
        rs!TotPmt = SchedPmt + rs!ExtraPmt
        rs!Interest = BeginningBal * (IntRate / PmtsPerYear)
        rs!Principal = rs!TotPmt - rs!Interest
        rs!EndingBal = BeginningBal - rs!Principal
        CumInt = CumInt + rs!Interest
        rs!CumInterest = CumInt
        BeginningBal = rs!EndingBal
        PmtNum = PmtNum + 1
        PmtDate = DateAdd("m", 1, PmtDate)
        rs.Update
        PmtMonth = PmtMonth + 1
        If PmtMonth > 12 Then
            PmtYear = PmtYear + 1
            PmtMonth = 1
        End If
    Loop
    
    
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume Exit_Proc
    End Select
End Sub

 

[SAM256]

Hi Pat Hartman,

That is some brilliant coding. For future purposes might need to borrow it to generate an amortization schedule.

My challenge currently is to incorporate the repayments in the amortization schedule to show a "schedule of repayments" against the amortization schedule.

For example, a client can pay a loan installment in parts, but each repayment for the same loan installment is summed and allocated to that particular loan installment.

arnelgp has been able to help with query to match the payments with installment paid, whether in part or whole.

However, the final column is where the difficulty really is i.e. the Loan_Outstanding, see the query for the previously attached file that i have been able to amend;

SELECT Payments.Loan_ID, Payments.Schedule_ID, Format(Sum(Payments.Amount_Paid),"#,##0") AS Amount_Paid
FROM Payments
GROUP BY Payments.Loan_ID, Payments.Schedule_ID;


SELECT Loan_Schedules.Loan_ID, Loan_Schedules.Schedule_ID, Loan_Schedules.Schedule_Date, Loan_Schedules.Beginning_Balance, Loan_Schedules.Scheduled_Payment, Loan_Schedules.Scheduled_Interest, Loan_Schedules.Scheduled_Principal, Loan_Schedules.Ending_Balance, IIF((Format((DLookUp("Amount_Paid","Loan_Payments","Loan_ID=" & [Loan_ID] & " And Schedule_ID=" & [Schedule_ID])),"#,##0"))="",0,(Format((DLookUp("Amount_Paid","Loan_Payments","Loan_ID=" & [Loan_ID] & " And Schedule_ID=" & [Schedule_ID])),"#,##0"))) AS Total_Payment, Format(IIF(Total_Payment<Loan_Schedules.Scheduled_Interest,Total_Payment,Loan_Schedules.Scheduled_Interest),"#,##0") AS Interest_Paid, Format(IIF(Total_Payment=Loan_Schedules.Scheduled_Interest,0,(Total_Payment-Interest_Paid)),"#,##0") AS Principal_Paid
FROM Loan_Schedules;

 

[arnelgp]

can you verify the result if correct on the last 3 columns.

you import the 2 Modules I added.

 

[SAM256]

arnelgp,

How many times am I going to say thank you.

Thanks a lot. It worked like a charm.

Just brilliant coding, that is what it is.

 

[arnelgp]

you're welcome, just compare it with your excel sheet, if you have.

 

[SAM256]

Hi arnelgp,

How would i go about filtering the SQL query by a value input in a text field on a specific form i.e. where an additional criteria is added to filter the Loan_Schedules table using the loan id input in a text field on a form

Loan_Schedules.Loan_ID=Forms!FormX!Loan_ID

whenever i input the condition in the criteria field within the query an error is generated in the first module (module1).

Please assist on a way forward.

 

[SAM256]

Hi arnelgp,

Please see attached what i mean exactly.

when i add the following criteria to the FinalQuery under Loan_Schedules.Loan_ID = [Forms]![Form1]![ID]

And input the respective Loan_ID in the ID field within Form1, an error is generated in the macro when the query is executed using the button on Form1.

Kindly assist with resolving the error.

 

[arnelgp]

hello sam. i remove the criteria from the query. instead a run the form with criteria in it. see the click event of the button. also the design has somewhat changed.

 

[SAM256]

Hi arnelgp,

It worked very well.

Thanks again.