Formula to calculate insurance premiums

[NabilSheber]

Hello folks, I am trying to create a simple program on excel for print out insurance offers easily without having to go back to rate tables and calculate manually. Basically, I need a formula to calculate the premium in a certain cell while the user simply enters age and amount of insurance. Any suggestions from anyone please. Thank you.

 

[Gerald Higgins]

Hi, welcome to the board.

Do you know how to calculate this premium, without using Excel ?

If YES, please explain here, and there's a good chance someone will be able to produce an Excel formula to do the same.

If NO, I'm not sure how we can help.

 

[NabilSheber]

Hi, welcome to the board.

Do you know how to calculate this premium, without using Excel ?

If YES, please explain here, and there's a good chance someone will be able to produce an Excel formula to do the same.

If NO, I'm not sure how we can help.

Hi Gerald,

Yes, I do know how to calculate the premium. It is quite straight forward, I have a rate per thousand table per age. For example age 30's rate is 2.5 per thousand and if the amount of insurance required is 100,000$ the simply the premium would be the rate 2.5 * 100,000/1000; in this case 250$.

I am preparing a quotation format on excel and what I need is a formula that when I enter the age in a certain cell the formula would simply read the age, extract the rate from the rate table relating to this age (that could be linked from another sheet) and the formula would calculate the correct premium. I would think it would be and IF and AND formula but I am having problems sorting it out.

Thanks to anyone who can help.

 

[Gerald Higgins]

Well that does sound straightforward.

Let's say your "rate per thousand table per age" is in columns A and B, with headers in row 1, and the first line of data in row 2, like this

Age...........Rate
30.............2.5
31.............2.6
32.............2.6
33.............2.7

and so on.

Let's say you plug the age into cell C1, and the amount of cover required into cell D2.

Then the formula is going to be something like . . .

=vlookup(c1,a2:b100,2,false)*d2/1000

Replace "b100" with whatever the last row of your table is.

If your age table is in bands, such as 30-35, 36-40 and so on, you can either choose to set up your table so that individual years are listed, OR, there are ways round that using slightly different formulas.

Post back if you need more help.

 

[NabilSheber]

Thank you very much Gerald, you have really helped and saved me some valuable time.

Appreciate your knowledge and assistance.

 

[NabilSheber]

Hi Gerald,

I wonder if you can help me one more time.

What would the formula you suggested look like if I have four different columns of rates which are amount of Cover dependent? Meaning, all else being the same, if the amount of cover is less than 50K then i want to use the rates in column 1 or A and if the cover is <100K I want to use the rate in Column 2 or B and so on.

Thanks in advance for your help.

regards,

 

[Gerald Higgins]

Hi. There are probably lots of ways to do this.

One way . . .

Have a cell somewhere, let's say E2, which contains a formula, which identifies which column to use.

Let's say the basic amount of cover is still in cell D2, as in post #4.

Your new formula would be something like this
=if(D2<50000,1,if(D2<100000,2,if(d2<150000,3,4)))

There are more elegant ways of doing this.
For example, as long as your bands are absolutely regular intervals, you could use something like
=roundup(d2/50000,0)
instead.

Your original vlookup formula then becomes something like
=vlookup(c1,a2:e100,1+e2,false)*d2/1000

Note, there are obviously overlaps in the formula ranges here, which you'll need to allow for.

 

[NabilSheber]

Thank you Sir! Will try that out and see what happens..

 

[VisheshMalik]

@NabilSheber, Hey​

Could you please share the rate per thousand table per age with me. Actually i need something like that for my project and unfortunately i cant find any.

 

[NabilSheber]

=VLOOKUP(L14,'LTP Age 65 Rates'!L5:'LTP Age 65 Rates'!O46,IF(L23<100000,2,IF(L23<250000,3,4)),FALSE)*L23/1000

This is the formula I am using to calculate the rate per thousand from three different rate columns depending on the amount of insurance requested.

I hope that this helps.

I basically followed Gerald Higgins suggestions.

Regards