+ Reply to Thread
Results 1 to 8 of 8

Need Help Writing Formula To Calculate Construction Performance & Payment Bond

  1. #1
    Registered User
    Join Date
    05-14-2013
    Location
    Austin, Texas
    MS-Off Ver
    Excel 365
    Posts
    3

    Need Help Writing Formula To Calculate Construction Performance & Payment Bond

    I need to write a formula that will calculate the cost of a construction performance and payment bond. The bond is calculated as a percentage of the contract amount. Let's just say the contract amount is in cell A1 on the spreadsheet. The cost of the bond will be 2.5% of the first $100,000 of contract, 1.5% on the next $500,000 of the contract, and then 1.0% on the next 2,000,000 and so on. I have put the table below that references every percentage we would use. Remember that the formula needs to work even if the contract amount is as small as $1 or as large as millions of dollars. I am terrible at these types of formulas and could use some help. Thank you for your assistance!


    BOND RATE BOND AMOUNT
    2.50% Up to 100,000.00
    1.50% On the Next 400,000.00
    1.00% On the Next 2,000,000.00
    0.75% On the Next 2,500,000.00
    0.70% On the Next 2,500,000.00
    0.65% Amount Over 7,500,000.00

  2. #2
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Need Help Writing Formula To Calculate Construction Performance & Payment Bond

    Hi Dallas,

    I came up with this... what do you think..
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-14-2013
    Location
    Austin, Texas
    MS-Off Ver
    Excel 365
    Posts
    3

    Re: Need Help Writing Formula To Calculate Construction Performance & Payment Bond

    Thank you for the quick reply. Let me take a look at it.

  4. #4
    Registered User
    Join Date
    05-14-2013
    Location
    Austin, Texas
    MS-Off Ver
    Excel 365
    Posts
    3

    Re: Need Help Writing Formula To Calculate Construction Performance & Payment Bond

    It worked! Thank you so much!

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Need Help Writing Formula To Calculate Construction Performance & Payment Bond

    See the attached file (A12 is the inputcell).

    The result is in the green cell.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Need Help Writing Formula To Calculate Construction Performance & Payment Bond

    Glad it worked... sorry it's not a "magic" formula... but it's easy enough to follow...

    Please mark the original thread as SOLVED...

    Thanks!

  7. #7
    Registered User
    Join Date
    06-28-2013
    Location
    Salt Lake City, Utah
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Need Help Writing Formula To Calculate Construction Performance & Payment Bond

    I need one that calculates the total bid amount including the bond cost. When I calculate the bond cost after the bid I always come up a little short.

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Need Help Writing Formula To Calculate Construction Performance & Payment Bond

    2 things.

    Start your own question.

    If usefull refer to this question.

    Explain your question more (add an excel file, without confidentional information).

    Please also add the desired solution in your file.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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