# Solved: Excel escalation formula

Discussion in 'Business Applications' started by COMPTEACH, Sep 18, 2012.

Not open for further replies.

Joined:
Nov 1, 2010
Messages:
11
i wonder if anyone can help with this formula.

The figures are

Sum insured R1,350,360
Escalation @ 10% R135,036
Total R1,485,396

the figures above are correct but have been calculated manually (not by me).

I need one formula in the 'Monthly Premium' column which does the following:

Multiplies sum insured by .1%
Multiplies Escalation by .1% and then halves that Esc figure as only 50% of the .1% will be paid by the client
This result is the annual premium - 8% must be added to the annual premium and then divided by 12 to give the monthly premium
The monthly premium should result in the figure above R127.62.

3. ### etaf Wayne Moderator

Joined:
Oct 2, 2003
Messages:
63,180
do not get the same answer
where am i going wrong

R 1,350,360.00
Multiplies sum insured by .1%
10 % = R 135,036.00
= R 1,485,396.00

Escalation is
= R 135,036.00
10% of the escalation
= R 13,503.60
then half of that
= R 6,751.80

so the 10% of the original same insured plus 50% of the escalation
R 141,787.80

then that is increased by 8%
R 153,130.82

and then divided by 12
R 12,760.90

what am i doing wrong
Code:
```Sum Insured 	             10%		                        Escalatio   Half Escalation	Annual Premium	8%	         Monthly

R 1,350,360.00 	 R 135,036.00 	 R 1,485,396.00 	 R 13,503.60 	 R 6,751.80 	 R 141,787.80 	 R 153,130.82 	 R 12,760.90
```

Joined:
Nov 1, 2010
Messages:
11
Hi Etaf - thank you for your reply. i am actually very confused with this formula. the person who wants it spoke to me over the phone and he said 'point one percent' - not 10%. I have actually arrived at his figure doing it the long way around but don't know how to get it all in one formula. Your help is greatly appreciated

5. ### etaf Wayne Moderator

Joined:
Oct 2, 2003
Messages:
63,180
can you go through the long way and show every step of the way
I can make a formula work - just need every step

like i laid out would help

sum insured = xxxx

10% of xxxx = yyyy

escalation = 10% of yyyy

50% of yyyy = aaaa

etc
detailed with the actual values instead of aaaaa yyyy xxx etc

Joined:
Nov 1, 2010
Messages:
11
Thanks Etaf

Sum insured R1,350,650
R1,350,650*0.1%
Result R1,350.36

Escalation clause R135,036
R136,036*0.1%/2
Result R67.52

Annual Premium total of R1,350.36 + R67.52
Result R1417.88

R1417.88*8%
Result R113.43

Total of annual premium + 8%
R1417.88 + R113.43
Result R1531.31

Result R127.61

7. ### etaf Wayne Moderator

Joined:
Oct 2, 2003
Messages:
63,180
so thats not .1%
must end 650
1,350.65

where does the R136,036 come from ?

Joined:
Nov 1, 2010
Messages:
11
Sorry, typo !! The figure shoudl be R135,036

9. ### etaf Wayne Moderator

Joined:
Oct 2, 2003
Messages:
63,180
so the sun assured is
Sum insured R1,350,360
so modified as above

correct

Joined:
Nov 1, 2010
Messages:
11
yes that is correct. i now need that long story in one formula!!

11. ### etaf Wayne Moderator

Joined:
Oct 2, 2003
Messages:
63,180
heres the formula
=(((A2*0.001)+(((A2*0.1)*0.001)/2))+(((A2*0.001)+(((A2*0.1)*0.001)/2))*0.08))/12
this often does not paste exactly adds the odd space
so inside here
Code:
```=(((A2*0.001)+(((A2*0.1)*0.001)/2))+(((A2*0.001)+(((A2*0.1)*0.001)/2))*0.08))/12
```
where
A2 = sum insured

and each step is in the attached file
where I have built it up from A2
so you can see each step of the way

it can be simplified - BUT this was shows each step and so you can change easily

#### Attached Files:

• ###### insured_calc_etaf.xlsx
File size:
9.6 KB
Views:
114

Joined:
Nov 1, 2010
Messages:
11
Thank you so much Etaf. I will try it right now.

Joined:
Nov 1, 2010
Messages:
11
You are 'brilliant'! Works like a charm! Thank you so much Etaf. i must now 'study' the formula to understand it myself!

14. ### etaf Wayne Moderator

Joined:
Oct 2, 2003
Messages:
63,180
OK - all I did was break it down to each section and then for each section add brackets

so you can almost read the sections by looking at the ()

i have kept the 0.1 * 0.001
so that can be simplified - but if you change the % you can see where to change
also you could have another cell(s) to reference those %
so you can change the % and work out the result for each stage
and use the \$ to fix that % cell(s) and then you can use the formula multiple times
if you need that type of flexibility post back

Joined:
Nov 1, 2010
Messages:
11
i think i will try and decipher it tomorrow because it's been a long day ....... Thank you again for your help.

16. ### etaf Wayne Moderator

Joined:
Oct 2, 2003
Messages:
63,180
your welcome - crossed posts , i added another option of needed

As Seen On