Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Excel escalation formula


(!)

COMPTEACH's Avatar
COMPTEACH COMPTEACH is offline
Member with 11 posts.
THREAD STARTER
 
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
Monthly Premium R127.62

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's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 51,582 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
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
__________________
Wayne
Please let us know what the final solution was to any problem posted
COMPTEACH's Avatar
COMPTEACH COMPTEACH is offline
Member with 11 posts.
THREAD STARTER
 
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's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 51,582 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
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
yyy = premium

escalation = 10% of yyyy

50% of yyyy = aaaa

8% added to premium = YYYY * .08
etc
detailed with the actual values instead of aaaaa yyyy xxx etc
COMPTEACH's Avatar
COMPTEACH COMPTEACH is offline
Member with 11 posts.
THREAD STARTER
 
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

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
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 51,582 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
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 ?
COMPTEACH's Avatar
COMPTEACH COMPTEACH is offline
Member with 11 posts.
THREAD STARTER
 
Join Date: Nov 2010
18-Sep-2012, 04:16 PM #7
Sorry, typo !! The figure shoudl be R135,036
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 51,582 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
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
COMPTEACH's Avatar
COMPTEACH COMPTEACH is offline
Member with 11 posts.
THREAD STARTER
 
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's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 51,582 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
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
File Type: xlsx insured_calc_etaf.xlsx (9.6 KB, 39 views)
COMPTEACH's Avatar
COMPTEACH COMPTEACH is offline
Member with 11 posts.
THREAD STARTER
 
Join Date: Nov 2010
18-Sep-2012, 04:42 PM #11
Thank you so much Etaf. I will try it right now.
COMPTEACH's Avatar
COMPTEACH COMPTEACH is offline
Member with 11 posts.
THREAD STARTER
 
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's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 51,582 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
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
COMPTEACH's Avatar
COMPTEACH COMPTEACH is offline
Member with 11 posts.
THREAD STARTER
 
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's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 51,582 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
18-Sep-2012, 04:58 PM #15
your welcome - crossed posts , i added another option of needed
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

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


(clock)
THIS THREAD HAS EXPIRED.
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)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