+ Reply to Thread
Results 1 to 14 of 14

Calculating fees based on valuation tiers

  1. #1
    Registered User
    Join Date
    05-30-2018
    Location
    Detroit, MI
    MS-Off Ver
    2013
    Posts
    8

    Unhappy Calculating fees based on valuation tiers

    Hello,

    I'm still learning Excel and I'm either not calling it the right thing or it's just so easy i'm over thinking it.

    I want to create a calculator for building permit fees.

    A lot of permits are based on the project valuation.

    Example:

    Charge for first $1,000 of valuation.......$40.00
    $1,001 to $50,000..............................$9.00 per thousand
    $50,001 and above.............................$8.00 per thousand

    If the valuation of a project is $100,000 dollars then i have to do this

    100,000- 1,000 for the first valuation will charge me $40.00. I then have to take the remaining cost and go to the next tier ($9.00). So 99,000-50,000 will make me take the 50*$9.00 = $450.00 . I then have 49,000 left of the value so it will be 49*$8.00 = 392.00.

    I want to make a formula that i can just type the valuation in and it will calculate it for me.


    PLEASE HELP ME!
    Last edited by Squid288; 05-30-2018 at 02:44 PM.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculating fees based on valuation tiers

    Is the result correct in your post, or the explanation?

    I would evaluate $1001 to $50000 as 49 * $9 and $50001 to $100000 as 50 * $8 rather than the results you provided.

    What you're asking for is similar to a sliding scale tax formula, but they are typically done with percentages rather that fixed amounts.

    See if this helps, assuming project valuation in A1 (adjust to suit)

    First add in $40 if the valuation is greater than $0 with a simple formula

    =IF(A1>0,40,0)

    Next figure out how much (if any) of the 1001 to 50000 band is applicable, (median does that), round it up to the nearest thousand (ceiling), divide by 1000, then multiply by 9 ($ amount).
    *Note that the reason for using 49000 in the formula, $1001 (or more accurately $1000.01) to $50000 is $49000

    =((CEILING(MEDIAN(0,49000,A1-1000),1000)/1000)*9)

    Finally figure out the amount over 50000. This formula works similar to the one above, but as there is no upper limit, the max function is used instead of median, the rest is the same.

    =((CEILING(MAX(0,A1-50000),1000)/1000)*8)

    Now you just need to add them together.

    Does that help?

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculating fees based on valuation tiers

    What's the fee for $1500? Is it $40, $44.50, or $49?
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    05-30-2018
    Location
    Detroit, MI
    MS-Off Ver
    2013
    Posts
    8

    Re: Calculating fees based on valuation tiers

    I tried that and while it did work, it is a dollar off :/ i wonder why...

    Hand calculation brings it to 882 while the formula brings it to 881

  5. #5
    Registered User
    Join Date
    05-30-2018
    Location
    Detroit, MI
    MS-Off Ver
    2013
    Posts
    8

    Re: Calculating fees based on valuation tiers

    I tried that and while it did work, it is a dollar off :/ i wonder why...

    Hand calculation brings it to 882 while the formula brings it to 881

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Calculating fees based on valuation tiers

    Or ..


    =IF(A1<=1000,40,IF(A1<=50000,40+(A1-1000)/1000*9,481+(A1-50000)/1000*8))

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculating fees based on valuation tiers

    Quote Originally Posted by Squid288 View Post
    I tried that and while it did work, it is a dollar off :/ i wonder why...

    Hand calculation brings it to 882 while the formula brings it to 881
    Sounds like you're calculating $50000 at $9 / per thousand, not $1001 to $50000 which, as I stated is in fact only $49000.

    The answers we provide are only as accurate as the information we are given to work with.

  8. #8
    Registered User
    Join Date
    05-30-2018
    Location
    Detroit, MI
    MS-Off Ver
    2013
    Posts
    8

    Re: Calculating fees based on valuation tiers

    Quote Originally Posted by shg View Post
    What's the fee for $1500? Is it $40, $44.50, or $49?
    It would be the $40.00 with half of the 9.00. but sometimes if you talk to the municipality they will have you round up or down.

  9. #9
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Calculating fees based on valuation tiers

    Your explanation is not really clear.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  10. #10
    Registered User
    Join Date
    05-30-2018
    Location
    Detroit, MI
    MS-Off Ver
    2013
    Posts
    8

    Re: Calculating fees based on valuation tiers

    Quote Originally Posted by jason.b75 View Post
    Sounds like you're calculating $50000 at $9 / per thousand, not $1001 to $50000 which, as I stated is in fact only $49000.

    The answers we provide are only as accurate as the information we are given to work with.
    Okay. that would explain it then. Thank you!

  11. #11
    Registered User
    Join Date
    05-30-2018
    Location
    Detroit, MI
    MS-Off Ver
    2013
    Posts
    8

    Re: Calculating fees based on valuation tiers

    Quote Originally Posted by mehmetcik View Post
    Your explanation is not really clear.
    Charge for first $1,000 of valuation.......$40.00
    $1,001 to $50,000..............................$9.00 per thousand
    $50,001 and above.............................$8.00 per thousand

    Valuation is $100,000

    $100,000 - $1,000 the first tier $1,000 (40.00) leaves 99,000 for the valuation. I would then go on to the next tier.............. $40.00

    99,000- $50,000 (the second tier $9.00 per thousand up to $50,000) what is left is then 49,000....... $450.00

    I would then take that $49,000 and times it by the last tier,$8.00, ...............$392.00

    You then add each tier together to get the $882.00

    Sorry if that doesn't make sense. It's hard to articulate it.

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculating fees based on valuation tiers

    A
    B
    C
    D
    E
    F
    1
    Value
    Fee
    Delta
    2
    0
    0.0%
    0.0%
    C2: =B2-N(B1)
    3
    1,000
    0.9%
    0.9%
    4
    50,000
    0.8%
    -0.1%
    5
    6
    Value
    Good Talk
    No Talk
    Bad Talk
    7
    0
    40.00
    40.00
    40.00
    B7: =40 + SUMPRODUCT((FLOOR($A7, 1000) > $A$2:$A$4) * (FLOOR($A7, 1000) - $A$2:$A$4) * $C$2:$C$4)
    8
    500
    40.00
    40.00
    40.00
    C7: =40 + SUMPRODUCT(($A7 > $A$2:$A$4) * ($A7 - $A$2:$A$4) * $C$2:$C$4)
    9
    1,000
    40.00
    40.00
    40.00
    D7: =40 + SUMPRODUCT((CEILING($A7, 1000) > $A$2:$A$4) * (CEILING($A7, 1000) - $A$2:$A$4) * $C$2:$C$4)
    10
    1,500
    40.00
    44.50
    49.00
    11
    2,000
    49.00
    49.00
    49.00
    12
    10,500
    121.00
    125.50
    130.00
    13
    50,500
    481.00
    485.00
    489.00
    14
    100,500
    881.00
    885.00
    889.00

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Calculating fees based on valuation tiers

    Second tier is 49000 (50000-1000 [from first tier])

    Third tier is 50000 (100000 -50000)

  14. #14
    Registered User
    Join Date
    05-30-2018
    Location
    Detroit, MI
    MS-Off Ver
    2013
    Posts
    8

    Re: Calculating fees based on valuation tiers

    Quote Originally Posted by JohnTopley View Post
    Second tier is 49000 (50000-1000 [from first tier])

    Third tier is 50000 (100000 -50000)
    Oh okay. Now i get it. sorry for the confusion I'm not super strong in the math department but you have helped me out tremendously! Thank you!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. SUMPRODUCT incorrectly calculating price tiers
    By awenner in forum Excel General
    Replies: 3
    Last Post: 08-16-2017, 01:14 PM
  2. calculating incentive fees
    By OmarHamza in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-23-2016, 07:54 AM
  3. Replies: 6
    Last Post: 06-08-2012, 11:28 AM
  4. Replies: 7
    Last Post: 04-22-2012, 02:54 PM
  5. [SOLVED] Calculating % of cost with different tiers
    By Schnizzle in forum Excel General
    Replies: 2
    Last Post: 03-27-2012, 09:08 AM
  6. calculating discount 15 tiers
    By bamboozle in forum Excel General
    Replies: 2
    Last Post: 04-12-2010, 01:24 PM
  7. calculating course fees
    By TDI-GUY in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-14-2005, 03:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1