Need Help Writing Formula To Calculate Construction Performance & Payment Bond

[construction-guru]

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, 2.0% of the next $400,000 of the contract, and then 1.5% of everything above that. 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 know this is some sort of three tiered "if then" formula, but I am just not knowledgeable in writing those types of formulas. This s a problem that every construction company that bonds projects needs to know the answer to. Thank you.

[MikeinOK]

=IF(A1>=500000,(((A1-500000)*0.015)+10500),IF(A1>=100000,(((A1-100000)*0.02)+2500),(A1*0.025)))

Try that, I may use too many parentheses, but it should work for you.

[dredwolf]

your looking at something like this:

d1=0.025 (first 100,000)
d2=0.02 (100,000 - 500,000)
d3=0.15 (everything above 500,000)
format as percentage

this will let you easily change the %

Please Login or Register  to view this content.

you could make the dollar values that the percentages apply to as cell references as well, to make changes to that range easier as well..

Hope this helps

[construction-guru]

=IF(A1>=500000,(((A1-500000)*0.015)+10500),IF(A1>=100000,(((A1-100000)*0.02)+2500),(A1*0.025)))

Try that, I may use too many parentheses, but it should work for you.

It works! Thank you!

[construction-guru]

your looking at something like this:

d1=0.025 (first 100,000)
d2=0.02 (100,000 - 500,000)
d3=0.15 (everything above 500,000)
format as percentage

this will let you easily change the %

Please Login or Register  to view this content.

you could make the dollar values that the percentages apply to as cell references as well, to make changes to that range easier as well..

Hope this helps

Thank you!

[dredwolf]

You are welcome

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

[stag22]

Great formula but mine is little different and I tried to adjust yours but not working. I would greatly appreciated if MikeinOK could fix the formula so that

0 to 100,000 at 2.5%
101,000 to 400,000 at 1.5%
401,000 to 2,000,000 at 1.0%

[dredwolf]

The basic formula is still the same , just the values change,
A1 = value
B1 =IF(OR(A1=0,A1=""),A1,IF(A1<=100000,A1*0.25,IF(A1<=400000,(100000*0.025)+((A1-100000)*0.015),IF(A1<=2000000,(100000*0.025)+(300000*0.015)+((A1-400000)*0.01),"Unknown Value"))))

Note that using cells for the values can make this formula much shorter and easier to read (and debug! )
Hope this helps )

[Concreteguy2001]

So i have a similar situation on bonds

So i have payment and performance bond and a 3 year bond above and beyond that for a 4 year total bond.


Per thousand
First 500,000 is at $8.00
Next 2,000,000 is at $5.00
Next 2,500,000 is at 4.50
next 2,500,000 is at 4

then the maintenance bond part for the next 3 years plus the above
First 2,500,000 2.00 per thousand per year
next 2,500,000 1.75 per thousand per year

So Lets say i have a 3.4 million dollar contract/bid amount? But these bids can be from 150k to 15 million (Cell A1 is my contract amount)

Thank you been just plugging numbers for years and wanted to make it more precise.

Thanks

[FDibbins]

So i have a similar situation on bonds

So i have payment and performance bond and a 3 year bond above and beyond that for a 4 year total bond.


Per thousand
First 500,000 is at $8.00
Next 2,000,000 is at $5.00
Next 2,500,000 is at 4.50
next 2,500,000 is at 4

then the maintenance bond part for the next 3 years plus the above
First 2,500,000 2.00 per thousand per year
next 2,500,000 1.75 per thousand per year

So Lets say i have a 3.4 million dollar contract/bid amount? But these bids can be from 150k to 15 million (Cell A1 is my contract amount)

Thank you been just plugging numbers for years and wanted to make it more precise.

Thanks

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread