Stepped bond rate calculator

[ronald coletto]

I am doing a bond calculation and I am having trouble calculating it

this is how it goes:

BOND RATES
$100,000.00 $25.00 $1,000.00
$400,000.00 $15.00 $1,000.00
$2,000,000.00 $10.00 $1,000.00
$2,500,000.00 $7.50 $1,000.00
$2,500,000.00 $7.00 $1,000.00
Over 7.5 $6.50 $1,000.00

example:
if the contract is
550,000.00

the 1st $100,000.00 = (100,000.00/1,000*25) = 2,500.00
the rest is $400,000.00 = (400,000.00/1,000*15) = 6,000.00
the remainder balance 50,000.00 = (50,000/1,000*10) = 500

for a total cost of the bond of (2,500+6,000+500)=9,000

thx in advance to someone that can help me with this, my tasks are over $10,000,000.00 and the change orders are changing the total price of the bond in construction so any help here is apreciated

[David A Coop]

Hi Ronald,

I am a bit confused by your post!

I am doing a bond calculation and I am having trouble calculating it

this is how it goes:

BOND RATES
$100,000.00 $25.00 $1,000.00
$400,000.00 $15.00 $1,000.00
$2,000,000.00 $10.00 $1,000.00
$2,500,000.00 $7.50 $1,000.00
$2,500,000.00 $7.00 $1,000.00
Over 7.5 $6.50 $1,000.00

example:
if the contract is
550,000.00

the 1st $100,000.00 = (100,000.00/1,000*25) = 2,500.00
the rest is $400,000.00 = (400,000.00/1,000*15) = 6,000.00
the remainder balance 50,000.00 = (50,000/1,000*10) = 500

for a total cost of the bond of (2,500+6,000+500)=9,000

The table looks wrong, the confusing stuff is red.

Also the Example looks wrong! I would expect the first $100K to be $2,500; the next $300k to be $4,500 and the last $150k to be $1,500 giving a total of $8,500.

If I am correct, then try this formula:

=SUMPRODUCT(--(A18>{0,100000,400000,2000000}),--(A18-{0,100000,400000,2000000}),{0.025,-0.01,-0.005,0})

Note: It is only good up to $2 million - you'll need to edit it based on how the rate table should have appeared!

Hope this helps, please let me know!

Regards

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[ronald coletto]

well not really the way that the bonding company calculate the prices is like this let me post:


$3,350,000.00 contract amount
$24,511.89 change order 1
$68,990.03 change order 2
$177,838.30 change order 3
AMOUNT with all change orders $3,621,340.22


1st 100k $100,000.00 $2,500.00
2nd 400k $400,000.00 $6,000.00
3rd 2 millions $2,000,000.00 $20,000.00
4th is the remainding balance of $3,621,340.22 $1,121,340.22 $8,410.05
total of the bond price $36,910.05
Hurricane 1.3% $479.83
Total $37,389.88



i posted my manual excel example for better understanding

[David A Coop]

Hi Ronald,

Believe it or not, your problem literally woke me up last night when it dawned on me that I was reading your table incorrectly! I made a quick note, and now here I am!

The formula is: =SUMPRODUCT(--(E16>{0,100000,500000,2500000,5000000,7500000}),--(E16-{0,100000,500000,2500000,5000000,7500000}),{0.025,-0.01,-0.005,-0.0025,-0.0005,-0.0005})

Here is your file where you can see it working. Bond Rate Calculator.xlsx

The SUMPRODUCT formula isn't linked to anything, but the blue values are where I worked out the values for the formula.

Sorry for the incomplete answer yesterday, I trust this one works for you!

Please test it and let me know!

Regards,

David


When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[ronald coletto]

Lol i will test it now

[ronald coletto]

David A Coop

AMAZING WORKS PERFECT

THX U ARE THE BEST!!!!!

[David A Coop]

David A Coop

AMAZING WORKS PERFECT

THX U ARE THE BEST!!!!!

You're welcome Ronald,

Thank YOU for the encouragement.

David

[ronald coletto]

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved"


I AM HAVING PROBLEMS CLOSING THIS THREAD