UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Urgent Help Needed In Creating Amortization Table, Access 2016    
 
   
KevinOyondi
post Aug 12 2017, 08:20 AM
Post#1



Posts: 7
Joined: 20-July 17



Greetings from Kenya! Attached below is a sample of what I am working on. I have used the pmt function to calculate monthly payments in my table(loantbl). I have also successfully generated an amortization schedule using a query(schedule) where I have exploited the DateAdd function. The problem is that I need to track repayment of the loans. I need help in writing the schedule directly to a table as opposed to using an append query because of duplicates. I am a green horn in VBA.
I have searched all over to no avail by the way that is why I have brought this question here.
Attached File(s)
Attached File  HELP.zip ( 36.48K )Number of downloads: 5
 
Go to the top of the page
 
GroverParkGeorge
post Aug 12 2017, 09:02 AM
Post#2


UA Admin
Posts: 30,980
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

I'm not sure quite what you mean by "duplicates" here.

The query returns one record for each loan, for each installment. There are no duplicates in that sense.

Are you trying to limit the returned recordset to the installments for a single loan? If that's the case, the solution is to create a form from which to launch the query, using a dropdown (combobox) control to select one loan. Then filter the query against that loan.

See the attached demo. Is this what you want?

Attached File  LoanSchedule.zip ( 26.38K )Number of downloads: 2

--------------------
Go to the top of the page
 
KevinOyondi
post Aug 12 2017, 09:49 AM
Post#3



Posts: 7
Joined: 20-July 17



Thank you GroverParkGeorge. I would like to write the schedules to a table directly because I want some extra fields to track payment of the installments and also calculate penalties. I can append the data to a table with the extra fields but the problem is that there would be duplication of data in the table as I continue adding loans to the loantbl and appending each time. The attachment is just a small sample of my database.
This post has been edited by KevinOyondi: Aug 12 2017, 09:51 AM
Go to the top of the page
 
GroverParkGeorge
post Aug 12 2017, 10:22 AM
Post#4


UA Admin
Posts: 30,980
Joined: 20-June 02
From: Newcastle, WA


I see. "The attachment is just a small sample of my database."

Quite often, as is the case here, not providing the full picture of the requirement up front makes it harder to figure out what a viable solution could be.

So you are afraid that each time you run this query, you'll duplicate the results "in the table". That's a valid concern, of course, but I'm not sure how that would happen unless you actually re-run the same load again.

But that would be the case in any situation, I would imagine.

So are you trying to create a table of actual, existing loans to be tracked? Or are you trying to do a series of "what ifs" on the same loan over and over?

If the former, the solution would seem to be to create a unique index on your output table to prevent multiple instances of the same load scenario be appended. Perhaps a combination of Load ID, and one other field that would uniquely identify that scenario.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Aug 12 2017, 12:20 PM
Post#5


UA Admin
Posts: 30,980
Joined: 20-June 02
From: Newcastle, WA


Perhaps there's more to this.

Are you creating actual loans to be distributed to specific organizations?

Or, are you creating loan scenarios to be used in illustrating specific amortization schedules based on a loan amount, length of payback period and so on?

If the latter, then possibly I can see a concern of some aspect of duplication. However, even if you use the same loan scenario over and over, you're going to create a different loan for each customer, and therefore there will be different loan IDs for each of those.

What's the actual business requirement we're trying to address?

--------------------
Go to the top of the page
 
ADezii
post Aug 12 2017, 02:34 PM
Post#6



Posts: 1,846
Joined: 4-February 07
From: USA, Florida, Delray Beach


I apologize for coming into this Thread late but I do think that I have a handle on what you are requesting. As I see it, each time the Query (Schedule) is executed you want the results to go into a previously defined Table Structure consisting of extra Fields for future processing. This Table will be 'Empty' prior to the Query being run, then populated with its results each and every time. The Duplication that you refer to would be if prior Records existed in the Append Table, then there would be obvious Duplication. Download the Attachment and see if this is what you are looking for, if I am off track, I do apologize for wasting your time.

P.S. - I changed the Formatting/Data Type in the [MONTHLY PAYMENT] Field on loantbl strictly for this Demo. You can easily change it back to its original state.
This post has been edited by ADezii: Aug 12 2017, 02:34 PM
Attached File(s)
Attached File  HELP_Revised.zip ( 31.57K )Number of downloads: 3
 
Go to the top of the page
 
KevinOyondi
post Aug 12 2017, 02:41 PM
Post#7



Posts: 7
Joined: 20-July 17



1.Actually creating a table to track existing loans. This means that I have a table with basic info about the loan like LOAN ID, Interest rate etc and a second table with the schedule(as opposed to having the schedule on a query). I need the second table so that I can have an Amount Paid field for each installment.
2. May be I've not been able to put my point across. Anyone know how to create an amortization table in Access? Just that would help.
3.thank you for responding.
Go to the top of the page
 
