Query to Calculate a late payment for each 30 days late

  • Thread starter Thread starter MMJII
  • Start date Start date
M

MMJII

Hello all,
Fist Happy holidays to all!
Next
I have a query that calcs a late charge if the datefinished is greater than
or = 30 days.
My problem is I need to figure out a way to perform this calc for each 30
days that the payment is late.
Any ideas are greatly appreciated.

=IIf(Now()-[datefinished]<30 And [amount
due]>0,0,(([Subtotal])*1.5)/100)+[Sales Tax]

TIA
MMJII
 
What is the amount of the late fee per month?

You likely can calculate a late fee using an expression similar to this
(assuming that the late fee is 1.5% of the amount due (without including
previous late fees) each month):

=[Subtotal]*(0.015*(DateDiff("d", [datefinished], Now()) \ 30))
 
Ken,

Thank you for your time & knowledge.

The late fee is 1.5% of the outstanding balance including previous late
fees.
every thirty days.
example

1/1/04 - Bal due 100.00
2/1/04 - Bal due 100.00 x 1.5% = 101.50
3/1/04 - Bal due 101.50 x 1.5% = 103.02 etc...
MMJ II
Ken Snell said:
What is the amount of the late fee per month?

You likely can calculate a late fee using an expression similar to this
(assuming that the late fee is 1.5% of the amount due (without including
previous late fees) each month):

=[Subtotal]*(0.015*(DateDiff("d", [datefinished], Now()) \ 30))


--
Ken Snell
<MS ACCESS MVP>

MMJII said:
Hello all,
Fist Happy holidays to all!
Next
I have a query that calcs a late charge if the datefinished is greater than
or = 30 days.
My problem is I need to figure out a way to perform this calc for each 30
days that the payment is late.
Any ideas are greatly appreciated.

=IIf(Now()-[datefinished]<30 And [amount
due]>0,0,(([Subtotal])*1.5)/100)+[Sales Tax]

TIA
MMJII
Click to expand...
Click to expand...
 
In that case, this should work:

LateFee = [Subtotal]*(1.015^(DateDiff("d", [datefinished], Now()) \ 30) - 1)

--
Ken Snell
<MS ACCESS MVP>

MMJII said:
Ken,

Thank you for your time & knowledge.

The late fee is 1.5% of the outstanding balance including previous late
fees.
every thirty days.
example

1/1/04 - Bal due 100.00
2/1/04 - Bal due 100.00 x 1.5% = 101.50
3/1/04 - Bal due 101.50 x 1.5% = 103.02 etc...
MMJ II
Ken Snell said:
What is the amount of the late fee per month?

You likely can calculate a late fee using an expression similar to this
(assuming that the late fee is 1.5% of the amount due (without including
previous late fees) each month):

=[Subtotal]*(0.015*(DateDiff("d", [datefinished], Now()) \ 30))


--
Ken Snell
<MS ACCESS MVP>

MMJII said:
Hello all,
Fist Happy holidays to all!
Next
I have a query that calcs a late charge if the datefinished is greater than
or = 30 days.
My problem is I need to figure out a way to perform this calc for each 30
days that the payment is late.
Any ideas are greatly appreciated.

=IIf(Now()-[datefinished]<30 And [amount
due]>0,0,(([Subtotal])*1.5)/100)+[Sales Tax]

TIA
MMJII
Click to expand...
Click to expand...
Click to expand...
 
Well, the math to do this is
balance due times
1 plus the percentage raised to the power of the number of periods involved.

BalanceDue * (1.015 ^ (DaysOverdue/30))

SO, if you want it to use entire 30 day periods only

(DateDiff("d",DateFinished,Date())\30)

Subtotal * (1.015^(DateDiff("d",DateFinished,Date())\30))

Ken,

Thank you for your time & knowledge.

The late fee is 1.5% of the outstanding balance including previous late
fees.
every thirty days.
example

1/1/04 - Bal due 100.00
2/1/04 - Bal due 100.00 x 1.5% = 101.50
3/1/04 - Bal due 101.50 x 1.5% = 103.02 etc...
MMJ II
Ken Snell said:
What is the amount of the late fee per month?

You likely can calculate a late fee using an expression similar to this
(assuming that the late fee is 1.5% of the amount due (without including
previous late fees) each month):

=[Subtotal]*(0.015*(DateDiff("d", [datefinished], Now()) \ 30))


--
Ken Snell
<MS ACCESS MVP>

MMJII said:
Hello all,
Fist Happy holidays to all!
Next
I have a query that calcs a late charge if the datefinished is greater than
or = 30 days.
My problem is I need to figure out a way to perform this calc for each 30
days that the payment is late.
Any ideas are greatly appreciated.

=IIf(Now()-[datefinished]<30 And [amount
due]>0,0,(([Subtotal])*1.5)/100)+[Sales Tax]

TIA
MMJII
Click to expand...
Click to expand...
Click to expand...
 
John, and Ken
Many thanks for the math, and query lessons.

It is very much appreciated.

Have a Happy New Year !!!!
MMJ II


John Spencer (MVP) said:
Well, the math to do this is
balance due times
1 plus the percentage raised to the power of the number of periods involved.

BalanceDue * (1.015 ^ (DaysOverdue/30))

SO, if you want it to use entire 30 day periods only

(DateDiff("d",DateFinished,Date())\30)

Subtotal * (1.015^(DateDiff("d",DateFinished,Date())\30))

Ken,

Thank you for your time & knowledge.

The late fee is 1.5% of the outstanding balance including previous late
fees.
every thirty days.
example

1/1/04 - Bal due 100.00
2/1/04 - Bal due 100.00 x 1.5% = 101.50
3/1/04 - Bal due 101.50 x 1.5% = 103.02 etc...
MMJ II
Ken Snell said:
What is the amount of the late fee per month?

You likely can calculate a late fee using an expression similar to this
(assuming that the late fee is 1.5% of the amount due (without including
previous late fees) each month):

=[Subtotal]*(0.015*(DateDiff("d", [datefinished], Now()) \ 30))


--
Ken Snell
<MS ACCESS MVP>

Hello all,
Fist Happy holidays to all!
Next
I have a query that calcs a late charge if the datefinished is greater
than
or = 30 days.
My problem is I need to figure out a way to perform this calc for
Click to expand...
each
30
days that the payment is late.
Any ideas are greatly appreciated.

=IIf(Now()-[datefinished]<30 And [amount
due]>0,0,(([Subtotal])*1.5)/100)+[Sales Tax]

TIA
MMJII
Click to expand...
Click to expand...
Click to expand...
 
Back
Top