I work in the bond industry (primary markets), we bring new issues to market and are responsible for "sizing" and structuring the deal. By structuring I mean structuring the amount of principal that needs to be amortized (can be issued) each year under the given constraints (revenues) of the issuer. Below is an example of a principal amortization schedule.

Principal

3,575,000.00

3,720,000.00

3,870,000.00

4,020,000.00

7,295,000.00

7,585,000.00

7,890,000.00

26,835,000.00

71,930,000.00

Notice that these are rounded into $5,000 blocks (that's how bonds are generally sold to institutional investors). This becomes important after viewing the code below. The aforementioned code listed below "fills" these principal amounts without exceeding the given constraints. Drum roll...here comes the code:

Calculate

Sheets("Debt Service").Select

Beep

Application.StatusBar = "Interpolate Level Debt Service"

Range("watch_1").Select

If Range("Rounding").Value < 0 Or Range("Rounding").Value >= 5000 Then

guess1 = 0

guess2 = 1000000

Range("Target").Value = guess1

Calculate

result1 = Range("Rounding").Value

Range("Target").Value = guess2

Calculate

result2 = Range("Rounding").Value

Do While Range("Rounding").Value < 0 Or Range("Rounding").Value >= 5000

guess3 = guess2 - (result2 * (guess2 - guess1) / ((result2 - result1 + 0.000001)))

Range("Target").Value = guess3

Calculate

result3 = Range("Rounding").Value

guess1 = guess2

guess2 = guess3

result1 = result2

result2 = result3

Loop

Calculate

End If

Beep

Application.StatusBar = False

End Sub

The debt service on this issue is a function of the principal amounts and the coupon (interest rate) on the individual principals. The code above works beautifully, however I need to add another condition to this macro and do not know how. I need all of the above to happen AND I need it to continue to happen until I have iterated to solve for the minimum TOTAL debt service (within the constraints obviously). Remember debt service is principal times the coupon. Any suggestions. Though this is long I still feel that it might need further explanation. I would gladly send a spreadsheet to anyone who is willing to help. Thanks in advance.