Search Search for: Business ApplicationsAll Forums

# Solved: Excel escalation formula

Member with 11 posts.

Join Date: Nov 2010
18-Sep-2012, 09:24 AM #1
Solved: Excel escalation formula
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.
 etaf   (Wayne)
Moderator with 46,103 posts.

Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
18-Sep-2012, 01:23 PM #2
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```
__________________
Please let us know what the final solution was to any problem posted
Member with 11 posts.

Join Date: Nov 2010
18-Sep-2012, 03:27 PM #3
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
 etaf   (Wayne)
Moderator with 46,103 posts.

Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
18-Sep-2012, 03:32 PM #4
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
__________________
Please let us know what the final solution was to any problem posted
Member with 11 posts.

Join Date: Nov 2010
18-Sep-2012, 03:52 PM #5
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
 etaf   (Wayne)
Moderator with 46,103 posts.

Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
18-Sep-2012, 04:09 PM #6
Quote:
 Sum insured R1,350,650 R1,350,650*0.1% Result R1,350.36
so thats not .1%
must end 650
1,350.65

Quote:
 Escalation clause R135,036 R136,036*0.1%/2 Result R67.52
where does the R136,036 come from ?
Member with 11 posts.

Join Date: Nov 2010
18-Sep-2012, 04:16 PM #7
Sorry, typo !! The figure shoudl be R135,036
 etaf   (Wayne)
Moderator with 46,103 posts.

Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
18-Sep-2012, 04:23 PM #8
so the sun assured is
Sum insured R1,350,360
Quote:
 Sum insured R1,350,360 R1,350,360*0.1% Result R1,350.36 Escalation clause R135,036 = 10% of the Sum insured R136,036*0.1%/2 Result R67.52 Annual Premium total of R1,350.36 + R67.52 Result R1417.88 Annual premium + 8% R1417.88*8% Result R113.43 Total of annual premium + 8% R1417.88 + R113.43 Result R1531.31 Monthly premium R1531.31/12 Result R127.61
so modified as above

correct
Member with 11 posts.

Join Date: Nov 2010
18-Sep-2012, 04:28 PM #9
yes that is correct. i now need that long story in one formula!!
 etaf   (Wayne)
Moderator with 46,103 posts.

Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
18-Sep-2012, 04:36 PM #10
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 (9.6 KB, 18 views)
__________________
Please let us know what the final solution was to any problem posted
Member with 11 posts.

Join Date: Nov 2010
18-Sep-2012, 04:42 PM #11
Thank you so much Etaf. I will try it right now.
Member with 11 posts.

Join Date: Nov 2010
18-Sep-2012, 04:48 PM #12
You are 'brilliant'! Works like a charm! Thank you so much Etaf. i must now 'study' the formula to understand it myself!
 etaf   (Wayne)
Moderator with 46,103 posts.

Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
18-Sep-2012, 04:53 PM #13
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
__________________
Please let us know what the final solution was to any problem posted
Member with 11 posts.

Join Date: Nov 2010
18-Sep-2012, 04:57 PM #14
i think i will try and decipher it tomorrow because it's been a long day ....... Thank you again for your help.
 etaf   (Wayne)
Moderator with 46,103 posts.

Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
18-Sep-2012, 04:58 PM #15
your welcome - crossed posts , i added another option of needed
 techguy.org/1069395
As Seen On

WELCOME TO TECH SUPPORT GUY!

If you're not already familiar with forums, watch our Welcome Guide to get started.

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

### Find the solution to your computer problem!

 Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)