Share via

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

Anonymous
2023-12-04T19:12:20+00:00

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
Microsoft 365 and Office | Excel | For home | Windows

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.

0 comments

Answer accepted by question author

  1. triptotokyo-5840 36,681 Reputation points Volunteer Moderator
    2023-12-04T20:47:15+00:00

    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

    ![](https://learn-attachment.microsoft.com/api/attachments/7c9c48e4-7928-4354-b039-ff5413574608?platform=QnA

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. triptotokyo-5840 36,681 Reputation points Volunteer Moderator
    2023-12-04T20:01:40+00:00

    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.

    ![](https://learn-attachment.microsoft.com/api/attachments/513056a2-98ed-4396-8158-31305c530043?platform=QnA

  3. HansV 462.6K Reputation points MVP Volunteer Moderator
    2023-12-04T19:59:05+00:00

    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))

    0 comments
  4. Anonymous
    2023-12-04T19:47:14+00:00

    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):

    =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

    0 comments