daily interest calculation

  • Thread starter Thread starter jmd
  • Start date Start date
J

jmd

I have developed a query to calculate a running total for
a simple savings account.

Field Name: TransDate
Field Name: Transaction
Field Name: Transtype (-1 for withdrawals and 1 for
deposits)

Bal: DSum("[transaction]*
[transtype]","tblsavingstable","DatePart('m', [transdate])
<=" & [AMonth] & " And DatePart('d', [transdate])<=" &
[Aday] & "")

AMonth: DatePart("m",[TransDate])

ADay: DatePart("d",[transdate])

I need the interest to calculate daily at a rate of .004
with or WITHOUT transactions. I've tried a variety of
combinations of queries, reports, and VB with no
success. Any help would be much appreciated.
 
Are you saying some records have a transaction quantity of Null? And that
that is what is causing calculation problems? If yes, use the Nz function to
replace Null with a value (here, I'm using zero):

Bal: DSum("Nz([transaction], 0)*
[transtype]","tblsavingstable","DatePart('m', [transdate])
<=" & [AMonth] & " And DatePart('d', [transdate])<=" &
[Aday] & "")

If I'm misunderstanding what you seek, post back with clarifications and
examples of what you have as data and what the results should be.
 
Thank you for taking a look.

Are you saying some records have a transaction quantity of Null? - No, this
is not the problem

Example Below:

TransDate Trans Running Total Interest (.004 compounded daily)
5/1/2004 $700.00 $700.00 #1
5/2/2004 $25.00 $675.00
5/3/2004 $15.00 $660.00
5/4/2004 $50.00 $710.00
5/5/2004 $95.00 $615.00
5/7/2004 $45.00 $660.00
5/8/2004 $35.00 $625.00
5/9/2004 $0.00 $625.00
5/10/2004 $96.00 $721.00
5/11/2004 $50.00 $671.00
5/12/2004 $0.00 $671.00
5/13/2004 $0.00 $671.00
5/14/2004 $0.00 $671.00
5/15/2004 $0.00 $671.00

#1 - I need the interest (.004) calculated daily and added to the balance,
the subsequent withdrawal/deposit transactions would be applied to the
previous day's closing balance and the interest re-calculated once again. If
the transaction for the day is a 0 or Null, I would need it to calculate from
the previous day's closing balance.

Let me know if I missed anything, I appreciate your assistance

Ken Snell said:
Are you saying some records have a transaction quantity of Null? And that
that is what is causing calculation problems? If yes, use the Nz function to
replace Null with a value (here, I'm using zero):

Bal: DSum("Nz([transaction], 0)*
[transtype]","tblsavingstable","DatePart('m', [transdate])
<=" & [AMonth] & " And DatePart('d', [transdate])<=" &
[Aday] & "")

If I'm misunderstanding what you seek, post back with clarifications and
examples of what you have as data and what the results should be.

--

Ken Snell
<MS ACCESS MVP>

jmd said:
I have developed a query to calculate a running total for
a simple savings account.

Field Name: TransDate
Field Name: Transaction
Field Name: Transtype (-1 for withdrawals and 1 for
deposits)

Bal: DSum("[transaction]*
[transtype]","tblsavingstable","DatePart('m', [transdate])
<=" & [AMonth] & " And DatePart('d', [transdate])<=" &
[Aday] & "")

AMonth: DatePart("m",[TransDate])

ADay: DatePart("d",[transdate])

I need the interest to calculate daily at a rate of .004
with or WITHOUT transactions. I've tried a variety of
combinations of queries, reports, and VB with no
success. Any help would be much appreciated.
Click to expand...
Click to expand...
 
I am assuming that you're not storing the interest amount in the table, so
you want to calculate the interest as a calculated field in a query.

Something like this may work (not tested!) to give you the daily interest
amount based on a transaction balance, but it won't automatically sum the
interest to the balances for compounding effect:

InterestAmount: (SELECT TOP 1 Nz(T.[Running Total], 0) *
DateDiff("d", T.TransDate, [tblsavingstable].TransDate) FROM
[tblsavingstable] AS T WHERE T.TransDate <
[tblsavingstable].TransDate) * 0.004


To get the compounding, I think you'll need to have a temporary table into
which you can write the Running Total values and then calculate an interest
for one day, and then sum it to that Running Total amount for calculating
the interest for the next period. Very tedious.

I don't know of a way to do this in a single query at this time.
--

Ken Snell
<MS ACCESS MVP>





jmd said:
Thank you for taking a look.

Are you saying some records have a transaction quantity of Null? - No, this
is not the problem

Example Below:

TransDate Trans Running Total Interest (.004 compounded daily)
5/1/2004 $700.00 $700.00 #1
5/2/2004 $25.00 $675.00
5/3/2004 $15.00 $660.00
5/4/2004 $50.00 $710.00
5/5/2004 $95.00 $615.00
5/7/2004 $45.00 $660.00
5/8/2004 $35.00 $625.00
5/9/2004 $0.00 $625.00
5/10/2004 $96.00 $721.00
5/11/2004 $50.00 $671.00
5/12/2004 $0.00 $671.00
5/13/2004 $0.00 $671.00
5/14/2004 $0.00 $671.00
5/15/2004 $0.00 $671.00

#1 - I need the interest (.004) calculated daily and added to the balance,
the subsequent withdrawal/deposit transactions would be applied to the
previous day's closing balance and the interest re-calculated once again. If
the transaction for the day is a 0 or Null, I would need it to calculate from
the previous day's closing balance.

Let me know if I missed anything, I appreciate your assistance

Ken Snell said:
Are you saying some records have a transaction quantity of Null? And that
that is what is causing calculation problems? If yes, use the Nz function to
replace Null with a value (here, I'm using zero):

Bal: DSum("Nz([transaction], 0)*
[transtype]","tblsavingstable","DatePart('m', [transdate])
<=" & [AMonth] & " And DatePart('d', [transdate])<=" &
[Aday] & "")

If I'm misunderstanding what you seek, post back with clarifications and
examples of what you have as data and what the results should be.

--

Ken Snell
<MS ACCESS MVP>

jmd said:
I have developed a query to calculate a running total for
a simple savings account.

Field Name: TransDate
Field Name: Transaction
Field Name: Transtype (-1 for withdrawals and 1 for
deposits)

Bal: DSum("[transaction]*
[transtype]","tblsavingstable","DatePart('m', [transdate])
<=" & [AMonth] & " And DatePart('d', [transdate])<=" &
[Aday] & "")

AMonth: DatePart("m",[TransDate])

ADay: DatePart("d",[transdate])

I need the interest to calculate daily at a rate of .004
with or WITHOUT transactions. I've tried a variety of
combinations of queries, reports, and VB with no
success. Any help would be much appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top