Creating an Amortisation Schedule in Access (1 Viewer)

[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.

 

[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.

 

[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.