KevinOyondi
post Aug 12 2017, 02:43 PM
Post#8



Posts: 7
Joined: 20-July 17



There is no hurry in Africa! Haha let me see what you've got.
Go to the top of the page
 
KevinOyondi
post Aug 12 2017, 03:50 PM
Post#9



Posts: 7
Joined: 20-July 17



ADezii this is actually how I want it. The deletion means I can't keep track of payments sadly. Thanks though. I'll try to see how to make it work tomorrow. Meanwhile any workaround will be highly appreciated.
Go to the top of the page
 
Jeff B.
post Aug 12 2017, 04:27 PM
Post#10


UtterAccess VIP
Posts: 9,859
Joined: 30-April 10
From: Pacific NorthWet


This may be an oversimplification, and may not take all of your constraints into account ...

From your description, you're "doing math" with numbers. While Access can do that, its strength is as a relational database. Is there a reason that a tool that's designed to work with numbers (e.g., Excel) is not appropriate to what you're trying to do?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
ADezii
post Aug 12 2017, 07:04 PM
Post#11



Posts: 1,846
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
Anyone know how to create an amortization table in Access?

  1. I came across some Code that generates an Amortization Table within a 2-Dimensional Array. I modified the Code to then use this Array to populate a Table (tblAmortization). Simply change the USER DEFINED CONSTANTS as listed below and in the Demo (Click() Event of Command Button) to generate this Table.
    CODE
    '************************** USER DEFINED SECTION **************************
    Const conAMOUNT As Currency = 50000
    Const conRATE As Single = 0.05
    Const conYEARS As Byte = 20
    Const conPAYMENTS_PER_YEAR As Byte = 12
    '**************************************************************************
  2. I am by no means a Financial Wizard, so I'll leave it up to you to evaluate this Demo.

This post has been edited by ADezii: Aug 12 2017, 07:05 PM
Attached File(s)
Attached File  HELP_Revised_3.zip ( 46.49K )Number of downloads: 1
 
Go to the top of the page
 
ADezii
post Aug 13 2017, 11:47 AM
Post#12



Posts: 1,846
Joined: 4-February 07
From: USA, Florida, Delray Beach


In my haste, it appears as though I have made a couple of serious Errors in my Demo. Please make the necessary adjustments prior to the Function Call:
CODE
Dim intCtr1 As Integer
Dim valAmortization As Variant
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset

CurrentDb.Execute "DELETE * FROM tblAmortization", dbFailOnError

'************************** USER DEFINED SECTION **************************
Const conAMOUNT As Double = 20000
Const conRATE As Double = 0.05
Const conYEARS As Byte = 2
Const conPAYMENTS_PER_YEAR As Byte = 12
'**************************************************************************
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblAmortization", dbOpenDynaset, dbAppendOnly)

valAmortization = AmortSchedTraditional(conAMOUNT, conRATE / 12, CLng((conYEARS * conPAYMENTS_PER_YEAR)))
'****************************** CODE HAS INTENTIONALLY BEEN OMITTED ******************************

This post has been edited by ADezii: Aug 13 2017, 11:49 AM
Go to the top of the page
 
KevinOyondi
post Aug 13 2017, 02:25 PM
Post#13



Posts: 7
Joined: 20-July 17



Thanks ADezii. I can comfortably pick up from where you have left. Jeff B I understand it is easier done in Excel just that this is a small component of my database and I want to keep everything within Access. Thanks for the concern.
Go to the top of the page
 
ADezii
post Aug 13 2017, 04:49 PM
Post#14



Posts: 1,846
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif
There is also a variation of then Amortization Code where you specify the Preferred Payment Amount as well as the Rate, and Present Value. The appropriate Amortization Table is then generated with the correct number of Payments. I did not include this is the Demo, but if you are interested I can Attach it for you.
This post has been edited by ADezii: Aug 13 2017, 04:50 PM
Go to the top of the page
 
KevinOyondi
post Aug 14 2017, 09:08 AM
Post#15



Posts: 7
Joined: 20-July 17



Helloo. I wouldn't mind that code too ADezii. Please attach it. 😊
Go to the top of the page
 
ADezii
post Aug 14 2017, 10:45 AM
Post#16



Posts: 1,846
Joined: 4-February 07
From: USA, Florida, Delray Beach


I have incorporated both the Traditional Amortization Code as well as the Preferred Monthly Payment Amortization Code into a single, completely independent Demo (both options will be obvious on the Intro Form). The Inputs for both approaches have been adjusted in order to validate each other. Good Luck with your Project.
This post has been edited by ADezii: Aug 14 2017, 10:45 AM
Attached File(s)
Attached File  HELP_Revised_4.zip ( 54.55K )Number of downloads: 2
 
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st November 2017 - 05:13 AM