I am trying to find a way to calculate a weighted-average interest payment in Access.
Here is an example:
You have a savings account with changing balance, the balance basically changes every time when you add some cash or withdraw cash. In Excel it would look like this
Balance rate beginning ending days Interest
($$) (%) date date (calculated)
-----------------------------------------------------------------------
500 6% 1/1/07 1/15/07 14 $1.17
300 6% 1/15/07 1/31/07 16 $0.80
total interest: $1.97
Not sure if I need to create a VBA code, or there is another way. Also I would like to be able to filter the period for which I want to calculate the interest payment for.
Like from 1/7/07 till 1/18/07 (based on the predetermined interest rate, and the respective balance). I have no idea if this would be a big headache to create; it could be a simple thing, but I do struggle… Any help is appreciated !!!
Rgds.
Atol
Also I would like to be able to filter the period for which I want to calculate the interest payment for.
Like from 1/7/07 till 1/18/07 (based on the predetermined interest rate, and the respective balance).
If you already have the data in Access just like you have it written above (via separate records for each period of different balances), I would create a query to house all of the calculated data you need along with the original data. The parameters of the query will be the dates that you input. Then you can call the query to give you the information whenever you need it.
According to your data, $500 invested at 6% annually for 14 days will yield $1.17. Thus, the daily accrued interest is 1.17/14 = $.08. (NON-weighted). This is not relevant, but I did it anyway, sorry.
In order to eliminate the headache of working with SQL aggregates, I would create a one column query first that sums the [days] field based on the parameters that you input...
FirstQuery
Code:
SELECT SUM((DateDiff("d",[begin date],[end date])) AS [total period days]
FROM table
WHERE (((table.[begin date] <= [parameter1]) AND (table.[end date] > [parameter1]))
OR ((table.[begin date] <= [parameter2]) AND (table.[end date] > [parameter2])));
Use the IIF function to calculate the weighted average
Code:
SELECT [balance], [rate], [begin date], [end date], [days], [interest (calculated)],
IIF([parameter1] Between [begin date] AND [end date], (([days] / [total period days])*[interest (calculated)]), NULL) AS [1st balance interest],
IIF([parameter2] Between [begin date] AND [end date], (([days] / [total period days])*[interest (calculated)]), NULL) AS [2nd balance interest]
FROM table, FirstQuery
WHERE (((table.[begin date] <= [parameter1]) AND (table.[end date] > [parameter1])) OR
((table.[begin date] <= [parameter2]) AND (table.[end date] > [parameter2])));
RESULT
**[1st balance interest] = Shows the interest accrued (weighted on the fraction of days / total period days) on the first balance.
**[2nd blance interest] = Shows the interest accrued (weighted on the fraction of days / total period days) on the second balance.
Parameter1 = Start Date
Parameter2 = End Date
The information I provided assumes that there are only 2 periods of differing balances. You can continue the pattern in the query to calcuate as many fields as you want, but I'm sure the SQL will become quite cumbersome. If you write it for calculating 5 different columns of weighted averages, and you enter only 2 parameters, your 3 fields that did not receive parameters will just be blank, because they separated by the "OR" operator. I am also sure that there is a better way to loop through this sort of thing in Visual Basic, but taking the data and the math that you need to work with and looping through a module is probably not something anyone would like to attempt. If I was an expert in the language, I would probably turn you down.
Set up your parameters to read input values from form controls like text boxes. Doing it this way too will allow you to view a total of the weighted-averages. Use the DSum function in a text box to sum all the fields that supply values from IIF functions.
Also, the queries above assume that you already have a query in your database that is set up exactly like your Excel example...
Thank you for the fast and valuable response. For sure I will need some time to absorb all those nice hints you provided. I realize that this task is a bit complex since there are too many variables that can change at any point of time - balances, interest rates, picking verious periods, etc... I am not good with VBA and could not figure it out.
Thanks again.
