Creating an Amortisation Schedule in Access (1 Viewer)

SAM256 · Jul 18, 2019

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 · Jul 18, 2019

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

Cronk · Jul 18, 2019

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_ · Jul 18, 2019

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 · Jul 19, 2019

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 · Jul 19, 2019

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

SAM256 · Jul 19, 2019

arnelgp

You are a true GENIUS (all caps)

thank you very very much.

arnelgp · Jul 19, 2019

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 · Jul 19, 2019

Will do and give you feedback once done.

Thanks again arnelgp.

SAM256 · Aug 2, 2019

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 · Aug 2, 2019

I forgot to attach the file.

Please find attached.

Gasman · Aug 2, 2019

Cross posted here as well arne

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

SAM256 · Aug 2, 2019

That is true.

Can you kindly help?

arnelgp · Aug 2, 2019

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

SAM256 · Aug 2, 2019

Hi arnelgp

Please find attached the sample DB.

Thanks.

SAM256 · Aug 2, 2019

Please find attached zip file with sample DB.

arnelgp · Aug 2, 2019

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 · Aug 2, 2019

arnelgp

You did it again.

God bless you and thanks again.

SAM256 · Aug 2, 2019

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 · Aug 2, 2019

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

Creating an Amortisation Schedule in Access (1 Viewer)

Registered User.

Attachments

I’m here to help

Registered User.

Longboard on the internet

Registered User.

..forever waiting... waiting for jellybean!

Attachments

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Registered User.

Attachments

Enthusiastic Amateur

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Attachments

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread