Different payment Types

[geet72]

I am working in a Insurance database with policy details, premium Amount and start and end date

I have a table with payment categories as Annual, Semi Annual, Quarterly (Equal), Quarterly (Other) etc

When the payment is sent out, if the policy is Annual, the payment should be divided by 12, if the payment is qtr, it should be divided only for the 3 month etc

Should I use a query or set up another table defining the division to make it work. Any suggestions

Regards

 

[MagicMan]

All depends on the volitility of the Payment Type. If this will not change (Fixed in stone) then hard code the division values. If the Payment Type can change, or new types can be added, have the division, description, etc in an Administrator table. This will save program changes when a change does occur.
Smiles
Bob

 

[geet72]

Thanks for the reply. Can you give me a example of the fields that I would need if I were to set this up as table

thanks

Regards

 

[MagicMan]

Sorry, but it is your application. I do not know your requirements. The table, if you feel you need it, should be hidden and Administrator access only. The fields... would be:
PaymentTypeID (Key autonumber)
PaymentTypeDescription (if you need to dynamically modifiy a form or report to show the type otherwise no need)
PayementTypeFrequency
Basically just add frequency to the existing table.

 

[geet72]

Thanks. Got it

Regards
Geetha

 

[GaryPanic]

ok in your table have

Paymenttype
divisionrate

so Annual would have a divsion rate of 1

Semi Annual (I presume once every 6 months)
would have a divison rate of 2
quaterly
4
and monthly 12

payment dates would need to be look at - but you have not mentioned these nor how you intend diary these onwards - as you will need some audit trail and this will be dictated by the numbers we are talking about

a sepearate diarypaymenttable might be needed
with paymenttype and it gerneate 1 entery for annual (I would of thought payment with order ont his one) and 2 enteries for semi- 4 for quaterly and 12 for monthly all running of your inception date - with renewal ability to run off inception date .

my view would be diary table option
fields on it payment due on
annual 1 entery 30 days fro inception (or whatever payment terms you run)
paid on
paid by (Credit card , cheque, Bank transfer, other )

 

[MagicMan]

Good point...all depends on the application...the math is the simple part...the requirements dictate the math. EachPaymentAmt=TotalAnnualPaymentAmt/PaymentTypeFrequency (rounded appropriately).

*Rounded appropriately....in the financial institutions, many will not send or request individual payment below a threshold. The last payment always has the rounding offset, the remaining are all rounded down. For example, a check for under $10 will not be issued. Total amount to be paid...$55, Monthly payments...1st Month payment $10, 2nd $10, third $10, fouth $10, Fifth $15. The checks are paid on Monthly cycles 1, 4, 7, 10, and 12. (Jan, Apr, Etc).
In the financial industry, a payment is not a simple thing.
Smiles

 

[GaryPanic]

I am into insurance - luckly its all up front premium payments - but occasionaly stages in to 4-5 payment have i have built a diary system which works (thank god)

the other alternative is to invoice the payments in one hit
so 12 invoices with with increasing payment dates on it - good point premium debited into your accoutn - bad point debtors list gets quite long as you have people owing you monies over 12 months - depends on the business plan, my company wants the premium debted straight away so i would do 4-5 payments invocies anddairy and settle as i go along -statemtns look iffy - but i warn the clients in advance - ignore them and await my email statement/letter - which they are more than happy with .

but it is all about numbers if we are talking large numbers here then sit back and think this one through - cos if you get it wrong its gonna be a swine to resolve .

any idea on the number of policies we are looking at and a breakdown of
annaul, semi. quaterly etc

 

[GaryPanic]

Good point...all depends on the application...the math is the simple part...the requirements dictate the math. EachPaymentAmt=TotalAnnualPaymentAmt/PaymentTypeFrequency (rounded appropriately).

