1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel escalation formula

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

Thread Status:
Not open for further replies.
Advertisement
  1. COMPTEACH

    COMPTEACH Thread Starter

    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
    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.
     
  2. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,958
    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 
    
     
  3. COMPTEACH

    COMPTEACH Thread Starter

    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
     
  4. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,958
    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
     
  5. COMPTEACH

    COMPTEACH Thread Starter

    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

    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
     
  6. etaf

    etaf Wayne Moderator

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

    where does the R136,036 come from ?
     
  7. COMPTEACH

    COMPTEACH Thread Starter

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

    etaf Wayne Moderator

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

    correct
     
  9. COMPTEACH

    COMPTEACH Thread Starter

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

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,958
    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:

  11. COMPTEACH

    COMPTEACH Thread Starter

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

    COMPTEACH Thread Starter

    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!
     
  13. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,958
    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
     
  14. COMPTEACH

    COMPTEACH Thread Starter

    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.
     
  15. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,958
    your welcome (y) - crossed posts , i added another option of needed
     
  16. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1069395