Creating an Amortisation Schedule in Access (1 Viewer)

[SAM256]

Hi all,


I am new to the forum and using ms access. I would really like some help creating the attached excel loan schedule in access.


The fields shaded yellow would be populated using a form and those shaded green would be calculated in the db.


Please kindly assist with the request :banghead:

 

[theDBguy]

Hi. Welcome to the forum! Why does it need to be in Access, if you already have it in Excel? Just curious...

 

[Cronk]

I agree,. It seems a lot of work to put emulate it in Access when a spreadsheet is ideal. Incidentally, it looks more like a payment schedule rather than amortization which is write down of an intangible asset such as goodwill or a licence where there are no payments or interest applied.

 

[Mark_]

Just to make sure, you are using simple interest, not compound?

Are you also trying to create a schedule of payments due so that you know when the next payment will be and for how much?

 

[SAM256]

Thank you all for your comments. Don't pay to much attention to the repayment columns i.e. columns I to O.

I am building a loan origination database in access. And all that is left is to create a loan schedule in access. However, I can create it in Excel easily, but replicating the same in access is something I can't quite figure out.

And I really need, you (the experts in access help).

Please help.

Mark.

You are very right, the computation of interest is simple (i.e. Loan amount*interest)
The weekly or monthly installment amount is calculated as follows ((Loan amount*interest)+Loan Amount)/Tenor

And yes again, I need a schedule of payments to know when the installments are due, and to display the information as in the excel sheet without the payments history.

Loans can be issued out weekly i.e. where the due date is every 7 days from the disbursement date OR
Monthly where the due date is every 30 days from the disbursement date

Please help.

 

[arnelgp]

I am willing to help you, I have spare time to finish this.

 

[SAM256]

arnelgp

You are a true GENIUS (all caps)

thank you very very much.

 

[arnelgp]

please test it on live data.
I only have a chance to test it on your sample excel file.
come back if you encounter errors.

btw, you need to fill all fields and press Save button on New product before it will create the schedule.

 

[SAM256]

Will do and give you feedback once done.

Thanks again arnelgp.

 

[SAM256]

Hi arnelgp

The amortization script worked like a charm. Thanks again.

I just have another kind request regarding, creating a calculated field (named "Total_Payment" - highlighted yellow in the attached excel sheet) in 1 table called "Loan_Schedules" that sums values from another table called "Payments" based on 2 criteria (the Loan_ID and Schedule_ID) and updates the field column (Total_Payment)

I have attached an excel file illustrating what i am trying to achieve.

Please kindly assist.

 

[SAM256]

I forgot to attach the file.

Please find attached.

 

[Gasman]

Cross posted here as well arne

https://www.access-programmers.co.uk/forums/showthread.php?t=306144

 

[SAM256]

That is true.

Can you kindly help?

 

[arnelgp]

if the sheet is extracted from your db, can you post your db. which table name can we get this.

 

[SAM256]

Hi arnelgp

Please find attached the sample DB.

Thanks.

 

[SAM256]

Please find attached zip file with sample DB.

 

[arnelgp]

there are two queries I made.
the "FinalQuery" is what you need, while the other is also needed.

you can remove the TotalPayment column and do the calculation in the query.

 

[SAM256]

arnelgp

You did it again.

God bless you and thanks again.

 

[SAM256]

Hi arnelgp,

Is it possible to add the following columns to SQL script with the following logic;

Column Name: Interest_Paid
Column Logic: IF(TotalPayment < Scheduled_Interest, TotalPayment, Scheduled_Interest)

Column Name: Principal_Paid
Column Logic: IF(TotalPayment = Interest_Paid, 0 ,(TotalPayment-Interest_Paid))

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)

 

[arnelgp]

yes you may try.
be sure to check if after adding those calculated fields, your query is still updateable.