Bond

[Rumpkin]

This works in excel 97 -2003 but does not work in 2013 or 2016.
I get a the #Name ? value.
Will you review and see what needs to change to work in 2013 or newer?

Function BOND(Subtotal, TaxRate) As Single
                            'Author Jim Hesh
                            'Set limits and rates according to your company's bond structure
  Dim Limit(6) As Single    'Increase this value for more limit steps
  Dim BondRate(6) As Single
  Dim B1 As Single
  Dim B2 As Single
  Dim i As Integer
  Dim Basis As Single
  Dim TempTotal As Single
  
  Limit(0) = 0
  Limit(1) = 100000      '   $400,000
  Limit(2) = 400000      '   $500,000
  Limit(3) = 1000000     '   $1,000,000
  Limit(4) = 2000000     '   $2,000,000
  Limit(5) = 2500000     '   $2,500,000
  Limit(6) = 2500001     '   $2,000,001
  BondRate(0) = 0
  BondRate(1) = 0.025    ' $25.00 per thousand Up to limit(1)
  BondRate(2) = 0.015    ' $15.00 per thousand From limit(2) to Limit(3)
  BondRate(3) = 0.01     ' $10.00 per thousand From limit(3) to Limit(4)
  BondRate(4) = 0.0075   ' $7.50 per thousand From limit(4) to Limit(5)
  BondRate(5) = 0.007    ' $7.00 per thousand From limit(4) to Limit(5)
  BondRate(6) = 0.0065   ' $6.50 per thousand From limit(4) to Limit(5)
  B1 = 0
  i = 0
  While Subtotal > Limit(i + 1)
    i = i + 1
    B1 = B1 + (Limit(i) - Limit(i - 1)) * BondRate(i)
  Wend
  
  Basis = (Subtotal * (1 + TaxRate) - Limit(i) + B1) / (1 - BondRate(i + 1) * (1 + TaxRate))
  'Round up to a full thousand
  Basis = 1000 * Int(Basis / 1000 + 0.999)
  B2 = BondRate(i + 1) * Basis
  TempTotal = B2 + Subtotal * (TaxRate + 1)

  'Check to see if tax + bond increase total to over the next limit
  If TempTotal > Limit(i + 1) Then
    i = i + 1
    B1 = B1 + (Limit(i) - Limit(i - 1)) * BondRate(i)
    Basis = Basis - Limit(i) + Limit(i - 1)
    Basis = 1000 * Int(Basis / 1000 + 0.999)
   End If
  B2 = BondRate(i + 1) * Basis
  BOND = B2 + B1
End Function

 

[Rumpkin]

Dave Patton,
Copy the code to a VBA module.
Change the rates and Limits to yours.
You will have to set your estimate up with a subtotal, named subtotal and a cell named Taxrate with the gross receipts rate for the project area, if there is one.
Formula is =Bond(SUBTOTAL,Taxrate)
Joeu2004 made some suggestions that may work as well.
Most bonding Companies will now share their calculation sheet which will give you the bond fees for the particular bid.
Know that bond fees are now always rounded up to no pennies