Add Penalties and Arrears to annual Fee (1 Viewer)

DJaftha

New member
Local time
Today, 12:52
Joined
Mar 5, 2020
Messages
3
Hello All

I have created a "Vehicle Licence Renewal" database and I'm stuck with a query. I need to calculate the penalties and arrear fees in the following way:

Lets say the licence expired the end of January 2020:

If todays date is 23 February then 10% of the licence amount must be added.(From the 1st to the 22nd is a grace period):

If todays date is 01 March then add above for February and (10% + 1/12th for March) must be added to the licence amount and so on for every month after that.

I hope there is simple answer for this (there should be) but my knowledge is very limited w.r.t formulas and vba.

Thank you all for looking and responding

Regards
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:52
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! The trick is in having a formula for calculating a value. Once you have that, everything else should follow. To have a formula, you'll need rules. What are your fee rules? You may have already mentioned some of them, but maybe you can explain more.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:52
Joined
May 7, 2009
Messages
19,175
why would you add 10% to license amount, when the license will expire yet on the 31 of January.
 

DJaftha

New member
Local time
Today, 12:52
Joined
Mar 5, 2020
Messages
3
Hi. Welcome to AWF! The trick is in having a formula for calculating a value. Once you have that, everything else should follow. To have a formula, you'll need rules. What are your fee rules? You may have already mentioned some of them, but maybe you can explain more.
Hi theDBguy, thanks for your attention to this. The policies around licence renewal is that once a vehicle licence expires, the owner has 22 days grace to update the licence and then from the 23 a penalty for the whole month will be added at 10% of the annual fee. e.g.
Vehicle expires the 31 January 2020. Annual fee is R408.00. Up untill 22 February 2020 is still a grace period to pay without any penalties.
From 23 February a 10% penalty will be added (R40.80c) = R448.80c
If the owner only renews the licence on 1 March 2020 a further 10% penalty (R40,80c) + arrear fee at 1/12th (R34.00) must be added = R448.80 + R40.80 + R34.00 = R523.60.

The penalty and arrear will be added to the amount on the 1st of every month.

I hope this helps out.
 

DJaftha

New member
Local time
Today, 12:52
Joined
Mar 5, 2020
Messages
3
why would you add 10% to license amount, when the license will expire yet on the 31 of January.
Hi arnelgp, the 10% is only added once the grace period expired after the licence expiry dated, like 23 of February.
 

HiTechCoach

Well-known member
Local time
Today, 05:52
Joined
Mar 6, 2006
Messages
4,357
There are two parts to the calculation:

1) If it is paid 23 or more days after the due date add 10% of the renewal fee

2) for each "month" past the due date, less a grace month, you add one month's fee (1/12 of the annual)


Code:
Public Function CalcPastDueFees(DueDate As Date, DatePaid As Date, RenewalFeeAnnual As Currency) As Currency

Dim PastDueFees As Currency
Dim MonthsPastDue As Integer

PastDueFees = 0

If (DatePaid - DueDate) > 22 Then

   ' 1)  If it is paid 23 days or more  after the due date add 10% of the renewal fee

   PastDueFees = PastDueFees + (RenewalFeeAnnual * 0.1)

   ' 2) for each "month" past the due date, less a grace month,  you add one month's fee (1/12 of the annual)

     MonthsPastDue = DateDiff("m", DueDate, DatePaid) - 1 ' subtract 1 for the grace month

     If MonthsPastDue > 0 Then
         PastDueFees = PastDueFees + ((RenewalFeeAnnual / 12) * MonthsPastDue)
     End If

End If

CalcPastDueFees = PastDueFees

End Function

Test run from the immediate window

Code:
? CalcPastDueFees(#1/31/2020#, #2/22/2020#, 408)
   0

? CalcPastDueFees(#1/31/2020#, #2/23/2020#, 408)
40.8

? CalcPastDueFees(#1/31/2020#, #3/1/2020#, 408)
74.8

? CalcPastDueFees(#1/31/2020#, #4/1/2020#, 408)
108.8
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:52
Joined
May 7, 2009
Messages
19,175
another question.
vehicle will expires on January 2020, re-new and pays all due on November 2020, when is the New expiration? January 2021 or November 2021?
 

HiTechCoach

Well-known member
Local time
Today, 05:52
Joined
Mar 6, 2006
Messages
4,357
another question.
vehicle will expires on January 2020, re-new and pays all due on November 2020, when is the New expiration? January 2021 or November 2021?

When the new renewal is due normally has no impact on the penalty calculation.

Whenever the renewal is paid, it is still for a full year up to the next due date. The renewal due date should not change. Thre is only penalties add for renewing late.
 

Users who are viewing this thread

Top Bottom