Nerak -
I would use a function for this (VBA code) in a general module. That way
you can call it from any query or form or report you want.
You would need to pass in the start date, end date, principal, interest
rate, and compounding_Frequency, and return the interest earned. If the
compounding frequency is fixed (e.g. you ALWAYS compound monthly or ALWAYS
compound daily, then you don't need to pass it in, just set it in the
function).
Depending on your need for accuracy and your business rules, you may need to
decide if compounding date is important. In other words, if you compound
monthly, do you always credit the earned interest on the last day of the
month, or do you credit it on the month anniversary of the start date? That
may not be an issue for most exercises.
Here is one to get you started. It assumes compounding monthly (12 times a
year). Open a new module and copy/paste this in (you may need to correct for
wrapping in the posting):
Public Function GetCmpdInterest(StartDate As Date, EndDate As Date, PrincAmt
As Double, IntRate As Double) As Double
'This will calculate compound interest assuming monthly compounding.
'This is a straight mathematical calculation, not taking into account any
fixed accrual dates.
Dim NumPeriods As Double 'Some some accrual methods would want this to be
integer
Dim CompoundInterest As Double
NumPeriods = (EndDate - StartDate) * 12 / 365 'the 12 indicates compounding
12 times a year
CompoundInterest = PrincAmt * (1 + IntRate / 12) ^ NumPeriods - PrincAmt
GetCmpdInterest = CompoundInterest
End Function
Then in your query, add a field like this:
CmpdDiv:
GetCmpdInterest([StartDate],[EndDate],[StartingPrincipal],[AnnualInterestRate])
Note the AnnualInterestRate should be entered like 0.06 for a 6% interest
rate.
Try it out and adjust as needed.
--
Daryl S
nerak said:
I am trying to find the best way to formulate for compounding interest with
different start dates and different finish dates but the interest percentage
the same. When I only had a few months I just put a formula in the query
which worked OK but when I went beyond 10 months I got the message 'too
complicated' when I ran the query.
I would like to know if a query is the best place to put this and can I use
macros for this or do I need VBA? I have looked at formula of finance but
that only covers stable totals and interest. Can anyone help please.
Regards nerak
