Ask a new question

Help with Excel Fomula to Calculate Insurance Premiums based on Age and Sum Insured

Hi All,

Below is the table of an example data sheet for an insurance premium. Will require a fomula to auto tabulate cost based on the the blank table below when selected Sum Insured Changes taking into consideration Age Group range.

Sum Insured

Age Group

Premium

150,000

(36-45)

???

Coverage amount(S$)

(0-35)

(36-45)

(46-50)*

(51-55)*

(56-60)*

(61-65)*

(66-70)*

50,000

 $     2.70

 $     4.50

 $   12.40

 $   18.90

 $   26.10

 $   36.35

 $   47.25

100,000

 $     5.40

 $     9.00

 $   24.80

 $   37.80

 $   52.20

 $   72.70

 $   94.50

150,000

 $     8.10

 $   13.50

 $   37.20

 $   56.70

 $   78.30

 $ 109.50

 $ 141.75

200,000

 $   10.80

 $   18.00

 $   49.60

 $   75.60

 $ 104.40

 $ 145.40

 $ 189.00

250,000

 $   13.50

 $   22.50

 $   62.00

 $   94.50

 $ 130.50

 $ 181.75

 $ 236.25

300,000

 $   16.20

 $   27.00

 $   74.40

 $ 113.40

 $ 156.60

 $ 218.10

 $ 283.50

350,000

 $   18.90

 $   31.50

 $   86.80

 $ 132.30

 $ 182.70

 $ 254.45

 $ 330.75

400,000

 $   21.60

 $   36.00

 $   99.20

 $ 151.20

 $ 208.80

 $ 290.80

 $ 378.00

450,000

 $   24.30

 $   40.50

 $ 111.60

 $ 170.10

 $ 234.90

 $ 327.15

 $ 425.25

500,000

 $   27.00

 $   45.00

 $ 124.00

 $ 189.00

 $ 261.00

 $ 363.50

 $ 472.50

Hi, thank you for coming to the forum, my name is Adekunle.its worth noting that this is a user community and we are all users here. while we strive to assist and share insights we are not microsoft. To calculate the insurance premium based on the Sum Insured and Age Group, you can use a combination of `INDEX` and `MATCH` functions. Here's a formula for cell D10 (assuming the Sum Insured is in column C and Age is in column B): ```excel =INDEX($E$2:$L$11,MATCH(B10,$A$2:$A$11,0),MATCH(C10,$B$1:$I$1,0)) ``` This formula uses `MATCH` to find the row and column numbers corresponding to the Age and Sum Insured, and then uses `INDEX` to retrieve the premium amount from the specified range. Here's a breakdown of the formula: - `$E$2:$L$11`: This is the range containing the premium values. - `MATCH(B10,$A$2:$A$11,0)`: Finds the row number based on the Age in cell B10. - `MATCH(C10,$B$1:$I$1,0)`: Finds the column number based on the Sum Insured in cell C10. Remember to adjust the cell references based on the actual location of your data in your spreadsheet. Copy this formula to the entire table, and it should auto-tabulate the premium amounts based on the selected Sum Insured and Age Group. Best regards Adekunle

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Let's say the range with the premiums is on a sheet named Premium Sheet.

With the sum insured in A2 and the age group in B2 on another sheet:

=INDEX('Premium Sheet'!$B$2:$H$11, MATCH(A2, 'Premium Sheet'!$A$2:$A$11), MATCH(B2&"*", 'Premium Sheet'!$B$1:$H$1, 0))

---
Best wishes, HansV
https://www.eileenslounge.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

One way to do this would be to use VLOOKUPs (see image below).

 

Results are in cells:-

 

C 15 / H 15 / C 19

 

 - with formulae in those cells shown just below the results cells.

 

3 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 

I’ve added a Calculator to my original screen posting (see image below).

 

Here’s my file:-

 

https://www.mediafire.com/file/55o9676yevl72kw/Help_with_Excel_Fomula_to_Calculate_Insurance_Premiums.xlsm/file

 

Download the file and open it clicking on:-

 

Enable Editing

 

 - if necessary / required.

 

Workbook should open the Worksheet called:-

 

Sheet1

 

 - cell:-

 

F 19

 

All user has to do is to:-

 

 - enter Sum Insured in F 19

 

 - and:-

 

 - age range (in terms of column number) in G 19

 

 - to see the Insurance Premium in cell H 19

 

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Adekunle,

Thank you for your detailed breakdown explanation on the excel fomula I could use for my above issue.

I have tried your formula but I keep receiving an issue with the fomular used.

Will you be able to share the file to me for my reference please?

Regards

Nick

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Adekunle,

Thank you for your detailed breakdown explanation on the excel fomula I could use for my above issue.

I have tried your formula but I keep receiving an issue with the fomular used.

Will you be able to share the file to me for my reference please?

Regards

Nick

Did you try my file?; as far as I can see it gives you exactly what you want.

Just complete cells F 19 and G 19 to get the result in cell H 19.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated April 30, 2024 Views 649 Applies to: