Life Insurance Premium Calc with age band

[Freshta01]

Hello,
I would like to get help with calculating life insurance premium/cost where the cost is based on age band and coverage amount is in units of $1,000. Please see attached sample sheet. If a person enters his/her age and desired coverage amount in cell B1&B2, the formula looks at rate chart and gives the cost. e.g. a person who is 40 years old and wants 200k of life insurance then the result should be 200,000*.130/1000 = $26.

Much appreciate any help with this!

 

[etaf]

a slight change in table
I have added another column

i have shown the lookup in a cell D2 - just so you can see it work

Book1
	A	B	C	D
1	age	40	premium	age lookup
2	amount	200000	26	0.13
3
4
5
6	<25	0.06	0
7		0.07	25
8		0.08	30
9		0.11	35
10		0.13	40
11		0.2	45
12		0.35	50
13		0.57	55
14		0.88	60
15		1.69	65
16		2.75	70
17
Sheet1

Cell Formulas
Range	Formula
C2	C2	=B2*INDEX(B6:B16,MATCH(B1,C6:C16,1))/1000
D2	D2	=INDEX(B6:B16,MATCH(B1,C6:C16,1))

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Dave Patton]

another alternative

T202306a.xlsm
	A	B	C
1	Enter Age	40	Premium
2	Amount	$200,000	$26
3
4
5	Age	Rate
6	0	6%
7	25	7%
8	30	8%
9	35	11%
10	40	13%
11	45	20%
12	50	35%
13	55	57%
14	60	88%
15	65	169%
16	70	275%
17
1h

Cell Formulas
Range	Formula
C2	C2	=B2/1000*LOOKUP(B1,A6:B16)

 

[Freshta01]

a slight change in table
I have added another column

i have shown the lookup in a cell D2 - just so you can see it work

Book1
	A	B	C	D
1	age	40	premium	age lookup
2	amount	200000	26	0.13
3
4
5
6	<25	0.06	0
7		0.07	25
8		0.08	30
9		0.11	35
10		0.13	40
11		0.2	45
12		0.35	50
13		0.57	55
14		0.88	60
15		1.69	65
16		2.75	70
17
Sheet1

Cell Formulas
Range	Formula
C2	C2	=B2*INDEX(B6:B16,MATCH(B1,C6:C16,1))/1000
D2	D2	=INDEX(B6:B16,MATCH(B1,C6:C16,1))

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

thank you so much for your response @etaf. I actually did try to download the XL2BB but due to restrictions I couldn't. I also tried to upload the sample excel file but it didn't allow me not sure why it doesn't allow to upload a small sample excel sheet. Sharing sites are also restricted from work computers.


I appreciate your response. I was curious to know if there's any formula to look at age-range? without changing the table. In your example, if someone inputs age 41, would it calculate? I'd have to try and see!?! but the idea is that anyone age 40-44 would get one rate and so on and so forth.

 

[Freshta01]

another alternative

T202306a.xlsm
	A	B	C
1	Enter Age	40	Premium
2	Amount	$200,000	$26
3
4
5	Age	Rate
6	0	6%
7	25	7%
8	30	8%
9	35	11%
10	40	13%
11	45	20%
12	50	35%
13	55	57%
14	60	88%
15	65	169%
16	70	275%
17
1h

Cell Formulas
Range	Formula
C2	C2	=B2/1000*LOOKUP(B1,A6:B16)

thank you Dave, any idea how I would Vlookup or Xlookup age-range instead of single age?

 

[Dave Patton]

" any idea how I would Vlookup or Xlookup age-range instead of single age?"
You may want to review Excel's help information for the functions.

Did you try the suggestion?

T202306a.xlsm
	A	B	C
1	Enter Age	27	Premium
2	Amount	$200,000	$14
3			$14
4			$14
5	Age	Rate
6	0	6%
7	25	7%
8	30	8%
9	35	11%
10	40	13%
11	45	20%
12	50	35%
13	55	57%
14	60	88%
15	65	169%
16	70	275%
17
1h

Cell Formulas
Range	Formula
C2	C2	=B2/1000*LOOKUP(B1,A6:B16)
C3	C3	=B2/1000*VLOOKUP(B1,A6:B16,2,1)
C4	C4	=B2/1000*XLOOKUP(B1,A6:A16,B6:B16,,-1,1)

 

[etaf]

if someone inputs age 41, would it calculate? I'd have to try and see!?! but the idea is that anyone age 40-44 would get one rate and so on and so forth.

Yes

the formula uses the lowest matching value - which is the 1 in the match
=INDEX(B6:B16,MATCH(B1,C6:C16,1))
if it was a 0 - then it would need to be an Exact match

so if they put in 41
it looks down the numbers to find the lower matching number , in this case 40 - and returns the value with 40

I suspect it could be modified to be used with your table , with some manipulation - i may have a think about that

 

[etaf]

there maybe a better way to do this , to use your table

I needed to add a couple of IFs

using your table I have added a couple of IF()

=INDEX(B6:B16,MATCH(IF(B1<25,"<2",B1),IF(B1<25,LEFT(A6:A16,2),LEFT(A6:A16,2)*1),1))

this is because of the <25 - so i have to change the range to be text and lookup the first 2 characters <2
otherwise 25 and above , i can just use the 2 characters on the left of the range , and *1 to change to a number

again in cell D2 - just so you can see the result returned , when you try different ages
and C2 has the calculation

=B2*INDEX(B6:B16,MATCH(IF(B1<25,"<2",B1),IF(B1<25,LEFT(A6:A16,2),LEFT(A6:A16,2)*1),1))/1000

try it out with different ages - to see if working

Book1
	A	B	C	D
1	age	18	premium	age lookup
2	amount	200000	12	0.06
3
4
5
6	<25	0.06
7	25 - 29	0.07
8	30 - 34	0.08
9	35 - 39	0.11
10	40 - 44	0.13
11	45 - 49	0.2
12	50 - 54	0.35
13	55 - 59	0.57
14	60 - 64	0.88
15	65 - 69	1.69
16	70+	2.75
Sheet1

Cell Formulas
Range	Formula
C2	C2	=B2*INDEX(B6:B16,MATCH(IF(B1<25,"<2",B1),IF(B1<25,LEFT(A6:A16,2),LEFT(A6:A16,2)*1),1))/1000
D2	D2	=INDEX(B6:B16,MATCH(IF(B1<25,"<2",B1),IF(B1<25,LEFT(A6:A16,2),LEFT(A6:A16,2)*1),1))

 

[Dave Patton]

T202306a.xlsm
	A	B		D	E
1	Enter Age	36
2	Amount	$200,000
3				$22	$22
4
19	Age	Rate
20	0 - 24	6%
21	25 - 29	7%
22	30 - 35	8%
23	36 - 40	11%
24
25	Age	Rate
26	<25	6%
27	25 - 29	7%
28	30 - 35	8%
29	36 - 40	11%
1h

Cell Formulas
Range	Formula
D3	D3	=XLOOKUP(B1,--LEFT(A20:A23,2),B20:B23,,-1,1)*B2/1000
E3	E3	=IF(B1<25,B26,XLOOKUP(B1,--LEFT(A27:A29,2),B27:B29,,-1,1))*B2/1000