Question Loan Amortization Schedule

[anski]

I need to create a Loan Database Management Program. What's critical is the creation of the amortization schedule. I know that this is easier done in Excel. But I need to do this in Access.

After the user enters the loan amount, interest, terms, etc., I want the program to be able to create an editable form showing the amortization schedule. The user can then edit (if needed) either the principal amount or the interest amount. I have attached an Excel file that does what I need. How can I do this in Access?

Thanks.

 

[DavidAtWork]

Presumably you are familiar with the calculation that is performed in your Excel version, in which case I don't see why you can't apply that to Access. Just need to create a table which will in effect be your repayment schedule which will hold the column values as in your Excel. That table will need a foreign key that will link to a LoanID. You could create a form which run off a Loan table (or with unbound text boxes) that the user will enter the same values as in your Excel ver, ie Loan amount, interest rate, number of payments etc. This could autogenerate a LoanID which can be used in the schedule creation. Once all the required Loan details have been entered you could use a button 'Create Schedule' to fire a function you'd need to write in vba that would create records in the repayment schedule table where each value is calculated in the same way your Excel works. Once all records have been created, this can then be displayed in a sub form under where user entered the loan details. I'm not familiar with how the interest is calculated as the loan is paid off, so couldn't say what the calculation would look like
David

 

[anski]

Thanks for replying. My main problem was how to create the schedule (like create rows equal to the payment tenor). If the loan is payable in 10 months, Access should be able to create 10 rows for each of the month.

I've figured out a way to do this by using a macro. Since I'm not a programmer (I'm more of a finance person), I had to find a way to do this without using VBA. I am more comfortable with macros.

I used setvalue and gotorecord (next). The macro continues until the total rows (dcount) is already equal to the payment tenor

 

[jmeni]

Hi Anski, please can you assist as I'm faced with a similar situation: I have already created a loan form with a repayment schedule subform created from a schedule table! I'm a newbie to access and vba programming! Thanks

 

[jmeni]

Waoh! Pat Hartman, U've made my day! I'm so grateful. Thanks & Again Thanks! This is as explicit as it can ever be! Based on what I have created (tables and forms etc) I will modify as required but I'll still need to SEROIUSLY study, analyze your solution so as to get a better understanding (understand the why behind the solution) and so improve on my journey to an Access expert level. As I said I'm a newbie to Access and PROMISE to study to show myself worthy of your feedback, so I can also contribute similarly to others lost in the Access maze!!

2ndly: Regards the lack of details, I didn't want to fall foul to forum's rules because I have earlier made a post in another forum(forum rules also barring me from posting links, but I was explicit on the details in the Modules and VBA forum: my query was Recordsets and VBA!)

Thanks once more!

Though I did not attach an upload of d database file, which I will do later on.

 

[jmeni]

Hi Richar3dner & all, Thanks for your offer! Please can you check the Modules & VBA forum because I have posted an explicit description of my problem: I don't know if it's okay to restate the issue here or elaborate in the former post. Waiting for a response so I can know how to proceed.
Also, regards the solution by Pat Hartman: I'm not quite clear on the use of querydef and tabledef, rsMtg & qdMtg(what's their use) and regards the AmortTable.jpg file, my schedule table is almost exactly alike except for the RecID(mine is ScheduleID) and UpdateDate??? (which I don't have on mine).
Thanks

 

[jmeni]

Dear Pat Hartman,
So sorry for not mentioning that I have an open post! Also I can't post the link to my earlier post as I'm yet to hit the minimum number(10 post) before I can post links to post. But you can merge the post as you have suggested and also my earlier post is in the Modules & VBA forum, the title is Recordsets and VBA by Jmeni.

I'm yet to have a full solution to my post, but I'll keep my subsequent post to the other post in the Modules & VBA post.
Thanks.

 

[KasanagAccess]

Good Day, I would have the same problem with how to generate Loan Amortization. Please check my unfinished database.

 

[general]

hi guys...need some help here...
i need to create a sample access dabase with code vba like loan amortization dbs .but step by step from A to Z