roosterup
asked on
Free RPG - Calculate prorated amount of annual premium, account for leap years
I need to calculate a prorated amount of annual premium in free rpgle and account the number of days in leap years.
say a company's annual premium would be $1000 and they sign up on 11/01. I only need to charge them for 61 days of the yearly premium.
anyone have any date math examples for doing this?
say a company's annual premium would be $1000 and they sign up on 11/01. I only need to charge them for 61 days of the yearly premium.
anyone have any date math examples for doing this?
In what language?
IBM i DB2 SQL:
ILE RPG (didn't test, but hopefully you get the idea):
- Days from current date to end of current year.
values days(year(current_date) || '-12-31') - days (current_date);
- Days from DATE column
select days(year(current_date) || '-12-31') - days(PolicyEffectiveDate) as ProrateDays from sometable;
- Verify properly handles leap years, including special rules for years divisible by 100 and 400
values days('2016-03-01') - days ('2016-02-01') --expect 29
values days('2017-01-01') - days ('2016-01-01') -- normal leap year - expect 366
values days('1900-12-31') - days ('1899-12-31') -- "100 rule" - expect 365
values days('2000-12-31') - days ('1999-12-31') -- "400 rule" - expect 366
values days(year(current_date) || '-12-31') - days (current_date);
- Days from DATE column
select days(year(current_date) || '-12-31') - days(PolicyEffectiveDate) as ProrateDays from sometable;
- Verify properly handles leap years, including special rules for years divisible by 100 and 400
values days('2016-03-01') - days ('2016-02-01') --expect 29
values days('2017-01-01') - days ('2016-01-01') -- normal leap year - expect 366
values days('1900-12-31') - days ('1899-12-31') -- "100 rule" - expect 365
values days('2000-12-31') - days ('1999-12-31') -- "400 rule" - expect 366
ILE RPG (didn't test, but hopefully you get the idea):
dcl-s endOfYear DATE(*ISO);
dcl-s effectiveDate DATE(*ISO) INZ('2017-03-17');
dcl-s proRateDays INT(5);
endOfYear = %date(%char(%subdt(%date() : *YEARS) :*ISO) || -12-31');
proRateDays = %diff( endOfYear : effectiveDate : *DAYS);
dcl-s effectiveDate DATE(*ISO) INZ('2017-03-17');
dcl-s proRateDays INT(5);
endOfYear = %date(%char(%subdt(%date()
proRateDays = %diff( endOfYear : effectiveDate : *DAYS);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Do you want it to be "61 days of a 365-day year because 2017 isn't a leap year" or "61 days of a 365.25-day year because that's a reasonable average over many years"? That is, do you want proration to be different for actual leap years or the same for every year?
Since we're apparently dealing with some sort of insurance, it is possible that there is governing law that controls the calculation - and that could even vary by jurisdiction. Might be tIme to email the legal team ...
Hi Roosterup, I see have your solution, but I would add a leap year checker like so:
d Dec31PolYear s d
d PolStrDate s d
d LeapDay s 10
d MultiPlier s 7 6 (7.6)
d DaysLeftInYear s 3 0
d OneYearPrimium s 10 2
// Base Primium $10,0000 per year
OneYearPrimium = 10000;
// use %DIFF(Dec31CurYear : PolStrDate : *Days)
Dec31PolYear = %Date(%CHAR(%SubDT(PolStrD ate:*YEARS )) +
'-12-31' : *iso);
DaysLeftInYear = %DIFF(Dec31PolYear : PolStrDate : *Days) + 1;
LeapDay = %CHAR(%SubDT(PolStrDate:*Y EARS)) + '-02-29' ;
test(ed) LeapDay;
if Not %Error ;
// It's a leap year
MultiPlier = DaysLeftInYear / 366;
else;
// no Leap year
MultiPlier = DaysLeftInYear / 365;
endif;
OneYearPrimium = OneYearPrimium * MultiPler;
d Dec31PolYear s d
d PolStrDate s d
d LeapDay s 10
d MultiPlier s 7 6 (7.6)
d DaysLeftInYear s 3 0
d OneYearPrimium s 10 2
// Base Primium $10,0000 per year
OneYearPrimium = 10000;
// use %DIFF(Dec31CurYear : PolStrDate : *Days)
Dec31PolYear = %Date(%CHAR(%SubDT(PolStrD
'-12-31' : *iso);
DaysLeftInYear = %DIFF(Dec31PolYear : PolStrDate : *Days) + 1;
LeapDay = %CHAR(%SubDT(PolStrDate:*Y
test(ed) LeapDay;
if Not %Error ;
// It's a leap year
MultiPlier = DaysLeftInYear / 366;
else;
// no Leap year
MultiPlier = DaysLeftInYear / 365;
endif;
OneYearPrimium = OneYearPrimium * MultiPler;