This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Help, I need to figure out a formula to calculate life insurance rate based on different age brackets. Eg, I have 12 age brackets starting like this 0-24, 25-29 and so on. The rate is based on the coverage amount eg. 100,000 will be $100*.08= $8 for an employee who is on age bracket of 35-39, Please help me. See below for a screen shot. I need to calculate the total cost
| LIFE INSURANCE COST CALCULATION FORM | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| BENEFIT | Coverage Amount | $100,000 | Age | 41 | Cost | |||||||
| Voluntary Employee Life (LVE) | ||||||||||||
| Employee | Employee Spouse | Emploeee Child | Family | |||||||||
| Age | Male | Female | Male | Female | Male | Female | Male | Female | ||||
| 0-24 | $ 0.05 | $ 0.05 | $ 0.05 | $ 0.05 | $ 0.05 | $ 0.05 | $ 0.05 | $ 0.05 | ||||
| 25-29 | $ 0.05 | $ 0.05 | $ 0.05 | $ 0.05 | $ 0.05 | $ 0.05 | $ 0.05 | $ 0.05 | ||||
| 30-34 | $ 0.06 | $ 0.06 | $ 0.06 | $ 0.06 | $ 0.06 | $ 0.06 | $ 0.06 | $ 0.06 | ||||
| 35-39 | $ 0.08 | $ 0.08 | $ 0.08 | $ 0.08 | $ 0.08 | $ 0.08 | $ 0.08 | $ 0.08 | ||||
| 40-44 | $ 0.12 | $ 0.12 | $ 0.12 | $ 0.12 | $ 0.12 | $ 0.12 | $ 0.12 | $ 0.12 | ||||
| 45-49 | $ 0.19 | $ 0.19 | $ 0.19 | $ 0.19 | $ 0.19 | $ 0.19 | $ 0.19 | $ 0.19 | ||||
| 50-54 | $ 0.30 | $ 0.30 | $ 0.30 | $ 0.30 | $ 0.30 | $ 0.30 | $ 0.30 | $ 0.30 | ||||
| 55-59 | $ 0.47 | $ 0.47 | $ 0.47 | $ 0.47 | $ 0.47 | $ 0.47 | $ 0.47 | $ 0.47 | ||||
| 60-64 | $ 0.66 | $ 0.66 | $ 0.66 | $ 0.66 | $ 0.66 | $ 0.66 | $ 0.66 | $ 0.66 | ||||
| 65-69 | $ 1.09 | $ 1.09 | $ 1.09 | $ 1.09 | $ 1.09 | $ 1.09 | $ 1.09 | $ 1.09 | ||||
| 70-74 | $ 2.14 | $ 2.14 | $ 2.14 | $ 2.14 | $ 2.14 | $ 2.14 | $ 2.14 | $ 2.14 | ||||
| 75-79 | $ 4.13 | $ 4.13 | $ 4.13 | $ 4.13 | $ 4.13 | $ 4.13 | $ 4.13 | $ 4.13 | ||||
| 80-999 | $ 7.70 | $ 7.70 | $ 7.70 | $ 7.70 | $ 7.70 | $ 7.70 | $ 7.70 | $ 7.70 |
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
=INDEX({0.05,0.06,0.08,0.12,0.19,0.3,0.47,0.66,1.09,2.14,4.13,7.7},MATCH(E1,{0,30,35,40,45,50,55,60,65,70,75,80},1))*B1
Let's say that if I have an employee who is 41 Years Old and is asking for 100,000 coverage, I want the formula to tell me how much the life insurance rate will be just by typing the coverage amount and typing the employees age.
If I manually calculate the amount will look something like this. The $0.12 is based per thousand
100000/1000=100 *.12 = $12.00 (she is on the age bracket of 40-44 years old)
My goal is to have a spreadsheet where I can change the coverage amount and the age and the formula will automatically calculate the cost for me
| BENEFIT | Coverage Amount | $100,000 | Age | 41 | Cost | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Voluntary Employee Life (LVE) |
You can share some dummy data which you need to calculate.
thank you so much for the help, how can I incorporate the age cell. so if I type the age, eg. 60 or 41, the formula will automatically calculate the rate.
I figured out how to add the coverage amount to the formula provided.
thank you so much
=INDEX({0.05,0.06,0.08,0.12,0.19,0.3,0.47,0.66,1.09,2.14,4.13,7.7},MATCH(B4,{0,30,35,40,45,50,55,60,65,70,75,80},1))