Compound interest calculation in a query

  • Thread starter Thread starter Ray Gunderson
  • Start date Start date
R

Ray Gunderson

I use a query to age accounts receivable but need help in
calculting the interest on past due receivables. We want
to calculate the interest compounded daily on the past
due receivables
 
Ray Gunderson said:
I use a query to age accounts receivable but need help in
calculting the interest on past due receivables. We want
to calculate the interest compounded daily on the past
due receivables
Click to expand...

Open any Access module, click on Help. Open the index or search and type:
"payment" without the quotes and you get a list of all the financial
functions. I believe PMT() may be the one you're looking for.

Do the same for the word "date" and look up the DateDiff function to get
specific information on how to calculate the past-due time.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
If you really mean compounded daily that's quite different and fascinating.

Simple interest would mean you apply the datdiff number of days multiplying
by the principal and compute total interest. You could put that right in
the query as an expression.

However, if you want compounded daily you would have to increase the
principal by every day's interest, apply today's interest rate and cumulate
a new and higher principal. You would compute that using a Function like
below using a For .. Step for each of the Date Diff accumulating the
principal and interest as you step through. NOTE I don't believe such an
interest computation is permissible in the USA and doubt it was specifically
agreed to. That's what the usury laws ended unless you're a loan shark who
is also an ACCESS developer. I think simple interest should be punishing
enough.

---------------
'Used for installment payments

Option Compare Database
Option Explicit

Public Function cumipmt(ratec, nperc, pvc, start_period, end_period,
Optional periodendbeg) As Currency
Dim periodc As Integer
Dim interestc As Single
interestc = 0
cumipmt = 0

For periodc = start_period To end_period Step 1

interestc = IPmt(ratec, periodc, nperc, pvc, 0, 0)

cumipmt = cumipmt + interestc

Next
End Function
 
Back
Top