Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-18-2019, 10:29 AM   #1
SAM256
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 6
Thanked 0 Times in 0 Posts
SAM256 is on a distinguished road
Creating an Amortisation Schedule in Access

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
Attached Files
File Type: xlsx Loan_Amortisation.xlsx (15.3 KB, 13 views)

SAM256 is offline   Reply With Quote
Old 07-18-2019, 10:49 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,665
Thanks: 38
Thanked 891 Times in 874 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Creating an Amortisation Schedule in Access

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 07-18-2019, 11:41 AM   #3
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,125
Thanks: 3
Thanked 462 Times in 455 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Creating an Amortisation Schedule in Access

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.

Cronk is offline   Reply With Quote
Old 07-18-2019, 01:59 PM   #4
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,948
Thanks: 19
Thanked 370 Times in 363 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Creating an Amortisation Schedule in Access

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?
Mark_ is offline   Reply With Quote
Old 07-19-2019, 04:08 AM   #5
SAM256
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 6
Thanked 0 Times in 0 Posts
SAM256 is on a distinguished road
Re: Creating an Amortisation Schedule in Access

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.
SAM256 is offline   Reply With Quote
Old 07-19-2019, 04:36 AM   #6
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,623
Thanks: 59
Thanked 2,440 Times in 2,340 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Creating an Amortisation Schedule in Access

I am willing to help you, I have spare time to finish this.
Attached Files
File Type: zip LoanAmortization.zip (92.0 KB, 9 views)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
SAM256 (07-19-2019)
Old 07-19-2019, 04:48 AM   #7
SAM256
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 6
Thanked 0 Times in 0 Posts
SAM256 is on a distinguished road
Re: Creating an Amortisation Schedule in Access

arnelgp

You are a true GENIUS (all caps)

thank you very very much.

SAM256 is offline   Reply With Quote
Old 07-19-2019, 04:51 AM   #8
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,623
Thanks: 59
Thanked 2,440 Times in 2,340 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Creating an Amortisation Schedule in Access

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-19-2019, 04:53 AM   #9
SAM256
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 6
Thanked 0 Times in 0 Posts
SAM256 is on a distinguished road
Re: Creating an Amortisation Schedule in Access

Will do and give you feedback once done.

Thanks again arnelgp.
SAM256 is offline   Reply With Quote
Old 08-01-2019, 11:11 PM   #10
SAM256
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 6
Thanked 0 Times in 0 Posts
SAM256 is on a distinguished road
Re: Creating an Amortisation Schedule in Access

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 is offline   Reply With Quote
Old 08-01-2019, 11:12 PM   #11
SAM256
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 6
Thanked 0 Times in 0 Posts
SAM256 is on a distinguished road
Re: Creating an Amortisation Schedule in Access

I forgot to attach the file.

Please find attached.
Attached Files
File Type: xlsx Tables.xlsx (10.4 KB, 2 views)
SAM256 is offline   Reply With Quote
Old 08-01-2019, 11:16 PM   #12
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,850
Thanks: 411
Thanked 691 Times in 670 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Creating an Amortisation Schedule in Access

Cross posted here as well arne

https://www.access-programmers.co.uk...d.php?t=306144
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 08-01-2019, 11:18 PM   #13
SAM256
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 6
Thanked 0 Times in 0 Posts
SAM256 is on a distinguished road
Re: Creating an Amortisation Schedule in Access

That is true.

Can you kindly help?
SAM256 is offline   Reply With Quote
Old 08-01-2019, 11:20 PM   #14
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,623
Thanks: 59
Thanked 2,440 Times in 2,340 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Creating an Amortisation Schedule in Access

if the sheet is extracted from your db, can you post your db. which table name can we get this.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-01-2019, 11:38 PM   #15
SAM256
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 6
Thanked 0 Times in 0 Posts
SAM256 is on a distinguished road
Re: Creating an Amortisation Schedule in Access

Hi arnelgp

Please find attached the sample DB.

Thanks.

SAM256 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a schedule Tigereyes78 Tables 9 03-28-2017 10:08 AM
Creating a Production schedule Puggwash General 4 03-28-2016 11:34 PM
creating a classroom schedule jonobugs Reports 17 04-10-2015 07:27 AM
Problems with Date when creating a schedule using VBA slaterino Modules & VBA 9 01-21-2011 12:47 PM
Question Question about creating a schedule ninjagore General 4 07-29-2010 06:35 AM




All times are GMT -8. The time now is 07:26 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World