Life Insurance Calculator Help

[TheCarlough]

Long-time lurker, first time poster!

I need help developing a formula that would allow an employee to enter their age, desired benefit $ amount, and spit out a monthly premium cost.

I have a table created to pull the necessary data from. Each row is the $ benefit amount (from $10,000 to $500,000 in increments of $10k). The columns are age bands. The first age band is 00-29 with each subsequent band in 5 year increments (30-34, 35-39, etc., etc) up until age 70. Any age over 70 is the same rate.

My goal, for example:

Employee enters his/her age: 31
Desired Benefit Amount: $100,000

Formula will lookup where the cross-section is for age 31 and $100,000 on the table: eg; $8.60. No additional calculation is needed. This is the monthly premium cost.

My guess is it's a VLOOKUP. I just don't know how to do one with a table this large and with age bands.

Thank you!

 

[DRSteele]

Welcome to the forum.

This type of task is easily accomplished with the use of functions INDEX and MATCH. I mocked this up for you.

Book1
	A	B	C	D	E	F	G	H	I	J	K
3	Desired Benefit Amount	100,000
4	Employee enters his age	31
5	Premium	8.60
6
7	Benefit|Age	0	30	35	40	45	50	55	60	65	70
8	10,000	7.03	7.17	7.31	7.46	7.61	7.76	7.92	8.07	8.24	8.40
9	20,000	7.17	7.32	7.46	7.61	7.76	7.92	8.08	8.24	8.40	8.57
10	30,000	7.32	7.47	7.62	7.77	7.92	8.08	8.24	8.41	8.58	8.75
11	40,000	7.47	7.62	7.77	7.93	8.08	8.25	8.41	8.58	8.75	8.93
12	50,000	7.62	7.77	7.93	8.09	8.25	8.41	8.58	8.75	8.93	9.11
13	60,000	7.77	7.93	8.09	8.25	8.42	8.59	8.76	8.93	9.11	9.29
14	70,000	7.93	8.09	8.26	8.42	8.59	8.76	8.94	9.12	9.30	9.48
15	80,000	8.09	8.26	8.42	8.59	8.76	8.94	9.12	9.30	9.49	9.68
16	90,000	8.26	8.43	8.60	8.77	8.94	9.12	9.31	9.49	9.68	9.87
17	100,000	8.43	8.60	8.77	8.95	9.13	9.31	9.50	9.68	9.88	10.08
18	110,000	8.60	8.77	8.95	9.13	9.31	9.50	9.68	9.88	10.08	10.28
19	120,000	8.77	8.95	9.13	9.31	9.50	9.68	9.88	10.08	10.28	10.48
20	130,000	8.94	9.13	9.31	9.50	9.68	9.88	10.08	10.28	10.48	10.69
Sheet24

Cell Formulas
Range	Formula
B5	=INDEX(B8:L20,MATCH(B3,A8:A20,0),MATCH(B4,B7:L7,1))

 

[TheCarlough]

Holy moly. You are amazing my friend.

Thank you!

 

[TheCarlough]

Question is there a way to do this if the age columns appear as follows?

[TABLE="width: 680"]
<tbody>[TR]
[TD="colspan: 10, align: center"]Age[/TD]
[/TR]
[TR]
[TD]00-29[/TD]
[TD]30-34[/TD]
[TD]35-39[/TD]
[TD]40-44[/TD]
[TD]45-49[/TD]
[TD]50-54[/TD]
[TD]55-59[/TD]
[TD]60-64[/TD]
[TD]65-69[/TD]
[TD]70+[/TD]
[/TR]
</tbody>[/TABLE]

Where instead of it being a single number it actually displays the range?