Excel Novice needs formula help. IF, i think?

Hi All,

I am trying to create an insurance premium calculator for a specific policy type. I have written the mathematical equations but get really confused when trying to translate it into excel.

The below are my equations:
If C4 = Death Only and D4 = 3 Months then F4 = B4*2%
If C4 = Death Only and D4 = 6 Months then F4 = B4*2.8%
If C4 = Death Only and D4 = 12 Months then F4 = B4*5.6%
If C4 = Death + Loss ACC and D4 = 3 Months then F4 = B4*3.7%
If C4 = Death + Loss ACC and D4 = 6 Months then F4 = B4*5.3%
If C4 = Death + Loss ACC and D4 = 12 Months then F4 = B4*10.6%
If C4 = Death + Loss AI and D4 = 3 Months then F4 = B4*4.4%
If C4 = Death + Loss AI and D4 = 6 Months then F4 = B4*6.3%
If C4 = Death + Loss AI and D4 = 12 Months then F4 = B4*12.6%

If E4 = NSW then H4 = (F4+G4)*2.5%
If E4 = WA then H4 = (F4+G4)*10%
If E4 = SA then H4 = (F4+G4)*11%
If E4 = QLD then H4 = (F4+G4)*9%
If E4 = VIC then H4 = (F4+G4)*10%
If E4 = NT then H4 = (F4+G4)*10%
If E4 = TAS then H4 = (F4+G4)*10%
If E4 = ACT then H4 = (F4+G4)*6%

If anyone could help it would de much appreciated. I have also attached the file if that is easier.

Hi, welcome to the forum

See if this will get you started. I created a small table for the type and Period...

	H	I	J	K
2		3 Months	6 Months	12 Months
3	Death Only - AI	2.0%	2.8%	5.6%
4	Death + Loss ACC	3.7%	5.3%	10.6%
5	Death + Loss AI	4.4%	6.3%	12.6%

Then used this to pull the %...
=IFERROR(INDEX('Rates & Calculations'!$I$3:$K$5,MATCH(Sales!$C4,'Rates & Calculations'!$H$3:$H$5,0),MATCH(Sales!$D4,'Rates & Calculations'!$I$2:$K$2,0)),"")

For the region, another small table with rates...

	H	I
7	NSW	2.5%
8	QLD	9.0%
9	VIC	10.0%
10	SA	11.0%
11	WA	10.0%
12	TAS	10.0%
13	NT	10.0%
14	ACT	6.0%

and a similar function for that...
=(F4+G4)*INDEX('Rates & Calculations'!$I$7:$I$14,MATCH(Sales!$E4,'Rates & Calculations'!$H$7:$H$14,0))

Thanks Mate

I think we (you) are on the right path, I can see the workings of the calculation except that the answer is 4 decimal places out. Any suggestions?

Example of the results below.

Sum Insured
$10,000

Cover Type
Death + Loss AI

Period of Cover
12 Months

Destination State
NSW

Base Premium
$0.1260

GST
$0.0126

Stamp Duty
$0.0035

Gross Premium
$0.1421

Base Premium should equal $1,260 and so forth across the row.

Modify the first formula to read:

Formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

You're a superstar, thank you all very much for your help.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

Happy to help and thanks for the feedback