Dear ALL
I Want to calculate actual UPR for insurance premium as below
Calculating Earned Premium
To determine Earned premium, we need to look at the length of the policy, and determine how much time has already elapsed.
Earned Premium = Total Premium / 365 * Number of Days Elapsed
For example if a 365 day policy with a full premium payment at the commencement of the insurance has been in effect for 30 days, 30/365 of the premium can be considered as being Earned. This will also mean that 185/365 of the premium would have to be considered unearned.
The same rules apply for policies with a term of more than one year, if someone paid a premium for two years of home insurance and 18 months has elapsed the Insurance company has Earned three quarters the premium.
I use following script to get a monthly premium please help get actual premium as above.
LOAD BCOD,
CLA_CODE,
PRD_CODE,
POLICY_NO,
REF_NO,
DATE(floor(MARKETING_DATE)) as MARKETING_DATE,
MDATE,
YEAR,
MONTH,
NAME,
ME_CODE,
//TRN_DATE,
DATE(floor(TRN_DATE)) as TRN_DATE,
TRAN_YEAR,
TRAN_MONTH,
//POL_PERIOD_FROM,
DATE(Floor(POL_PERIOD_FROM),'DD-MM-YYYY') AS POL_PERIOD_FROM,
SCCOD,
CP,
RS,
TC,
Total Premium,
then i used given below cal
Trandatecalender:
LOAD
DAY(MARKETING_DATE) AS MARKETING_DAY,
DATE(MARKETING_DATE) AS MARKETING_DATE,
DATE(monthstart(MARKETING_DATE), 'DD-MM-YYYY') as DAY_MONTH_YEAR,
WEEK(MARKETING_DATE) AS MARKETING_WEEK,
YEAR(MARKETING_DATE) AS MARKETING_YEAR,
MONTH(MARKETING_DATE) AS MARKETING_MONTH,
MONTH(MARKETING_DATE) &'-'& WEEK(MARKETING_DATE) AS MONTH_WEEK;
LOAD DATE (MARKETING_MINDATE + ITERNO() -1) AS MARKETING_DATE WHILE (MARKETING_MINDATE + ITERNO () -1) <= NUM (MARKETING_MAXDATE);
LOAD
MIN(MARKETING_DATE) AS MARKETING_MINDATE,
MAX(MARKETING_DATE) AS MARKETING_MAXDATE
RESIDENT SALES;
please help get UPR value using above script.
Earned Premium = Total Premium / 365 * Number of Days Elapsed
sagara
