1. interest calculation

I am trying to write an access databse that will calculate Interest payable on loans. Interest can be calculated a number of times per year so I have set up the calculation to have a from and to date and use datediff to calculate no of days and apportion annual interest accordingly.
Only problem is that I then have to do this for every lender (its a small recreation club whose members lent it money to start up). Anyway I want to have this info put in once and have it calculate for all of the stated periods interest.- Note this must not wipe previous calculations.
Any answers appreciated especially if no words containing more than 4 letters are used

2. Re: interest calculation

Are the loans all at the same rate of interest? Are there any transactions that affect the loan balance BESIDES the interest accrued?

Leaving aside details of formulas and code for the moment, here's how I would handle it, assuming there's no compounding going on within the year:

1. Tables:

A table that holds the transactions, whether they're interest accruals, payments, or whatever. I usually have two tables like this: one for the current fiscal year's transactions and one to archive historical transactions. You only work with the current one, but you need the historical table for other purposes.

A table that contains the individual member/lender information.

A table that contains information about the loans themselves (i.e., rate, loan date, etc.)

A table that contains snapshot loan balances. This would normally be the year-end balance for a fiscal year entered as the beginning balance for the next fiscal year. This simplifies calculations, since you don't have to wade back through years of transactions to figure out the beginning balance for the period.

2. To post interest, first create an array to hold the individual loan information, including the lenderID, the beginning balance for the year, the interest rate, the interest accrued to date, interest paid to date, and the net total of any (non-interest) transactions that might affect the loan balance. Populate the array with data.

2. Calculate the elapsed days from the beginning of the fiscal year to today. You always accrue interest through the previous day, so that will give you the right number of days, at least until you get to year-end.

3. Loop through the array. For each lender, calculate the correct accrued interest for the total elapsed days. If there have been transactions affecting the loan balance, you'll have to calculate the interest up to each transaction based on the balance prior to the transaction and total them. Post the difference between that total interest amount and what was previously accrued. I would include the number of days in the record each time I posted interest. That way, you could total the days posted and subtract it from the elapsed days to get the current number of days. I usually also include the loan balance on which the interest was calculated for that number of days (it saves a lot of head scratching later).

3. Re: interest calculation

Thanks for you response - but I checked and array (along with many other words) has more than 4 characters. I am an absolute beginner with this and creating an array made no sense to me nor did the loop through process - Could you elucidate with little words please

4. Re: interest calculation

Umm, in that case, you're going to have a little trouble creating this. I would recommend getting yourself some beginning Access books and studying hard. Most of the words do have more than 4 letters, unfortunately.

Meanwhile, try using a make-table query to create a temporary table instead of an array. Put the information I suggested for the array into the temporary table, one record for each lender's loan. You loop throught the table (or array) by using either a Do While or a For loop. With a table, you would open a recordset (rst) based on the table. Then you would write something like this:

Do While Not rst.EOF
'/This tells it to stop when it
'/gets to the end of the table

'<< here is where you read the information
'<< from the recordset and do your calculation
'<< you would need either a second recordset
'<< or an append query to post the interest

rst.MoveNext
'/This tells it to go to the
'/next record in the recordset
Loop

'/you clean up your resources by
'/destroying the object variables
rst.Close
Set rst = Nothing

5. Re: interest calculation

Thanks for the help - looks like my attempts at humour re 4 letter words backfired.
I am probably further down the track on this issue than it appears:
I have the database working quite well (by my standards) with tables for investors, interest calculations, cheque payments etc.
I can have the database calculate date differences between any two dates and apply the appropriate proportional interest rate, have as many individual payments against that calculation as needed etc etc.
What I am really looking for is some efficiency. The way I have it set up, I can have individual calculation dates for each investor/investment. Whilst this is very flexible, it is also tedious as I have to update each investors date range individually. Before I converted this from Works, I could just have one start and finish date that applied to all investments. I know the relaional nature of Access means this method wont work but wondered if there was an easily understood method that would work

6. Re: interest calculation

You have to understand--I used to be a banker and bankers take interest calculations very seriously.[img]/w3timages/icons/grin.gif[/img]

Actually, anything you did in Works was spreadsheet-based, and no, there is no real equivalent in a relational database.[img]/w3timages/icons/sad.gif[/img] The spreadsheet model is easy to understand but very limited. The relational model is much harder to understand because it's not really intuitive, but it's almost unlimited in what you can do with it.

Unfortunately, to use Access effectively, you're going to have to learn something about code. Macros lack the flexibility and error trapping of code and can take as much effort as writing code if you want to do them well. There just isn't any alternative unless you contract with someone to write you the code to handle it.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•