Compounding Interest

  • Thread starter Thread starter nerak
  • Start date Start date
N

nerak

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
 
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.
 
Thank you so much Daryl I truly appreciate your time and clarity in answering
this.

Have a fantastic day!

Daryl S said:
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
Click to expand...
Click to expand...
 
Hi,
I am an IT trainee trying to create a property management system on MS access 2007. The rent escalates annually at a rate of 10% thus formula for working out new rent is similar to compound interest. I am trying to build a code or expression that would automate the rent escalation..., please help me out.
Thanks, Peter.
 
Back
Top