I am putting together a simple little calculator that tells me a clients "insurance age” and a backdate Date. A lot of life insurance carriers use “Age Nearest” when it comes to underwriting risk on life insurance. What this means is, if you are within 6 months of your next birth date underwriting will use your next age to underwrite the case: Lets say my birth date is 11/12/1979, I am 33 years, 8 months and 5 days old as of today. So my insurance age is 34. If I want my insurance age to be my actual age of 33 (cost of ins. is cheaper) I need to backdate the application/policy to a date that is 2 months and 6 days before today’s date. So, I am looking for a formula that will tell me what the insurance age is and what the backdate Date is.
Backdating for Insurance purposes
- Thread starter jhemmerly
- Start date
SirJB7
Excel Rōnin
Hi, jhemmerly!
Try this to get the backdated date:
=FECHA.MES(A1;(AÑO(B1-A1)-1900)*12+6) -----> in english: =EDATE(A1,(YEAR(B1-A1)-1900)*12+6)
where A1 is the birth date and B1 the actual or used date.
BTW, I'm not an assurance auditor but should I call it cheating?
Regards!
Try this to get the backdated date:
=FECHA.MES(A1;(AÑO(B1-A1)-1900)*12+6) -----> in english: =EDATE(A1,(YEAR(B1-A1)-1900)*12+6)
where A1 is the birth date and B1 the actual or used date.
BTW, I'm not an assurance auditor but should I call it cheating?
Regards!
First, it depends a little bit on how you determine which date it closer due to leap years. Could say half a year is either 182.5 days or 183 days. I'm going to assume 183, as the solution depends more on structure than value (which you can tweak).
Next, I'm going to annoy my fellow ninja SirJB7 by using the DATEDIF function, because, well, I can. <grin>
My sample data
[pre]
[/pre]
Formula in B2:
=DATEDIF(A2,TODAY()+183,"y")
Formula in C2:
=DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2))-184
Next, I'm going to annoy my fellow ninja SirJB7 by using the DATEDIF function, because, well, I can. <grin>
My sample data
[pre]
Code:
Birthday Age Back date
1-Apr-80 33 29-Sep-12
2-Mar-82 31 30-Aug-12
31-Jan-84 29 31-Jul-12
31-Dec-85 28 30-Jun-13
1-Dec-87 26 31-May-13
31-Oct-89 24 30-Apr-13
1-Oct-91 22 31-Mar-13
31-Aug-93 20 28-Feb-13
1-Aug-95 18 29-Jan-13
1-Jul-97 16 29-Dec-12
1-Jun-99 14 29-Nov-12
1-May-01 12 29-Oct-12
1-Apr-03 10 29-Sep-12
1-Mar-05 8 29-Aug-12
30-Jan-07 6 30-Jul-12
30-Dec-08 5 29-Jun-13
30-Nov-10 3 30-May-13
30-Oct-12 1 29-Apr-13Formula in B2:
=DATEDIF(A2,TODAY()+183,"y")
Formula in C2:
=DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2))-184
@SirJB7
Not sure your formula is working right. Using same sample data:
[pre]
[/pre]
some of your backdates go into the future...which I don't think is intent.
Not sure your formula is working right. Using same sample data:
[pre]
Code:
Birthday Actual Backdate
1-Apr-80 7/17/2013 1-Oct-13
2-Mar-82 7/17/2013 2-Sep-13
31-Jan-84 7/17/2013 31-Jul-13
31-Dec-85 7/17/2013 30-Jun-13
1-Dec-87 7/17/2013 1-Jun-13
31-Oct-89 7/17/2013 30-Apr-13
1-Oct-91 7/17/2013 1-Apr-13
31-Aug-93 7/17/2013 28-Feb-13
1-Aug-95 7/17/2013 1-Feb-13
1-Jul-97 7/17/2013 1-Jan-14
1-Jun-99 7/17/2013 1-Dec-13
1-May-01 7/17/2013 1-Nov-13
1-Apr-03 7/17/2013 1-Oct-13
1-Mar-05 7/17/2013 1-Sep-13
30-Jan-07 7/17/2013 30-Jul-13
30-Dec-08 7/17/2013 30-Jun-13
30-Nov-10 7/17/2013 30-May-13
30-Oct-12 7/17/2013 30-Apr-13some of your backdates go into the future...which I don't think is intent.
SirJB7
Excel Rōnin
@Luke M
Hi, smiling!
First, let us see the table with my updated columns as yours so as to compare them:
-----
[pre]
[/pre]
-----
From which I can conclude:
a) You use a strange and inexact way to establish a 6 month period (182.5 or 183 days), I rather prefer to use a straight 6 months value: that explains the slight differences in last column ranging from 0 to 3 in absolute values.
b) You're clearly older than me as I always suspected, but until now I hadn't any proof: regarding 6th column (F) you're 1 year older than me, who still remain always the same (Led Zeppelin dixit), i.e., just a little kid. Unless your maths using imperial date system works different, who knows!, or unless you rounded the years... take the 1st 2 cases: 33 vs 32, 31 vs 30, and I stick with my version, integer years.
c) Regarding your immediate previous post, I don't arrive to those dates, please check my 5th column (E). Are you still using Julian calendar? Move on!
My formulas:
D: =AÑO(C2-A2)-1900 -----> in english: =YEAR(C2-A2)-1900 ... i.e, the inner part of my original formula.
E: =FECHA.MES(A2;(AÑO(C2-A2)-1900)*12+6) -----> in english: =EDATE(A2,(YEAR(C2-A2)-1900)*12+6) ... my original formula, column shifted.
So either I'm missing to see a pair of elephants within a toilette (and I should go again to the oculist, but I think he doesn't want to see me, if unless he were a she...) or you're actually fried as you said:
http://chandoo.org/forums/topic/formula-challenge-006-whats-the-number-you-can-confirm-is-a-prime-or-not#post-124343
Regards!
Hi, smiling!
First, let us see the table with my updated columns as yours so as to compare them:
-----
[pre]
Code:
Birthday Age Luke M Back date Luke M Age SirJB7 Back date SirJB7 Age differences Back date differences
01-abr-80 33 29-sep-12 32 01-oct-12 1 -2
02-mar-82 31 30-ago-12 30 02-sep-12 1 -3
31-ene-84 29 31-jul-12 28 31-jul-12 1 0
31-dic-85 28 30-jun-13 27 30-jun-13 1 0
01-dic-87 26 31-may-13 25 01-jun-13 1 -1
31-oct-89 24 30-abr-13 23 30-abr-13 1 0
01-oct-91 22 31-mar-13 21 01-abr-13 1 -1
31-ago-93 20 28-feb-13 19 28-feb-13 1 0
01-ago-95 18 29-ene-13 17 01-feb-13 1 -3
01-jul-97 16 29-dic-12 15 01-ene-13 1 -3
01-jun-99 14 29-nov-12 13 01-dic-12 1 -2
01-may-01 12 29-oct-12 11 01-nov-12 1 -3
01-abr-03 10 29-sep-12 9 01-oct-12 1 -2
01-mar-05 8 29-ago-12 7 01-sep-12 1 -3
30-ene-07 6 30-jul-12 5 30-jul-12 1 0
30-dic-08 5 29-jun-13 4 30-jun-13 1 -1
30-nov-10 3 30-may-13 2 30-may-13 1 0
30-oct-12 1 29-abr-13 0 30-abr-13 1 -1-----
From which I can conclude:
a) You use a strange and inexact way to establish a 6 month period (182.5 or 183 days), I rather prefer to use a straight 6 months value: that explains the slight differences in last column ranging from 0 to 3 in absolute values.
b) You're clearly older than me as I always suspected, but until now I hadn't any proof: regarding 6th column (F) you're 1 year older than me, who still remain always the same (Led Zeppelin dixit), i.e., just a little kid. Unless your maths using imperial date system works different, who knows!, or unless you rounded the years... take the 1st 2 cases: 33 vs 32, 31 vs 30, and I stick with my version, integer years.
c) Regarding your immediate previous post, I don't arrive to those dates, please check my 5th column (E). Are you still using Julian calendar? Move on!
My formulas:
D: =AÑO(C2-A2)-1900 -----> in english: =YEAR(C2-A2)-1900 ... i.e, the inner part of my original formula.
E: =FECHA.MES(A2;(AÑO(C2-A2)-1900)*12+6) -----> in english: =EDATE(A2,(YEAR(C2-A2)-1900)*12+6) ... my original formula, column shifted.
So either I'm missing to see a pair of elephants within a toilette (and I should go again to the oculist, but I think he doesn't want to see me, if unless he were a she...) or you're actually fried as you said:
http://chandoo.org/forums/topic/formula-challenge-006-whats-the-number-you-can-confirm-is-a-prime-or-not#post-124343
Regards!
NARAYANK991
Excel Ninja
Hi ,
Can you please clarify one point ?
As I have understood your post , the issue of backdating comes in only when you are more than half-way through one year ; going by your own example date of 11/12/1979 ( which I assume is November 12 , 1979 ) , as on July 17 this year , you are closer to your 34th birthday than your 33rd.
To reduce the burden on the customer , you backdate the insurance policy so that your age is taken as 33 rather than 34.
However , is this policy to be applied in all cases ?
If we take the first date in Luke's example , April 1 , 1980 , then on July 17 this year , you are closer to 33 than 34 ; therefore your calendar age and the insurance age will both match ; in this case , is there any need for backdating ?
Narayan
Can you please clarify one point ?
As I have understood your post , the issue of backdating comes in only when you are more than half-way through one year ; going by your own example date of 11/12/1979 ( which I assume is November 12 , 1979 ) , as on July 17 this year , you are closer to your 34th birthday than your 33rd.
To reduce the burden on the customer , you backdate the insurance policy so that your age is taken as 33 rather than 34.
However , is this policy to be applied in all cases ?
If we take the first date in Luke's example , April 1 , 1980 , then on July 17 this year , you are closer to 33 than 34 ; therefore your calendar age and the insurance age will both match ; in this case , is there any need for backdating ?
Narayan
I want to thank each one of you for your help. This will work great. It is good to see people passionate - and competitive I mite add - about their abilities in excel. This is an amazing website for an amazing software program.
NARAYANK991
To your question, current age will be used. If you are 33 and are within 6 months since your birth date, the carrier will use 33 in their risk assessment when it comes to underwriting. If it is 6 months or longer since your last birthday the carrier will use 34. (Unless you backdate) Remember, not all life insurance carriers use age nearest, but most do.
NARAYANK991
To your question, current age will be used. If you are 33 and are within 6 months since your birth date, the carrier will use 33 in their risk assessment when it comes to underwriting. If it is 6 months or longer since your last birthday the carrier will use 34. (Unless you backdate) Remember, not all life insurance carriers use age nearest, but most do.
NARAYANK991
Excel Ninja
Hi ,
So can I take it that backdating will not be applicable in all cases ?
It will be applicable only in those cases where the insurance age is greater than the calendar age.
Narayan
So can I take it that backdating will not be applicable in all cases ?
It will be applicable only in those cases where the insurance age is greater than the calendar age.
Narayan
I have run into a snag, I need to back date 6 calendar months and 4 days from upcoming birthday. If my birthday is Jan, 24 I need to go back 6 months to July, 24. (It does not matter how many days our in each of those months) and then 4 days which would be July, 20. This is the backdate that I need to calculate. Any additional help with this formula would be a blessing.
Hello, SirJB7 and Luke M.
Could one of you help me with this? I need to backdate by months and days not just by days. If my birthday is Dec, 15 I need to go back 6 months (does not matter how many days are in those months)which will be June, 15. Then by 4 days. The backdate then will be June, 11. Any help would be awesome.
Could one of you help me with this? I need to backdate by months and days not just by days. If my birthday is Dec, 15 I need to go back 6 months (does not matter how many days are in those months)which will be June, 15. Then by 4 days. The backdate then will be June, 11. Any help would be awesome.