Query for calculating interest on account withdrawals

[bigdavidt]

I am working on a query to calculate loan interest and print it out as part of a report. There is a billing date on the 16th of each month. If money is drawn from the available loan amount after the last billing date, a calculation is made based on the number of days from the billing date to the withdrawl date, the amount of money taken out before the withdrawl date, and the interest rate. Another calculation must also be made, only the number of days involved runs from the date of the withdrawal to the present, and it involves the new amount of withdrawals. There can be multiple withdrawals of varying amounts, and the times can also vary. is there a way to perform such a calculation in a query, such as a crosstab query?

 

[JerryKlmns]

Table holding transactions (withdrawals)
DateWhen Date/Time
Withdrawal Currency
AccountNo Text (?) [Indexed Yes Duplicates Ok]

-------------------------------------------------------

Table holding Interest rate for every Account
AccountNo Text (?) [Primary Key]
InterRate Number Double

-------------------------------------------------------

Query to calculate interests (Days in Year = 365) on every transaction

SELECT Trns.AccountNo, Trns.Withdrawal, Trns.DateWhen, Date()-[DateWhen] AS Days, Rates.InterRate, Round((Date()-[DateWhen])*[Withdrawal]*[InterRate]/365,2) AS Interest
FROM Rates INNER JOIN Trns ON Rates.AccountNo = Trns.AccountNo;

 

[bigdavidt]

Your advice is good, but there is one problem I apparently did not get across. The query has to calculate interest based on the amount of money withdrawn for a time period beginning with the last billing date and ending with the date of the first withdrawal following the last billing date. If there is a second withdrawal, an additional calculation has to be performed based on the new total total created by the first withdrawal, for the time period beginning with the first withdrawal and ending with the second withdrawal. Then a calculation has to be formed based on the new total following the second withdrawal, for the time period beginning with the second withdrawal and ending with the present date.

Son you have Time Period One (the number of days from the last billing to the date of the first transaction) and Amount One (the balance as of the last billing date). Then you have Time Period Two (the number of days from the first billing date to the second billing date) and Amount Two (the balance created by the first transaction). Then you have Time Period Three (the number of days from the second billing date to the present) and Amount Three (the balance created by the second transaction).

Following a billing period there can be one transaction, two transactions, three transactions, several transactions, or none at all. The total interest resulting from these transactions is what the end user is looking for.

This reminds me of story problems

Have a good day.

 

[JerryKlmns]

The interest rate calculation is based on amount withdrawn and not on account balance.So if you define the time span for the query to run you ´ll get for that period the calculation of interest. The time span should be from previous billing till the next billing date

Now the query running is a parameter query that prompts for the last billing date and the new billing date

SQL

PARAMETERS [Prev_Boll] DateTime, [New_Bill] DateTime;
SELECT Trns.AccountNo, Trns.Withdrawal, Trns.DateWhen, [New_Bill]-[DateWhen] AS Days, Rates.InterRate, Round(([New_Bill]-[DateWhen])*[Withdrawal]*[InterRate]/365,2) AS Interest
FROM Rates INNER JOIN Trns ON Rates.AccountNo = Trns.AccountNo
WHERE (((Trns.DateWhen) Between ([Prev_Boll]+1) And [New_Bill]));

 

[bigdavidt]

This is an improvement, but does not quite solve the problem. What has to happen is that the query must perform ALL these calculations, the results added, and then presented as part of a report file in the form of a letter with information to update a client or customers on the status of an account. The end users are not particularly computer savvy.

Thank you again.