*Rounded appropriately....in the financial institutions, many will not send or request individual payment below a threshold. The last payment always has the rounding offset, the remaining are all rounded down. For example, a check for under $10 will not be issued. Total amount to be paid...$55, Monthly payments...1st Month payment $10, 2nd $10, third $10, fouth $10, Fifth $15. The checks are paid on Monthly cycles 1, 4, 7, 10, and 12. (Jan, Apr, Etc).
In the financial industry, a payment is not a simple thing.
Smiles

interesting - I (personally ) do the rounding at the beginning - no regs on this - but i do it on the basis that i am ineffect giving credit and as such want the problematic maths sorted at inception so i would do 15,10,10,10,,10

and the guys in my office all do the same - difference in culture ... as long as we get the monies .

 

[MagicMan]

It is all determined by company policies. These are stated in the contract. Thats why I mentioned real early, "It is your application." Every application is driven by the requirements (Company policies drive requirements!).
Side Note: I wrote a cash out program for a large insurance company that had purchased/consolidated with over 30 other companies. All the policy types and corporate profiles from the past had to be honored. What a nightmare!

 

[geet72]

Hi

Thanks for all the help. This what I have so far

Tables -
Company Details,
Broker details,
Business Entity (Different businesses within the company have their own identification,
Policy Categories for the limits like per accident, per occurence
Policy Claim Categories such as Claims made type or occurence type
Polict Details like number, start date, end date etc
Policy Limits - limits on each policy
Payment categories - Annual, semi annual etc
Payment Details - Payment Date, Amount, Account No, check or wire or CC
Policy Type - Auto, Property etc,
Policy Sub type - Bodily injury, Sabotage coverage etc

95% of the policies are annual, 2% are quarterly with different percentages paid in each qtr such as 40-20-20-20 and 1% are prepaid, 2% are other where it can be 2million in first year, 1 million in second year etc

I am trying to set up a table so that I can get a schedule of payments from a query when they become due based on the policy Start date.

Also, is there a way to set up a renewal option. The policy number is unique in the policy details table. How do I set this up.

Any help is appreciated

 

[MagicMan]

Well, you have a formidable task ahead of you. My approach would be as follows:
1) Pass one (Single query if possible...may need vb code if too complex)
a) Determine the policy payment dates for each payment
b) Determine the payment amount for each payment date (includes applying payment limit)
2) I do not see a table for payment cycle (dates when checks are cut)....
For each payment cycle (every week, every day, every month...not sure)
a) Identify policies with payment dates that fall into this payment cycle
b) Accumulate the total amount to be paid in this cycle
c) Determine and corporate cash flow limitations and identify payments to be defered
d) Finalize payment totals (Authorization to proceed)
e) Create the check file

 

[geet72]

I kind of figured that is what I needed and was half way through it.

Also, can you help with renew option for the policy. The policy number is unique and I am not sure how the auto renewal will work. I am assuming that there should be yes/no button for auto renewal and when the button is clicked, the existing policy should be copied as a new policy. What about the policy number. How do I get around policy number being unique

 

[gemma-the-husky]

for each policy you might want to consider a second table including the payment schedule, rather than just store a payment profile in the policy record

might be easier to allocate payments from your banking system against policies, and would certainly make cash flow projection/arrears management etc easier to manage and monitor

you may also need to consider how you manage policy amendments/variations.
ie halfway through a year,the policyholder increases the insured value, or wants to change his payment method, or profile

Big insurance companies are bound to need stuff like this -


find out every last little oddity at the beginning - because it will be really hard to add them in later - you're probably dealing with thousands of policies, so a 1 percent "strangeness" will result in policy oddities for hundreds of policies - and your system wont be liked/accepted if it cant deal with odd events

 

[MagicMan]

I totally agree with gemma...great suggestion.

 

[geet72]

Also, can you help with renew option for the policy. The policy number is unique and I am not sure how the auto renewal will work. I am assuming that there should be yes/no button for auto renewal and when the button is clicked, the existing policy should be copied as a new policy. What about the policy number. How do I get around policy number bein